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