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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

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

 

Answered 01/25/2013 by: GillySpy
Seventh Degree Black Belt

Please log in to comment

Answers

2

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

 

Answered 01/25/2013 by: chucksteel
Red Belt

Please log in to comment
0

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")

Answered 01/28/2013 by: Mariusja
Second Degree Green Belt

  • 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
Please log in to comment
0

This is working a 100% thanks a lot!!

Answered 01/28/2013 by: Mariusja
Second Degree Green Belt

  • If you download the MYSQL workbench it's pretty good at showing you the typos.

    http://www.mysql.com/downloads/workbench/
Please log in to comment
Answer this question or Comment on this question for clarity