/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (2)

Posted by: Hobbsy 4 years ago
Red Belt
1

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
Posted by: Hobbsy 4 years ago
Red Belt
1

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

Pv3380UP3t0l7MHAAAAABJRU5ErkJggg==

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