Patch compliance report help k1000 version 8.0
Answers (2)
I found a report on here that I was able to use successfully to do something like what you are doing. The first step is to create a Smart Label with the filters you are looking for. Then Here is SQL code for a report that searches based on that Smart Label:
select M.NAME as NAME, SYSTEM_DESCRIPTION as Description, M.IP as IP_ADDRESS, KT.PHASE as STATUS, Concat('Patched: ', PS.PATCHED,' , ','Not Patched: ', PS.NOTPATCHED,' , ','Detect Failures: ', PS.DETECT_FAILURES) as PATCH_RESULTS, PS.LAST_RUN as LAST_RUN_DATE
from MACHINE M
left join PATCHLINK_SCHEDULE_MACHINE_STATUS PS on PS.MACHINE_ID = M.ID
left join PATCHLINK_SCHEDULE PSS on PSS.ID = PS.PATCHLINK_SCHEDULE_ID
left join KBSYS.KONDUCTOR_TASK KT on KT.KUID = M.KUID
where PSS.DESCRIPTION = 'YOURSMARTLABELNAMEHERE'
and KT.TYPE = PSS.KONDUCTOR_TASK_TYPE
ORDER BY M.NAME
Comments:
-
What is pss.description I tried a patch set label and a device label and it returned no data so I'm missing something here. - patchadams 6 years ago
-
I think the way to incorporate a device label would be:
SELECT M.NAME AS NAME, SYSTEM_DESCRIPTION AS Description, M.IP AS IP_ADDRESS, KT.PHASE AS STATUS, Concat('Patched: ', PS.PATCHED, ' , ', 'Not Patched: ', PS.NOTPATCHED, ' , ', 'Detect Failures: ', PS.DETECT_FAILURES) AS PATCH_RESULTS, PS.LAST_RUN AS LAST_RUN_DATE
FROM MACHINE M
JOIN MACHINE_LABEL_JT JT on JT.MACHINE_ID = M.ID
JOIN LABEL L on JT.LABEL_ID = L.ID
LEFT JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS PS ON PS.MACHINE_ID = M.ID
LEFT JOIN PATCHLINK_SCHEDULE PSS ON PSS.ID = PS.PATCHLINK_SCHEDULE_ID
LEFT JOIN KBSYS.KONDUCTOR_TASK KT ON KT.KUID = M.KUID
WHERE KT.TYPE = PSS.KONDUCTOR_TASK_TYPE
AND L.NAME = 'YOUR_LABEL_NAME'
ORDER BY M.NAME - JasonEgg 6 years ago
SELECT
M.NAME AS MACHINE_NAME,
M.ID,
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 MACHINE M
LEFT JOIN PATCHLINK_MACHINE_STATUS MS ON M.ID = MS.MACHINE_ID
LEFT JOIN KBSYS.PATCHLINK_PATCH PP ON MS.PATCHUID = PP.UID
LEFT JOIN PATCHLINK_PATCH_STATUS PPS ON PP.UID = PPS.PATCHUID
JOIN MACHINE_LABEL_JT ML ON M.ID = ML.MACHINE_ID
JOIN LABEL L ON ML.LABEL_ID = L.ID
WHERE L.NAME = 'YOUR_DEVICE_LABEL'
AND PP.IMPACTID = 'Critical'
AND PP.RELEASEDATE BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY)
AND PP.IS_SUPERCEDED = 0
AND PPS.STATUS = 0
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME
