Sql not reporting multiple label queries
I am trying to run a script that John was nice enough to share and I can get it to work only if I have one label in the query:
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
+SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'Server Group 1'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME
I would like to be able to put all six groups into one report if possible. I also would like the report to just show the last thirty days.
Now what would be awesome is if someone could point out how I could get this to show multiple labels, the patch that was installed and what critical patches were installed!
Answers (1)
Change this line
AND L.NAME = 'Server Group 1'
to this
AND L.NAME RLIKE 'Server Group 1|Server Group 2| Server Group 3'
Comments:
-
Just saw the comment about the last 30 days. Are you talking about machine check in the past 30 days, or patch release date in the past 30 days? - dugullett 10 years ago
-
DANG!!!! I forgot the rLike!!! D'oh!!
The patches that were released in the previous month. (Ie July's critcals) That way I can tell compliance the percentage each month. - cwest311 10 years ago-
Add this line for for patches released in the past 30 days.
AND PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 30 DAY) - dugullett 10 years ago-
What line do I need to add that to? When I place that line under the 'where' statement; nothing resolves.... - cwest311 10 years ago
-
I usually add lines like this to the first line in the WHERE.
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
+SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 30 DAY)
AND PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME like 'MACHINE_LABEL%'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME - dugullett 10 years ago
