I want a K1000 report that will give a list of all user that have specific software installed
I want a report that will give a list of all user that have specific software installed. I did manage to do the report until here but I don’t know how to add the user full name on this report. Can someone please assist me with this?
select E1.NAME as MACHINE,E1.IP,E1.CS_MODEL,E1.BIOS_SERIAL_NUMBER,E1.USER_LOGGED,E1.OS_NAME,E1.SERVICE_PACK,E3.DISPLAY_NAME,E3.DISPLAY_VERSION
from MACHINE E1
inner join MACHINE_SOFTWARE_JT E2
on E2.MACHINE_ID = E1.ID
inner join SOFTWARE E3
on E3.ID = E2.SOFTWARE_ID
inner join MACHINE_LABEL_JT E4
on E4.MACHINE_ID = E1.ID
inner join LABEL E5
on E5.ID = E4.LABEL_ID
where E3.DISPLAY_NAME like 'PROXY Pro Master%'
and E3.IS_PATCH= 0
GROUP BY E1.NAME,E1.IP,E1.CS_MODEL,E1.BIOS_SERIAL_NUMBER,E1.USER_LOGGED,E1.OS_NAME,E1.SERVICE_PACK,E3.DISPLAY_NAME,E3.DISPLAY_VERSION
Answers (4)
Chuck's answer is definitely valid. The implication would be that the User's full name is populated. That depends entirely on how you have setup your users -- LDAP customers or not. Ideally your environment is setup such that it is that way, but what if it's not or what if you don't want to join the USER table. There is a cost to another join.
Here's another way, in the MACHINE table (inventory snapshot) we capture the following columns:
USER
USER_FULLNAME
USER_LOGGED
USER_NAME
USER_DOMAIN
if USER_FULLNAME was a reliable source of information then you could use that. Your query would become:
select
E1.NAME as MACHINE,
E1.IP,
E1.CS_MODEL,
E1.BIOS_SERIAL_NUMBER,
E1.USER_LOGGED,
E1.USER_FULLNAME, -- added
E1.OS_NAME,
E1.SERVICE_PACK,
E3.DISPLAY_NAME,
E3.DISPLAY_VERSION
from
MACHINE E1
join MACHINE_SOFTWARE_JT E2 on E2.MACHINE_ID = E1.ID
join SOFTWARE E3 on E3.ID = E2.SOFTWARE_ID
where
E3.DISPLAY_NAME like 'PROXY Pro Master%'
and E3.IS_PATCH= 0
note: i also took the liberty of:
- removing the unnecessary join to the LABEL table
- removing the unnecessary group by that was probably there because of the label table join
P.S. You may get some higher quality data and make this report easier to maintina by putting all of your "PROXY Pro Master" titles into a label and joining in that sotware label instead of using DISPLAY_NAME
You need to add a join to the USER table:
join USER on E1.USER_LOGGED = USER.USER_NAME
And then add a select for USER.FULL_NAME
When I use this query I get the following sql Error:
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1] in EXECUTE(
"select E1.NAME as MACHINE, E1.IP, E1.CS_MODEL, E1.BIOS_SERIAL_NUMBER, E1.USER_LOGGED, E1.USER_FULLNAME, -- added E1.OS_NAME, E1.SERVICE_PACK, E3.DISPLAY_NAME, E3.DISPLAY_VERSIONfrom MACHINE E1 join MACHINE_SOFTWARE_JT E2 on E2.MACHINE_ID = E1.ID join SOFTWARE E3 on E3.ID = E2.SOFTWARE_IDwhere E3.DISPLAY_NAME like 'PROXY Pro Master%'and E3.IS_PATCH= 0 LIMIT 0")
Comments:
-
Take out the --added. On the second line there should be a space in between E3.DISPLAY_VERSION and FROM. Also on the last line there should be a space in between E2.SOFTWARE_ID and WHERE. Also take off the LIMIT 0. I'm not sure if this a format issue when you copied and pasted?
select E1.NAME as MACHINE, E1.IP, E1.CS_MODEL, E1.BIOS_SERIAL_NUMBER,
E1.USER_LOGGED, E1.USER_FULLNAME, E1.OS_NAME, E1.SERVICE_PACK, E3.DISPLAY_NAME, E3.DISPLAY_VERSION
from MACHINE E1
join MACHINE_SOFTWARE_JT E2 on E2.MACHINE_ID = E1.ID
join SOFTWARE E3 on E3.ID = E2.SOFTWARE_ID
where E3.DISPLAY_NAME like 'PROXY Pro Master%'and E3.IS_PATCH= 0 - dugullett 11 years ago