Patching Report
Hi,
I badly need some help. Does anyone have a SQL query to generate the list of patches that are being patched base on the schedule of patching?
This is for servers only.
1 Comment
[ + ] Show comment
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
9 years ago
Top Answer
This query will show you each patch for a given patching schedule:
SELECT S.DESCRIPTION, ORG1.PATCHLINK_PATCH_LABEL_JT.PATCHUID, P.*
FROM ORG1.PATCHLINK_SCHEDULE S
JOIN ORG1.PATCHLINK_SCHEDULE_DEPLOY_LABEL_JT on ORG1.PATCHLINK_SCHEDULE_DEPLOY_LABEL_JT.PATCHLINK_SCHEDULE_ID = S.ID
JOIN ORG1.PATCHLINK_PATCH_LABEL_JT on ORG1.PATCHLINK_PATCH_LABEL_JT.LABEL_ID = ORG1.PATCHLINK_SCHEDULE_DEPLOY_LABEL_JT.LABEL_ID
JOIN KBSYS.PATCHLINK_PATCH P on P.UID = ORG1.PATCHLINK_PATCH_LABEL_JT.PATCHUID
WHERE S.DESCRIPTION = "Test Patching Deploy"
I wanted to start with that because it shows valuable information about the join tables necessary and will show you what exists in the database for patches. This report does not filter the patches on whether they are enabled or not in KACE which is important. For instance it would make sense to exclude patches that are superceded so adding IS_SUPERCEDED = 0 to the report should be done.
If you really want a comma separated list of patches per schedule then you need to group the patches like this:
SELECT S.DESCRIPTION, GROUP_CONCAT(P.TITLE)
FROM ORG1.PATCHLINK_SCHEDULE S
JOIN ORG1.PATCHLINK_SCHEDULE_DEPLOY_LABEL_JT on ORG1.PATCHLINK_SCHEDULE_DEPLOY_LABEL_JT.PATCHLINK_SCHEDULE_ID = S.ID
JOIN ORG1.PATCHLINK_PATCH_LABEL_JT on ORG1.PATCHLINK_PATCH_LABEL_JT.LABEL_ID = ORG1.PATCHLINK_SCHEDULE_DEPLOY_LABEL_JT.LABEL_ID
JOIN KBSYS.PATCHLINK_PATCH P on P.UID = ORG1.PATCHLINK_PATCH_LABEL_JT.PATCHUID
WHERE P.IS_SUPERCEDED = 0
GROUP BY S.ID
This may result in a very long list of patches depending on your patch subscriptions.
Comments:
-
Hi chucksteel,
Thanks for the query. It works well.
I have another question, will you be able to help me to get the report for the list of patches that installed on a specific schedule? Let say i will run the server patching every Thursday @ 3 am and i want to know what are the patches installed per server. - lalako88 9 years ago-
There should be canned reports showing patches installed on computers. Do you want to limit the report to just machines included in a specific schedule? - chucksteel 9 years ago
-
I need the report to show the server hostname and the patched that has been installed after the patching schedule run. I will need this report for servers only.
I saw this report in K1000 just now - "Device, what patches are installed" , but i dont know where can i indicate that is it only for the servers. For the after the patching the report will be generated, I think I will run as report schedules. I really appreciate your help.
Select CASE WHEN MACHINE.SYSTEM_DESCRIPTION = '' THEN MACHINE.NAME WHEN MACHINE.SYSTEM_DESCRIPTION != '' THEN CONCAT(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) END AS MACHINE_NAME,
P.TITLE AS DISPLAY_NAME, P.IDENTIFIER as KB_ARTICLE from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P
where
MACHINE.ID = S.MACHINE_ID and
S.PATCHUID = P.UID and
S.STATUS = 'PATCHED'
order by MACHINE_NAME, P.TITLE - lalako88 9 years ago -
Do you have your servers labeled with a specific label? Are they in their own patch schedule? How you determine what is and isn't a server in your environment will affect how the report is made. We don't use KACE to manage servers in my environment so depending on how you have things configured I may not be able to test a report. - chucksteel 9 years ago
Patching Schedule | Patch1, Patch2, Patch3, etc.? - chucksteel 9 years ago