/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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