SQL - List missing patch per device
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
Answers (0)
Please log in to answer
Be the first to answer this question
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 :) - CSelles 4 years ago