Patch Report Request
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.
Answers (2)
Top Answer
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
Comments:
-
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. - jmorton 5 years ago
-
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. - jmorton 5 years ago-
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 - chucksteel 5 years ago
-
Thank you! I should be all set now! - jmorton 5 years ago
-
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. - jmorton 5 years ago-
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. - chucksteel 5 years ago
-- 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
Comments:
-
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. - jmorton 5 years ago
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 - jmorton 5 years ago