/build/static/layout/Breadcrumb_cap_w.png

Patch compliance report help k1000 version 8.0

Hi all the report below seems to be returning all kinds of patches that are not installed for each host in the device label.  I was wondering if someone could help me modify it so I get only the following patches reported on in the format this one gives me:

The patching parameters I would like returned are:
release date within the last 30 days and
superseded = no and
type=security

The report below seems to have some of those but it's really returning just everything that needs to be installed.  I'm not sure why.

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 AND PP.IMPACTID = 'Critical' 
AND RELEASEDATE between DATE_SUB(NOW(),INTERVAL 30 DAY) and DATE_SUB(NOW(),INTERVAL 1 DAY)
AND PP.IS_SUPERCEDED = 0)
LEFT JOIN PATCHLINK_PATCH_STATUS PPS ON (PP.UID = PPS.PATCHUID AND PPS.STATUS = 0)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE L.NAME = device-label'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME

0 Comments   [ + ] Show comments

Answers (2)

Posted by: DaveMT 5 years ago
4th Degree Black Belt
1

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 5 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 5 years ago
Posted by: JasonEgg 5 years ago
Red Belt
0
How about this? I just re-worked your original query to move the criteria from the JOIN ON clauses to the WHERE clause:
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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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