/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Exclude Smart Label from KACE Report

04/19/2016 1115 views
Hi, 

We are running a report to get all machines, service tag, model and warranty end date.  

SELECT 
DW.SERVICE_TAG, 
DA.SHIP_DATE, 
DA.MACHINE_DESCRIPTION, 
DW.END_DATE, 
DW.START_DATE, 
M.ID as MACHINE_ID, 
M.NAME, 
M.BIOS_SERIAL_NUMBER, 
M.MAC, 
M.IP 
FROM 
DELL_WARRANTY DW 
left join 
MACHINE M ON M.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG 
left join 
DELL_ASSET DA ON DW.SERVICE_TAG in (DA.SERVICE_TAG , DA.PARENT_SERVICE_TAG) 
GROUP BY SERVICE_TAG 
ORDER BY END_DATE

How would I go about excluding a set of Smart Labels or Labels from the SQL above?

Thanks!

Adam
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

2
You need to add joins to the MACHINE_LABEL_JT and the LABEL tables:
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)  
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') 

Then add a group_concat to get the labels in a column:
GROUP_CONCAT(LABEL.NAME) as "Labels"

Then use a HAVING clause to eliminate machines with a specific label:
HAVING LABELS not like "%Desktop%"

The end result looks like this:
SELECT 
DW.SERVICE_TAG, 
DA.SHIP_DATE, 
DA.MACHINE_DESCRIPTION, 
DW.END_DATE, 
DW.START_DATE, 
M.ID as MACHINE_ID, 
M.NAME, 
M.BIOS_SERIAL_NUMBER, 
M.MAC, 
M.IP,
GROUP_CONCAT(LABEL.NAME) as "Labels"
FROM 
DELL_WARRANTY DW 
left join 
MACHINE M ON M.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG 
left join 
DELL_ASSET DA ON DW.SERVICE_TAG in (DA.SERVICE_TAG , DA.PARENT_SERVICE_TAG) 
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = M.ID)  
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden')
GROUP BY SERVICE_TAG 
HAVING LABELS not like "%Desktop%"
ORDER BY END_DATE
At least that's how I would do it.


Answered 04/19/2016 by: chucksteel
Red Belt

  • Thanks very much for this! That works perfectly!
 
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