/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Report to Show Missing Patches on Devices that are within a Label

01/07/2019 1210 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

2
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)
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ