How to Add User Name
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
Answers (2)
Please log in to answer
Posted by:
scottlutz
12 years ago
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.
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.
Posted by:
GillySpy
12 years ago
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:
But there is no guarantee the user names will line up because of missing names or same user on more than one machine, etc
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
