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!


2 Comments   [ + ] Show Comments

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.
  • 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.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity