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!
Answers (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
