/build/static/layout/Breadcrumb_cap_w.png

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:
Posted by: chucksteel 7 years ago
Red Belt
1

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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