/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Report Assistance

04/19/2018 478 views
Hello,
I'm looking for advice on how to customize the existing Software Title & Version - Computer List report so that I might filter it by specific device labels (in our case, we need to be able to filter by Smart Labels that we have set up that filter the devices by departments).  The existing Query for the canned report is:

select CONCAT(DISPLAY_NAME, " (", DISPLAY_VERSION ,")") as Software_Title, MACHINE.NAME as Computer_Name, SYSTEM_DESCRIPTION, IP, MAC, CS_DOMAIN, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED from (SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE) where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID 
and not IS_PATCH
order by Software_Title


If someone could help me add the verbiage to include my label, I'd be extremely grateful.

Thanks as always!
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
Anytime that you want to filter on a label you need to add two join statements (at least):
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
Once those are in place you can add to the where clause:
and LABEL.NAME = "User Services"

Your full statement looks like this:
select CONCAT(DISPLAY_NAME, " (", DISPLAY_VERSION ,")") as Software_Title, 
MACHINE.NAME as Computer_Name, 
SYSTEM_DESCRIPTION, IP, MAC, CS_DOMAIN, 
REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED 
from (SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE) 
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID 
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID 
and not IS_PATCH
and LABEL.NAME = "User Services"
order by Software_Title

Answered 04/20/2018 by: chucksteel
Red Belt

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