/build/static/layout/Breadcrumb_cap_w.png
01/07/2019 150 views

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')

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
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


Answered 01/08/2019 by: chucksteel
Red Belt

  • 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)