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.
SELECT
M.NAME AS ComputerName,
IP,
OS_NAME,
M.USER_LOGGED AS USER_LOGGED,
P.PUBLISHER,
P.TITLE AS DISPLAY_NAME,
-- P.IMPACTID, --
P.SEVERITY,
MS.DETECT_STATUS,
-- PP.REBOOT, --
P.IS_SUPERCEDED,
-- P.RELEASEDATE, --
-- PP.IS_APP, --
P.INFO_URL,
P.PATCH_TYPE
FROM
PATCH_MACHINE_STATUS MS
JOIN
KBSYS.PATCH P ON P.ID = MS.PATCH_ID
JOIN
MACHINE M ON M.ID = MS.MACHINE_ID
WHERE
MS.DETECT_STATUS != 'PATCHED'
AND P.IS_SUPERCEDED = '0'
AND P.SEVERITY = 'Critical'
ORDER BY M.NAME
Answers (2)
I would suggest you establish the ID's for the smart labels that you want to reference and add to the WHERE statement where the smart label id = x or y or z
You may also need to add in P.ID to your select statement and then use that
Comments:
-
I really do wish I understood what you're saying but I'm very new to working with Kace SQL reporting. - poisedforflight 4 years ago
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.....
SELECT MACHINE.NAME,
MACHINE.IP,
MACHINE.USER_LOGGED,
PATCH.PUBLISHER,
PATCH.TITLE,
PATCH.SEVERITY,
PATCH_MACHINE_STATUS.DETECT_STATUS
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)
WHERE
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
Comments:
-
No worries. That returns 0 results, unfortunately. - poisedforflight 4 years ago
