K1000 SQL Query using grouped labels for Software
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