/build/static/layout/Breadcrumb_cap_w.png
01/11/2019 298 views

Our auditors would like a report that shows, for each computer, how many patches are installed vs how many are not, and it needs to be broken out by things like Windows Patches and Adobe Patches. The report would look something like this:

Windows Patches
Computer Name        Number Installed        Number Not Installed
pc-x                              70                               5
pc-y                              73                               2


Adobe Patches
Computer Name        Number Installed        Number Not Installed
pc-x                              5                               2
pc-y                              6                               1

I already have SQL code to build a report that shows a table like this for all patches installed vs not installed on each machine, but I was not sure how to break it out by specific vendors, and then computer names. I know you can use a smart label for the machines, but was having trouble figuring out how to join the patch smart label, which I found under ORG1.LABEL.

Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • Can you post your current SQL query?
  • Sure, Thank you. I found out that it does not absolutely have to be the way I showed originally. It can be separate reports for each vendor. What I have right now shows all patches, without breaking out the ones by vendor.

    Select M.NAME as MACHINE_NAME, SUM(MS.STATUS='PATCHED') AS PATCHED, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED

    FROM ORG1.PATCHLINK_MACHINE_STATUS MS JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID

    JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID

    JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID

    where PPS.STATUS = 0 /* 0=active patches */
    group by MACHINE_NAME

    order by MACHINE_NAME, PP.TITLE

Answer Chosen by the Author

0

The KBSYS.PATCHLINK_PATCH includes a vendor column, so we can use that to select patches for a specific vendor:

Select M.NAME as MACHINE_NAME, SUM(MS.STATUS='PATCHED') AS PATCHED, 
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED
FROM ORG1.PATCHLINK_MACHINE_STATUS MS JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID 
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID 
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID 
where PPS.STATUS = 0 /* 0=active patches */ 
AND VENDOR like "Adobe%" /* Use like % to catch multiple spellings, etc */
group by MACHINE_NAME
order by MACHINE_NAME, PP.TITLE

Answered 01/15/2019 by: chucksteel
Red Belt

  • Thanks Chucksteel. I did that and for some reason, it only reported a handful of machines, but I am running a detect of all adobe machines again and hope that fixes it. I did upgrade SMA to version 9.0, so I hope that did not affect any reporting. Thanks.
    • Yup, forcing a detect of adobe patches fixed that.

      I have one more question. I know if I add the line GROUP_CONCAT(PP.TITLE) AS PATCH_NAME to the beginning, it lists all patches, both installed and not installed, for each machine. Do you know if there is a way to break it so that one column names the ones installed, and the other column lists the ones not installed? Thanks.
      • Today I learned how to use a CASE statement inside of a GROUP_CONCAT:
        Select M.NAME as MACHINE_NAME, SUM(MS.STATUS='PATCHED') AS PATCHED,
        SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
        GROUP_CONCAT(CASE WHEN MS.STATUS = 'PATCHED' THEN PP.TITLE END) AS 'Titles Patched',
        GROUP_CONCAT(CASE WHEN MS.STATUS = 'NOTPATCHED' THEN PP.TITLE END) AS 'Titles Not Patched'

        FROM ORG1.PATCHLINK_MACHINE_STATUS MS
        JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID
        JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
        JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
        where PPS.STATUS = 0 /* 0=active patches */
        AND VENDOR like "Adobe%" /* Use like % to catch multiple spellings, etc */
        group by MACHINE_NAME
        order by MACHINE_NAME, PP.TITLE

        Credit for syntax to https://stackoverflow.com/questions/12745352/how-group-concat-with-like-where-condition-in-that-case
  • Thank you! I should be all set now!
    • Okay, sorry to keep asking questions. A couple things came up, if you have advice.

      1) Is there any way to join software versions to a patching report? For our adobe patching report, my director is stating that the auditors are going to want an additional column which shows which version of Adobe Reader is installed on each machine so they can judge if someone is x number of versions behind. I attempted something like this with the report builder wizard, but no go.

      2) Is there a way, instead of vendor, to create the patch report only for a certain patch label? I have seen how to do it with a device label, but not with the patch label.

      Thanks for all your help.
      • 1) Software versions are harder than one would think. Mostly because version numbers generally aren't real numbers since they contain more than one period, e.g. Adobe Reader 11.0.20.17. Combining that data with patches presents its own difficulties, as there isn't a link in the database from patches to software. The best way to produce a report for a specific title is to hard code the target version and find systems that don't have that version.

        2) Yep. I'll post as another answer.

All Answers

0
-- Patches per computer patched and not patched
-- for patches in a given label
-- includes count and titles of patches
-- Poasted as solution for https://www.itninja.com/question/patch-report-request

Select M.NAME as MACHINE_NAME, SUM(MS.STATUS='PATCHED') AS PATCHED, 
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
GROUP_CONCAT(CASE WHEN MS.STATUS = 'PATCHED' THEN PP.TITLE END) AS 'Titles Patched',
GROUP_CONCAT(CASE WHEN MS.STATUS = 'NOTPATCHED' THEN PP.TITLE END) AS 'Titles Not Patched'
FROM ORG1.PATCHLINK_MACHINE_STATUS MS 
JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID 
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID 
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID 
JOIN PATCHLINK_PATCH_LABEL_JT ON PATCHLINK_PATCH_LABEL_JT.PATCHUID = PP.UID
JOIN LABEL ON LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID
where PPS.STATUS = 0 /* 0=active patches */ 
AND LABEL.NAME = "Adobe Patches" /* Set patch label here */
group by MACHINE_NAME
order by MACHINE_NAME, PP.TITLE


Answered 01/22/2019 by: chucksteel
Red Belt

  • Thank you once again Chucksteel. As far as the software versions, I was in MySQL trying to go through tables and think which ones I could possibly join together in such a way as to join together the data I need, and I was just clueless. It does not help that I am very much a beginner at this. I appreciate all your help.