/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Report to show computers that do not have certain software installed

09/01/2020 237 views

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?




Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

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

Answered 09/01/2020 by: KevinG
7th Degree Black Belt

  • 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?
    • 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
 
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