KACE SQL Patch mod
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.
Answers (1)
This looks familiar.
http://www.itninja.com/question/patch-reporting-question
Comments:
-
Take out the release date line of course. Unless you want that. - dugullett 11 years ago
-
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 - cwest311 11 years ago-
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 - dugullett 11 years ago
