I'm wondering how I would go about adding the username to this report. I'd like to match it with the machine name.

SELECT DISPLAY_NAME,PUBLISHER,
GROUP_CONCAT(DISTINCT MACHINE.NAME SEPARATOR '\n') AS MACHINE_NAME_GROUPED,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,
URLINFO_ABOUT
FROM SOFTWARE
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID)
LEFT JOIN MACHINE ON (MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)
LEFT JOIN SOFTWARE_LABEL_JT ON (SOFTWARE_LABEL_JT.SOFTWARE_ID = SOFTWARE.ID)
LEFT JOIN LABEL ON (LABEL.ID = SOFTWARE_LABEL_JT.LABEL_ID)
WHERE (NOT SOFTWARE.IS_PATCH) AND ((MACHINE.NAME is not null) AND (LABEL.NAME is null))
GROUP BY SOFTWARE.ID
ORDER BY DISPLAY_NAME,MACHINE_NAME_GROUPED
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Tayana,

Since you are already pulling from the machine table, you should just be able to add MACHINE.USER to your select, since it also lives in there.
Answered 02/03/2012 by: scottlutz
Orange Senior Belt

Please log in to comment
0
What you want could be in MACHINE.USER, MACHINE.USER_FULLNAME, MACHINE.USER_LOGGED, MACHINE.USER_NAME

But since you are grouping on machines the MACHINE_NAME_GROUPED contains a list of machines. How would you want to show users? How are you expecting to match them up ?

You might be able to use:
SELECT DISPLAY_NAME,PUBLISHER,
GROUP_CONCAT(DISTINCT MACHINE.NAME ORDER BY MACHINE.ID SEPARATOR '\n' ) AS MACHINE_NAME_GROUPED,
GROUP_CONCAT(DISTINCT MACHINE.USER_LOGGED ORDER BY MACHINE.ID SEPARATOR '\n' ) AS USER_NAME_GROUPED,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,
...


But there is no guarantee the user names will line up because of missing names or same user on more than one machine, etc
Answered 02/03/2012 by: GillySpy
Seventh Degree Black Belt

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