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:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

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!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share