/build/static/layout/Breadcrumb_cap_w.png

I need a SQL report that show me Connected computers and a specific software.

Hi everyone! 

I already have the part that show me the specific software but I don't know how to add in this report the column "Power State" (true: connected / false: disconnected)


SELECT SOFTWARE.PUBLISHER, SOFTWARE.DISPLAY_NAME, SOFTWARE.DISPLAY_VERSION, USER_FULLNAME, MACHINE.NAME AS SYSTEM_NAME, MACHINE.IP FROM MACHINE LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) WHERE (SOFTWARE.Display_name like 'xxxxx %') GROUP BY MACHINE.ID ORDER BY SOFTWARE.PUBLISHER, SOFTWARE.DISPLAY_NAME, USER_FULLNAME, SYSTEM_NAME


If anyone knows hot to do this I'll appreciated a LOT!

Thanks!


0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 3 years ago
Red Belt
1

You need to add a join to the KBSYS.SMMP_CONNECTION table:

LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID

With the join in place you can add this line to the where clause:

and CLIENT_CONNECTED = 1

Here is the resulting query:

SELECT SOFTWARE.PUBLISHER, SOFTWARE.DISPLAY_NAME, SOFTWARE.DISPLAY_VERSION, 
USER_FULLNAME, 
MACHINE.NAME AS SYSTEM_NAME, MACHINE.IP 
FROM MACHINE 
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) 
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) 
LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID
WHERE (SOFTWARE.Display_name like 'xxxxx %') 
and CLIENT_CONNECTED = 1
GROUP BY MACHINE.ID 
ORDER BY SOFTWARE.PUBLISHER, SOFTWARE.DISPLAY_NAME, USER_FULLNAME, SYSTEM_NAME



 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ