Report to Show Missing Patches on Devices that are within a Label
I have this SQL Query:
Select CASE WHEN MACHINE.SYSTEM_DESCRIPTION = '' THEN MACHINE.NAME WHEN MACHINE.SYSTEM_DESCRIPTION != '' THEN CONCAT(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) END AS MACHINE_NAME,
P.TITLE AS DISPLAY_NAME, P.IDENTIFIER as KB_ARTICLE, P.VENDOR as Publisher, P.IMPACTID as Impact, S.STATUS as Status from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P
where
MACHINE.ID = S.MACHINE_ID and
S.STATUS = 'NOTPATCHED' and
S.PATCHUID = P.UID and
P.IS_SUPERCEDED = '0'
order by MACHINE_NAME, P.TITLE
Which shows every single server w/ a missing patch but how would I tweak this to show only servers under a certain label?
LABEL.NAME in ('Label1','Label2')
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
5 years ago
Top Answer
Select CASE
WHEN MACHINE.SYSTEM_DESCRIPTION = ''
THEN MACHINE.NAME
WHEN MACHINE.SYSTEM_DESCRIPTION != ''
THEN CONCAT(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION)
END
AS MACHINE_NAME,P.TITLE AS DISPLAY_NAME, P.IDENTIFIER as KB_ARTICLE
from PATCHLINK_MACHINE_STATUS S
JOIN MACHINE on MACHINE.ID = S.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH P on S.PATCHUID = P.UID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
where
S.STATUS = 'NOTPATCHED'
and LABEL.NAME = "User Services"
order by MACHINE_NAME, P.TITLE
Comments:
-
Thank you so much. This was driving me crazy and you made this look so simple. (Which it probably is for people w/ SQL skills) - lama01 5 years ago
