Need machines not compliant by patch report for just patches in a label
There is a canned report "Machines not compliant by patch", but I need to limit it to just the patches in a particular label, say, "approved patches". How do I change the SQL to just look for the patches in that label? This should be trivial, but I can't get it.
Here is the SQL of the canned report:
SELECT PP.TITLE AS DISPLAY_NAME,
M.NAME AS ComputerName,
SYSTEM_DESCRIPTION, IP, MAC,
M.USER_LOGGED as USER_LOGGED,
CS_DOMAIN
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
ORDER BY PP.TITLE
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
Shootifitmoves
12 years ago
Solved it:
SELECT PP.TITLE AS DISPLAY_NAME
, M.NAME AS ComputerName
, M.IP
, M.MAC
, M.USER_LOGGED AS USER_LOGGED
FROM
ORG1.PATCHLINK_MACHINE_STATUS MS
INNER JOIN KBSYS.PATCHLINK_PATCH PP
ON PP.UID = MS.PATCHUID
INNER JOIN ORG1.MACHINE M
ON M.ID = MS.MACHINE_ID
INNER JOIN ORG1.PATCHLINK_PATCH_LABEL_JT
ON PATCHLINK_PATCH_LABEL_JT.PATCHUID = PP.UID
INNER JOIN ORG1.LABEL
ON PATCHLINK_PATCH_LABEL_JT.LABEL_ID = LABEL.ID
WHERE
MS.STATUS = 'NOTPATCHED'
AND LABEL.NAME = 'Patching - Approved Windows 7 Critical Patches'
ORDER BY
DISPLAY_NAME