SQL Query Assistance for reporting
I have the following query that I am using to get me all the computers that have Microsoft Office Enterprise 2007 installed.
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 'Microsoft Office Enterprise 2007%'
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
If I want to add the following software on the same report:
Microsoft Office Professional Edition 2003
Microsoft Office Professional Plus 2007
Microsoft Office Professional Plus 2010
I want to do one report that will show me all the versions of Microsoft Office installed on one report.
THX
-
Thanks this is perfect. Thanks for the help. - Mariusja 10 years ago
Answers (1)
Change this line
where E3.DISPLAY_NAME like 'Microsoft Office Enterprise 2007%'
to this
WHERE E3.DISPLAY_NAME RLIKE 'Microsoft Office Enterprise 2007|Microsoft Office Professional Edition 2003|Microsoft Office Professional Plus 2007|Microsoft Office Professional Plus 2010'
