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,
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!