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
 
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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;
 
Answered 09/24/2013 by: joe.pyrczak
Orange Senior Belt

Please log in to comment
0

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'

 

Answered 09/18/2013 by: dugullett
Red Belt

  • 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
    • 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.
  • 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
    • Could it be because your are LEFT joining all those? Try just JOIN statements.
  • 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.
Please log in to comment
Answer this question or Comment on this question for clarity

Share