/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


SQL - List missing patch per device

05/11/2020 111 views

Hello,

Can you help to get an sql query to list missing patch per device? the database schema changes has changed I dont know how do it on this new db.


System Name        | Missing Patchs
xxxxx01                   | KBxxxxxx1 
xxxxx01                   | KBxxxxxx2 
xxxxx01                   | KBxxxxxx3 
xxxxx01                   | KBxxxxxx4 
xxxxx01                   | KBxxxxxx5 


1 Comment   [ + ] Show comment

Comments

  • You may want to check this article:

    https://support.quest.com/kace-systems-management-appliance/kb/309572/updating-custom-sql-queries-for-10-0

    Additionally you can start building the report with wizard and then save it. Open it from the list of report and hit Edit SQL, you will get SQL for it. Below an example I did:

    This one I used:
    - Topic: Device
    I selected from Device Identity Information:
    * IP Address and System Name
    I selected from Operating System Info:
    *OS Name
    I selected from Patch status:
    * Detect Status
    - You can sort it as you preference, I did:
    Order by: System (Ascending) IP Address(Ascending) Detect Status (Ascending
    - Then on Filter I added:
    * Detect Status = NOT PATCHED

    This is the SQL I got from that report above:

    SELECT MACHINE.NAME AS SYSTEM_NAME, OS_NAME, MACHINE.IP, group_concat(distinct concat(KBSYS.PATCH.TITLE,' (',KBSYS.PATCH.PATCH_IDENTIFIER,') - ',PATCH_MACHINE_STATUS.DETECT_STATUS) order by KBSYS.PATCH.TITLE separator '\n') as DETECT_STATUS FROM MACHINE left join PATCH_MACHINE_STATUS on PATCH_MACHINE_STATUS.MACHINE_ID = MACHINE.ID LEFT JOIN KBSYS.PATCH ON (KBSYS.PATCH.ID = PATCH_MACHINE_STATUS.PATCH_ID) WHERE ((PATCH_MACHINE_STATUS.DETECT_STATUS = 'NOTPATCHED')) GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME, OS_NAME, DETECT_STATUS

    I hope it helps :)

Be the first to answer this question

 
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