All,

I have this script but I am having a little difficulty trying to plugin a label to query this against. This is the script I have so far:

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 would like to run this query again two labels that I have:

1. All workstations

2. All Servers

I currently am only patching M$, Adobe and Java. I dont know if that matters or not. I think its awesome that KACE shows me a kewl little graphic on the 'security' tab but I have to break that percentage down a little further. Hopefully its just an oversight on my part.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

This looks familiar. 

http://www.itninja.com/question/patch-reporting-question

Answered 06/18/2013 by: dugullett
Red Belt

  • Take out the release date line of course. Unless you want that.
    • Well now im even more confused..... I took that script and ran it and they both came back with the same results?!?!? The graphic says the environment is 93% patched and I am getting a percentage of 72-73 with this scripts. Also; I have over 730 servers and over 10,5K workstations. I dont think that the query is running the way it is supposed to... Any ideas? Can you also tell me what the '11' are that is in the count column but nothing in status? I attached a copy of both scripts:

      Servers:

      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 ='All Servers'
      group by MS.STATUS

      Returns:
      : 11
      NotPatched: 159866
      Patched: 2211155

      Workstations:

      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 ='All workstations'
      group by MS.STATUS

      Returns:
      : 11
      NotPatched: 159866
      Patched: 2211155
      • Try this. I made some changes. I'm not sure why this would work, and the other would not. I'm thinking the LEFT JOIN needed to be used, but I'm still learning myself. Keep in mind with the "AND P.RELEASEDATE < DATE_SUB(NOW(), INTERVAL 30 DAY)" this will only include patches that are 30+ days old so it will not match the graphic. If you take that line out, it should be more accurate.

        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
Please log in to comment
Answer this question or Comment on this question for clarity
Nine Simple (but Critical) Tips for Effective Patch Management
This paper reviews nine simple tips that can make patch management simpler, more effective and less expensive.

Share