I have a SQL query that tells me total number of Critical patches deployed and not deployed.  The query I am using is:

 

select MS.STATUS, count(*) AS COUNT
from PATCHLINK_MACHINE_STATUS MS,
            PATCHLINK_PATCH_STATUS PS,
            KBSYS.PATCHLINK_PATCH P
where MS.DETECT_ERROR_CODE = 0
and MS.PATCHUID = P.UID
and MS.PATCHUID = PS.PATCHUID
and PS.STATUS != 1
and P.IMPACTID like 'Critical%%'
group by MS.STATUS

 

I have been asked to take this report a little further, but my SQL skills are lacking.  Can you help me do the following:

  • Modify this query to only display information for Critical patches that are over 30 days old.
  • Modify this query for only workstation Critical patches.
  • Modify this query for only server Critical patches.

Ultimately, I want to end up with two SQL queries.

  • All workstation Critical patch results older than 30 days - Patched/Not Patched
  • All server Critical patch results older than 30 days - Patched/Not Patched

Thank you in advance for any help you can offer!

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Do you have machine labels setup for your servers/workstations? If so something like this would work.

select MS.STATUS, count(*) AS COUNT

from PATCHLINK_MACHINE_STATUS MS, 

            PATCHLINK_PATCH_STATUS PS, 

            KBSYS.PATCHLINK_PATCH P,

            LABEL L

where MS.DETECT_ERROR_CODE = 0

and MS.PATCHUID = P.UID

and MS.PATCHUID = PS.PATCHUID

and PS.STATUS != 1

and P.IMPACTID = 'Critical'

AND P.RELEASEDATE < DATE_SUB(NOW(), INTERVAL 30 DAY)

AND L.NAME = '<SERVER/WORKSTATION_MACHINE_LABEL_NAME>'

group by MS.STATUS
Answered 06/14/2013 by: dugullett
Red Belt

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • This should work better.

    select MS.STATUS, count(*) AS COUNT
    from PATCHLINK_MACHINE_STATUS MS
    LEFT JOIN PATCHLINK_PATCH_STATUS PS ON PS.PATCHUID=MS.PATCHUID
    LEFT JOIN KBSYS.PATCHLINK_PATCH P ON P.UID=PS.PATCHUID
    LEFT JOIN MACHINE M ON M.ID = MS.MACHINE_ID
    LEFT JOIN MACHINE_LABEL_JT MLJT ON M.ID=MLJT.MACHINE_ID
    LEFT JOIN LABEL L ON L.ID=MLJT.LABEL_ID
    where MS.DETECT_ERROR_CODE = 0
    and PS.STATUS = 0
    and P.IMPACTID = 'Critical'
    AND L.NAME = '<LABEL NAME>'
    AND P.RELEASEDATE < DATE_SUB(NOW(), INTERVAL 30 DAY)
    group by MS.STATUS
  • Thanks, dugullett! This works perfectly!
  • The data I am getting using the second query appears to be valid, but I have a couple of questions...

    Is this getting patches with critical impact or patches with critical severity? Also, is there a way to see what patches it is including in these counts? A secondary query would be fine for this validation if it can't be included in the original.
    • It will return the critical impact. If you run the query below it will show the patches. Kace also has some pretty good built in patching reports you might want to check.

      select DISTINCT P.TITLE
      from PATCHLINK_MACHINE_STATUS MS
      LEFT JOIN PATCHLINK_PATCH_STATUS PS ON PS.PATCHUID=MS.PATCHUID
      LEFT JOIN KBSYS.PATCHLINK_PATCH P ON P.UID=PS.PATCHUID
      LEFT JOIN MACHINE M ON M.ID = MS.MACHINE_ID
      LEFT JOIN MACHINE_LABEL_JT MLJT ON M.ID=MLJT.MACHINE_ID
      LEFT JOIN LABEL L ON L.ID=MLJT.LABEL_ID
      where MS.DETECT_ERROR_CODE = 0
      and PS.STATUS = 0
      and P.IMPACTID = 'Critical'
      AND L.NAME = '<LABEL>'
      AND P.RELEASEDATE < DATE_SUB(NOW(), INTERVAL 30 DAY)
Please log in to comment
Answer this question or Comment on this question for clarity

Share