Exclude Smart Label from KACE Report
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
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top Answer
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.
Comments:
-
Thanks very much for this! That works perfectly! - ABall92 8 years ago
