Modifying an existing report to target a specific label
Hi all,
I have an existing report that allows me to see inventory PCs older than 4 years.
SELECT MACHINE.NAME, CS_MODEL, BIOS_SERIAL_NUMBER, DA.SHIP_DATE, USER_FULLNAME, USER_LOGGED, MACHINE.OS_NAME
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
HAVING DA.SHIP_DATE < DATE_SUB(NOW(), INTERVAL 4 YEAR)
ORDER BY DA.SHIP_DATE, MACHINE.NAME
This works perfectly, but now I need to modify the report so the results only shows those PC that are members of a specific label (e.g. 'staff'). Does anyone know how to implement this in the report above? I've done some research and tried various things on my own, but my SQL knowledge isn't great and I can't seem to get it.
Thanks for any help.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
You need to add two join statements to the MACHINE_LABEL_JT and then to the LABEL tables:
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
Place those after the join to the DELL_ASSET table.
You can now add a statement to the where clause for the specific label:
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
AND LABEL.NAME = "staff"
Comments:
-
That's fantastic, thanks so much. It works great! - seanboy 7 years ago
