Report to show computers that do not have certain software installed
I pulled a little SQL query off of this forum to build a report showing computers that do not have AV installed. That query is this:
SELECT MACHINE.NAME AS SYSTEM_NAME FROM MACHINE WHERE (((not exists (select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME like '%Our AV Software%')) )) ORDER BY SYSTEM_NAME
It works, but I would like to add a column in the Excel output to show when the computers last checked in. What would I add to this query for that?
Answers (1)
Top Answer
SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.LAST_INVENTORY FROM MACHINE WHERE (((not exists (select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME like '%Our AV Software%')) )) ORDER BY SYSTEM_NAME
Comments:
-
Thank you for the quick response. And apologies for not thinking of this earlier, but I was thinking of doing that as a daily report. How would I go about showing only computers that have checked in within the last 24 hours? - Need Help 4 years ago
-
SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.LAST_INVENTORY FROM MACHINE WHERE (not exists (select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME like '%Our AV Software%')) AND MACHINE.LAST_INVENTORY > DATE_SUB(NOW(), INTERVAL 24 HOUR) ORDER BY SYSTEM_NAME - KevinG 4 years ago
-
Awesome! Thank you. - Need Help 4 years ago
