Patch reporting for specific machine name
I need an SQL script like the report "Patches waiting to be deployed" but for a specific machine.
Would also like to have a report showing missing patches for a specific machine and yet another one showing patch errors for a specific machine.
Would also be really good to have the 3 scripts for a group of machines based on Device label. We are using LDAP labels where we have AD groups for different department servers. The servers in for example R&D department are member of an AD group named _KACE_RnD and we have a LDAP label with the same name in KACE.
3 Comments
[ + ] Show comments
-
If it's just for one machine, why not use the "Patching Detect/Deploy Status" on the device's inventory page? - JasonEgg 7 years ago
-
Can you move the "3 scripts" question to a separate topic? Also: I'm not quite too sure what you're asking for. Sounds like you have labels to target. - JasonEgg 7 years ago
-
We have 1200 machines and using about 20 LDAP labels to differentiate the patch schedules. The system owners want to have a report of what will be patched on their servers a couple of days before. They also want a report after the patching showing how it went. That's why I want reports that shows each patch that will be installed on all machines in a device label. - Internalit@cambio.se 7 years ago
Answers (2)
Please log in to answer
Posted by:
chucksteel
7 years ago
For the patches waiting to be deployed report:
1. Open the current report (click on the title, don't run the report)
2. Click Duplicate
3. Give it a new name
4. Find these two lines:
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
GROUP BY PP.UID
5. Insert:
WHERE M.NAME = "machinename"
between those lines
6. Save the report
Here is a report that will show the number of patches installed and not installed per machine in a given label:
SELECT MACHINE.ID, MACHINE.NAME,
PSMS.PATCHED,
PSMS.NOTPATCHED,
PSMS.LAST_RUN
FROM PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS
JOIN MACHINE on MACHINE.ID = PSMS.MACHINE_ID
JOIN PATCHLINK_SCHEDULE P on P.ID = PSMS.PATCHLINK_SCHEDULE_ID
JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE and K.KUID = MACHINE.KUID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
WHERE
LABEL.NAME = "Label"
and NOTPATCHED > 20
ORDER BY MACHINE.NAME
Comments:
-
Hi, I like the simplicity of this report. However when I run it, I sometimes get 2 LAST_RUN times for the same machine with possible different PATCH counts. How can I limit this so that I only see the latest LAST_RUN entry only for each machine, and not all? - tuyen 7 years ago
-
You need to use the maximum last_run which then requires that you group by something. I chose machine name:
SELECT MACHINE.ID, MACHINE.NAME,
PSMS.PATCHED,
PSMS.NOTPATCHED,
MAX(PSMS.LAST_RUN)
FROM PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS
JOIN MACHINE on MACHINE.ID = PSMS.MACHINE_ID
JOIN PATCHLINK_SCHEDULE P on P.ID = PSMS.PATCHLINK_SCHEDULE_ID
JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE and K.KUID = MACHINE.KUID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
WHERE
LABEL.NAME = "SL-Patch Production"
and NOTPATCHED > 20
GROUP BY MACHINE.NAME
ORDER BY MACHINE.NAME - chucksteel 7 years ago-
Thank you. This is a very useful report to show that there are patches to be deployed to computers.
Is it possible to list it such that for each machine where patches are to be deployed, it will list the patches that will need to be deployed as well? - tuyen 7 years ago -
This query includes the patches that are listed as being needed. I noticed that the information in the PATCHLINK_MACHINE_STATUS table showing the number of patches that are "NOTPATCHED" doesn't match the number reported from the patching schedule, however.
SELECT MACHINE.ID, MACHINE.NAME,
PSMS.PATCHED,
PSMS.NOTPATCHED,
MAX(PSMS.LAST_RUN),
COUNT(PATCH.TITLE),
GROUP_CONCAT(PATCH.TITLE) as "Patches Needed"
FROM PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS
JOIN MACHINE on MACHINE.ID = PSMS.MACHINE_ID
JOIN PATCHLINK_SCHEDULE P on P.ID = PSMS.PATCHLINK_SCHEDULE_ID
JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE and K.KUID = MACHINE.KUID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
JOIN PATCHLINK_MACHINE_STATUS PMS on PMS.MACHINE_ID = MACHINE.ID and PMS.STATUS = "NOTPATCHED"
JOIN KBSYS.PATCHLINK_PATCH PATCH on PATCH.UID = PMS.PATCHUID
WHERE
LABEL.NAME = "SL-Patch Production"
and NOTPATCHED > 20
GROUP BY MACHINE.NAME
ORDER BY MACHINE.NAME
I'm not sure how to determine which count is correct. - chucksteel 7 years ago
Posted by:
tuyen
7 years ago
I was able to reply to your last comment.
I tried your query, and it works well:
SELECT MACHINE.ID, MACHINE.NAME,
PSMS.PATCHED,
PSMS.NOTPATCHED,
MAX(PSMS.LAST_RUN),
COUNT(PATCH.TITLE),
GROUP_CONCAT(PATCH.TITLE) as "Patches Needed"
FROM PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS
JOIN MACHINE on MACHINE.ID = PSMS.MACHINE_ID
JOIN PATCHLINK_SCHEDULE P on P.ID = PSMS.PATCHLINK_SCHEDULE_ID
JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE and K.KUID = MACHINE.KUID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
JOIN PATCHLINK_MACHINE_STATUS PMS on PMS.MACHINE_ID = MACHINE.ID and PMS.STATUS = "NOTPATCHED"
JOIN KBSYS.PATCHLINK_PATCH PATCH on PATCH.UID = PMS.PATCHUID
WHERE
LABEL.NAME = "SL-Patch Production"
and NOTPATCHED > 20
GROUP BY MACHINE.NAME
ORDER BY MACHINE.NAME
SELECT MACHINE.ID, MACHINE.NAME,
PSMS.PATCHED,
PSMS.NOTPATCHED,
MAX(PSMS.LAST_RUN),
COUNT(PATCH.TITLE),
GROUP_CONCAT(PATCH.TITLE) as "Patches Needed"
FROM PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS
JOIN MACHINE on MACHINE.ID = PSMS.MACHINE_ID
JOIN PATCHLINK_SCHEDULE P on P.ID = PSMS.PATCHLINK_SCHEDULE_ID
JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE and K.KUID = MACHINE.KUID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
JOIN PATCHLINK_MACHINE_STATUS PMS on PMS.MACHINE_ID = MACHINE.ID and PMS.STATUS = "NOTPATCHED"
JOIN KBSYS.PATCHLINK_PATCH PATCH on PATCH.UID = PMS.PATCHUID
WHERE
LABEL.NAME = "SL-Patch Production"
and NOTPATCHED > 20
GROUP BY MACHINE.NAME
ORDER BY MACHINE.NAME
What I have noticed is that the number patches and the patches listed as required to be patch is coming from all previous detections. Is it possible to limit the "Patches Needed" list to the results from the latest detection run only? I think if you do this, then the count would be correct. Currently, it is including counts from previous detections where that patch it is referencing may already be superceded and no longer needed due to changing the deployment settings. In my case, I had an All Patch Detection done a few months ago which included non-security patches, and the results of that detection is still there with the computer inventory details, however according to actual patches that need to be deployed, the patch listed is not needed to be deployed.
Comments:
-
Our results may also differ because we have separate detect and deploy schedules. Because of this my last result in the PATCHLINK_SCHEDULE_MACHINE_STATUS table doesn't show any patches detected, because the last result was a deploy. - chucksteel 7 years ago
-
I definitely would like to know how this is possible as well. previous detect counts really doesn't make for accurate compliance reports. for example my laptop says i am missing one patch that happens to be a dated chrome update and i know i have an updated version of chrome based off my check in the about section and in my software list. - fcpkaceguy 5 years ago
-
In that particular case, the patch will show as needed until the next detect cycle runs. Patching and software inventory are not tied together in that way. - chucksteel 5 years ago
