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.
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Do you want something that looks like:

    Patching Schedule | Patch1, Patch2, Patch3, etc.?
Please log in to comment

Answer Chosen by the Author

0
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.

Answered 04/17/2015 by: chucksteel
Red Belt

  • 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.
    • 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?
      • 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
      • 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.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity