Is there anyway that I can have the output of a report include any smart labels that have been applied to a machine? I have a report that runs to locate specific software and then return the computer name and IP address. It works great, but being able to include the smart labels would be exactly what I need. Any help is greatly appreciated.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

This would work.

SELECT DISTINCT M.NAME, IP,

GROUP_CONCAT(DISTINCT L.NAME SEPARATOR '\n') AS LABEL_NAME

FROM MACHINE M

LEFT JOIN MACHINE_LABEL_JT ON M.ID = MACHINE_LABEL_JT.MACHINE_ID

LEFT JOIN LABEL L ON MACHINE_LABEL_JT.LABEL_ID = L.ID

GROUP BY M.NAME

ORDER BY M.NAME
Answered 10/08/2012 by: dugullett
Red Belt

Please log in to comment

Answers

1

You can also restrict the labels that are returned by using a WHERE statement.  For example, I have a bunch of location labels (Pittsburgh computers, Marelan computers, etc) and to return just those labels, I would tweak dgullett's query like this so that only the "computers" labels would be listed:

SELECT DISTINCT M.NAME, IP,
L.NAME AS LABEL_NAME
FROM MACHINE M
LEFT JOIN MACHINE_LABEL_JT ON M.ID = MACHINE_LABEL_JT.MACHINE_ID
LEFT JOIN LABEL L ON MACHINE_LABEL_JT.LABEL_ID = L.ID
WHERE L.NAME rlike 'computers'
GROUP BY M.NAME
ORDER BY M.NAME

If your goal is truly to return *all* of your labels, then dgullett's approach works great.

John

Answered 10/09/2012 by: jverbosk
Red Belt

Please log in to comment
1

Another trick is to only select labels that are in a label group. We use LDAP labels for department and all of those are in the Departments label group (which has an ID of 258). The following query just includes labels in that group:

 SELECT IP,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS DEPARTMENT,
MACHINE.NAME AS SYSTEM_NAME,MACHINE.USER_NAME,USER_LOGGED,USER_FULLNAME
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 = 258)
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 
WHERE LABEL_LABEL_JT.LABEL_ID = 258
GROUP BY MACHINE.ID
ORDER BY SYSTEM_NAME

The label group is specified as part of the LABEL_LABEL_JT join statement.

Answered 10/09/2012 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity