K1000 Patch Reporting Question
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
Answers (1)
Please log in to answer
Posted by:
dugullett
10 years ago
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
Comments:
-
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 - dugullett 10 years ago -
Thanks, dugullett! This works perfectly! - gcounce 10 years ago
-
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. - gcounce 10 years ago-
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) - dugullett 10 years ago
