How do I limit this SQL Query to either a single or multiple Smart Labels?
I am trying to create a report that lists patches missing from machines assigned to a Smart Label or multiple Smart Labels.
This is the code I'm using so far which returns ALL devices. I just need to know how to limit it to label(s).
For instance, I want to run it against devices belonging to the "Devices - Windows Servers" Smart Label.
M.NAME AS ComputerName,
M.USER_LOGGED AS USER_LOGGED,
P.TITLE AS DISPLAY_NAME,
-- P.IMPACTID, --
-- PP.REBOOT, --
-- P.RELEASEDATE, --
-- PP.IS_APP, --
KBSYS.PATCH P ON P.ID = MS.PATCH_ID
MACHINE M ON M.ID = MS.MACHINE_ID
MS.DETECT_STATUS != 'PATCHED'
AND P.IS_SUPERCEDED = '0'
AND P.SEVERITY = 'Critical'
ORDER BY M.NAME
Ok so in your KACE SMA go to Home > Label Management . Smart labels
and hover over any label name.
Note in the bottom left hand corner of your screen some text appears, that is the ID number in the database for your smart label, as per this picture
So now in your report you can restrict by the label ID.
Maybe your SQL should look a bit like.....
FROM ((ORG1.MACHINE MACHINE
INNER JOIN ORG1.MACHINE_LABEL_JT MACHINE_LABEL_JT
ON (MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID))
INNER JOIN ORG1.PATCH_MACHINE_STATUS PATCH_MACHINE_STATUS
ON (PATCH_MACHINE_STATUS.MACHINE_ID = MACHINE.ID))
INNER JOIN KBSYS.PATCH PATCH
ON (PATCH.ID = PATCH_MACHINE_STATUS.PATCH_ID)
PATCH_MACHINE_STATUS.DETECT_STATUS != 'PATCHED'
AND PATCH.SEVERITY = 'Critical'
AND MACHINE_LABEL_JT.LABEL_ID = 'the label ID from the screenshot"
ORDER BY MACHINE.NAME
But to be fair, that's a quick response and I haven't had time to test the code, apologies