/build/static/layout/Breadcrumb_cap_w.png

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?

EKdCCP.jpeg

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!


Asked 7 years ago 1057 views
2 Comments   [ + ] Show comments
  • Sanity check: you use an actual label in the 3rd to last line ("AND L.NAME = 'Label name'") and that label has devices (that are also in patch schedules) in it? When I removed the label line and ran the query in my environment, it looks like I get the desired results. - JasonEgg 7 years ago
  • Hi there, thanks for replying. The report is run against an actual label, I just edited it in the code above.

    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

Answers (0)

Be the first to answer this question

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ