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

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

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
Answered 05/24/2012 by: Shootifitmoves
Senior White Belt

Please log in to comment
Answer this question or Comment on this question for clarity