SQL for Patch ID Status (Smart Label)
I'm trying to create a Smart Label that groups devices that have successfully completed a specific Patch Schedule. The Smart label wizard doesn't seem to allow me to specify any criteria for the Patch Name/ID. Is there SQL I can use to create a smart label instead? I'm essentially looking to group windows devices that have the Task phase 'completed' for the Patching schedule with ID 001 (for example). Is there SQL for this? Thanks in advance for anyone with ideas.
Answers (1)
So the table I think you are looking for is PATCH_SCHEDULE_RUN_MACHINE which has fields in of MACHINE_ID, PATCH_SCHEDULE_ID and STATUS
It also has a field PATCH_SCHEDULE_RUN_ID which increments with every patch schedule run
SO if you want to create a label for machines that have completed a specific patch schedule you will first need to find the ID of the patch schedule, the easiest way to do that is to go to Security>Patch Management>Schedules and hover over the schedule to see the ID in the bottom left of your screen.
You then need to build an SQL statement that selects the machine ID, where the schedule is the ID you need, with the maximum PATCXH_SCHEDULE_RUN_ID where the STATUS is "completed"
You could then use that SQL in your smart label
I have tested this SQL so you just need to add in your schedule ID to try this, run it as a report first before using it as smart label SQL
SELECT MACHINE_ID
FROM PATCH_SCHEDULE_RUN_MACHINE
WHERE PATCH_SCHEDULE_ID = 6
AND PATCH_SCHEDULE_RUN_ID = (
SELECT MAX(PATCH_SCHEDULE_RUN_ID)
FROM PATCH_SCHEDULE_RUN_MACHINE
WHERE PATCH_SCHEDULE_ID = 6
)
AND STATUS LIKE '%completed%';
