K1000 SQL Query using grouped labels for Software
Hi Everyone,
I am trying to build on my previous query regarding listing computers in each location which are in a grouped smart label called "Locations". Now, I am trying to do the same list of computers, but only for one software package.
My goal is to list all the systems with software X installed, broken up by the Location labels which are created by subnet.
I am getting more than 1000 results, whenI should get no more than 264.
Here is the query I am have, bear in mind I am still pretty new at this. If anyone could offer some suggestions? Thanks
# Select Columns to gather information from SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.USER as LAST_LOGGED_ON, GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS SUBNET # Gathering information from the below Tables FROM MACHINE 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') LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 130) LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID LEFT JOIN SOFTWARE ON SOFTWARE.CONTACT = ASSET.OWNER_ID # Use a LEFT JOIN to access another table and columns #LEFT JOIN DELL_ASSET DA on DA.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER WHERE LABEL_LABEL_JT.LABEL_ID = 130 # Display all of Software X, broken up by subnet name AND SOFTWARE.DISPLAY_NAME LIKE '%Software X%' GROUP BY MACHINE.ID # Ordered by Subnet smart labels ORDER BY LABEL.NAME
Answers (2)
Here is the final SQL script that I ended up using. Someone here at work had a bit of secret SQL knowledge :)
SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.USER AS LAST_LOGGED_ON, LABEL.NAME AS LOCATION FROM ORG1.MACHINE #Connect the software and machine tables JOIN MACHINE_SOFTWARE_JT ON (MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID) #To get the location you will need the location label JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID) JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID) #displays the location name based on the label child label id JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 130) #the following 3 software id's are SAP GUI 7.20 for Windows WHERE MACHINE_SOFTWARE_JT.SOFTWARE_ID = 1178 OR MACHINE_SOFTWARE_JT.SOFTWARE_ID = 2312 OR MACHINE_SOFTWARE_JT.SOFTWARE_ID = 3791 #Remove duplicate entries with the group by clause GROUP BY MACHINE.NAME;
A couple of things you may want to try. First add DISTINCT in from of MACHINE.NAME in you first line.
SELECT DISTINCT MACHINE.NAME AS SYSTEM_NAME
You may also want to search for exactly the software name. If you are searching for '%office%' for example you will get a lot more results than searching for
SOFTWARE.DISPLAY_NAME = 'MICROSOFT OFFICE PROFESSIONAL PLUS 2010'
Comments:
-
Hi, Greatly appreciate the help.
I added the DISTINCT as suggested, also the actual display name is "SAP GUI for Windows 7" and I am testing via a search for the number of actual installs which is 264, but the report is still showing 1440 systems. :(
Thanks - joe.pyrczak 11 years ago-
Take this line out GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS SUBNET. Since you are specifying the label ID of 130 there's no reason to have it. - dugullett 11 years ago
-
Ok, great. I added to the select:
LABEL.NAME AS LOCATION ... So I can break on that. However I still get 1440 rows.
I do have a query without the location that does report the 264 systems with the software installed. Any idea what I messed up? I thought it might be the LEFT JOIN for the SOFTWARE table?
Joe - joe.pyrczak 11 years ago-
Could it be because your are LEFT joining all those? Try just JOIN statements. - Wildwolfay 11 years ago
-
and then it does not work :( . Sorry, I am not sure if I should just use JOIN on all or just certain ones. Tried all, then 0 results. - joe.pyrczak 11 years ago