Report Assistance
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!
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
Top Answer
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_TitleComments:
-
As always, you are the man! - stickman00 6 years ago
