K1000 Report of All Patches Missing by Machine, displayed in a specific way...
I need help creating a specific report. This report:
http://www.itninja.com/question/k1000-report-of-all-patches-missing-by-machine
is almost what I need except that I need the report to display like this:
"Machine name", # of patches missing this machine
list of patches missing
"machine name 2", # of patches missing on this machine
list of patches missing
etc
This is code code from the above link, I think it just needs to be modified slightly:
**SQL Statement**
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
Thanks for any help you can provide!
-
Bueller? - nick2585 10 years ago
-
How about by path label - that way we know we are current within three months. I'm finding it's reporting on superseded patches which then is not accurate. - anaccarato 8 years ago
-
I'm terrible at this SQL thing which is why I'm asking. - anaccarato 8 years ago
Answers (2)
Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED, GROUP_CONCAT(PP.TITLE) AS PATCH_NAME
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 ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
where MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */
group by MACHINE_NAME
order by MACHINE_NAME, PP.TITLE
That should give you exactly what you are looking for. Here are a couple variations of the query that you may find useful.
With labels included:
Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED, GROUP_CONCAT(PP.TITLE) AS PATCH_NAME
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 ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
where MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */
and L.NAME LIKE ('Windows Workstations (All)') and (M.NAME not like '%WINDOWS-%')
group by MACHINE_NAME
order by MACHINE_NAME, PP.TITLE
Filtering by patch impact:
Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED, GROUP_CONCAT(PP.TITLE) AS PATCH_NAME
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 ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
where PP.IMPACTID = ('Critical') AND MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */
and L.NAME LIKE ('Windows Workstations (All)') and (M.NAME not like '%WINDOWS-%')
group by MACHINE_NAME
order by MACHINE_NAME, PP.TITLE
Finally, adding in a period of time (only report against machines that have checked in within the last...)
Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED, GROUP_CONCAT(PP.TITLE) AS PATCH_NAME
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 ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
where PP.IMPACTID = ('Critical') AND MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */
and L.NAME LIKE ('Windows Workstations (All)') and (M.NAME not like '%WINDOWS-%') and
((TIMESTAMP(LAST_SYNC) <= NOW() AND TIMESTAMP(LAST_SYNC) > DATE_SUB(NOW(),INTERVAL 14 DAY)))
group by MACHINE_NAME
order by MACHINE_NAME, PP.TITLE
Comments:
-
Jparkins. I know this is from over a year ago but thank you for these reports. I'm curious how would I edit this report to show the same columns except:
1. Remove the 'patch name' column entirely
2. I think these reports are accounting for all critical patches even if they don't apply to each computer. I have many fully patched systems (or close to it) and this report says they're missing dozens or hundreds. I Group my patches in patch catalog labels. Do you know the best way to add my patch labels to a query?
Also do you have a recommendation of how I could learn what each of the items actually refer to? Is there a legend of some sort that explains it? How did you come to learn what each item means in general?
Thanks,
Ben - bens401 9 years ago-
It looks to me like this report lists all the patches which are not installed on a given machine. This is not at all the same as patches which are missing. For example, the first PC listed on my report shows dozens of Office 2007 patches. However, the PC in question has Office 2010 installed, not Office 2007. - MichaelMc 9 years ago
Comments:
-
No takers?? - egas 8 years ago
