K1000 Microsoft Patch Compliance Reports not Working After Version 7 Upgrade
Hi all,
I have recently upgraded my company's K1000 appliance to version 7 (7.0.121306). This has unfortunately broken the custom SQL reports that I had set up to display patch compliance. The reports still run without error but the data they contain is incorrect as it contains missing values (machine count is incorrect / Windows OS's missing).
To clarify what the report does it displays the Windows OS name, total machines for a specific OS, total installed / missing patches (Microsoft critical impact only) and a percent patched for each OS. Please refer to the screenshot further down, this was taken from when the report was functioning correctly:
Would any of you SQL guru's be able to chime in with any advice please or does anyone have a similar report that works in version 7?
Here's the SQL code:
select
m.OS_NAME AS WINDOWS_VERSION
, count(DISTINCT m.id) AS TotalMachines
, SUM(MS.STATUS='PATCHED') AS Total_Patches_Installed
, SUM(MS.STATUS='NOTPATCHED') AS Total_Patches_Missing
, ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED') +SUM(MS.STATUS='NOTPATCHED')))*100,000) AS PERCENT_PATCHED
from
MACHINE as m JOIN MACHINE_LABEL_JT ML ON
(m.ID = ML.MACHINE_ID)
JOIN LABEL L ON
(ML.LABEL_ID = L.ID)
JOIN PATCHLINK_MACHINE_STATUS MS 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
PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'Label name'
AND PP.VENDOR = 'Microsoft Corp.'
group by m.OS_NAME
Thanks very much in advance!
2 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
This issue has been resolved! I believe that data in the PATCHLINK_MACHINE_STATUS table got purged during the V7 upgrade. I re-ran a patch detect on all devices which resolved the issue. - Joe27 7 years ago