Report for MS Office Version
I am trying to create a report to see which computer we have still have Office 2010 on them and which one have Office 2013. I tried using the wizard but it is not grabbing the correct info I need. What I would like to have it do is list every computer with Machine Name and User Name under the Software Version of Office. Any help with this will be greatly appreciated. Thank you.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
worzie
6 years ago
I have a couple methods and obviously there are always going to be better ways.
The first is using the report wizard:
- Title and Topic - topic is Software Catalog - Discovered Software
- Add Subtopic Device only show rows from both
- Under Discovered Software Info
- Select Name
- Under Device Identity Information
- Select System Name
- Under Operating System Info
- Select Name
- Column Order - leaving default
- Sort and Breaks - leaving default
- Filters
- Edit the default to Match all of the following
- Name contains Microsoft Office
- Product Name = Office
- Under Device - not specifying any rules to filter the records
Alternatively, I have an old SQL query that goes as follows:
SELECT DISPLAY_NAME, DISPLAY_VERSION, if(length(file_name)>0, 1, 0) as HAS_FILE, (SELECT group_concat(distinct if(LABEL.NAME not like 'HDN_LABEL_%', LABEL.NAME, 'System Hidden') ORDER BY LABEL.NAME separator '
') FROM SOFTWARE_LABEL_JT SLJT INNER JOIN LABEL ON SLJT.LABEL_ID = LABEL.ID WHERE SOFTWARE.ID = SLJT.SOFTWARE_ID) as LABEL_NAME, (SELECT group_concat(distinct MACHINE.NAME ORDER BY MACHINE.NAME separator '
') FROM MACHINE INNER JOIN MACHINE_SOFTWARE_JT MSJT ON MACHINE.ID = MSJT.MACHINE_ID WHERE MSJT.SOFTWARE_ID = SOFTWARE.ID) as MACHINE_NAME, (select count(distinct MACHINE_ID) from MACHINE_SOFTWARE_JT WHERE SOFTWARE_ID=SOFTWARE.ID) as MACHINES_AFFECTED, PUBLISHER, SOFTWARE.ID AS SOFTWARE_ID FROM SOFTWARE LEFT JOIN SOFTWARE_LABEL_JT ON (SOFTWARE_LABEL_JT.SOFTWARE_ID = SOFTWARE.ID) LEFT JOIN LABEL ON (LABEL.ID = SOFTWARE_LABEL_JT.LABEL_ID) LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID) LEFT JOIN MACHINE ON (MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID) WHERE PUBLISHER LIKE '%Microsoft%' AND DISPLAY_NAME LIKE '%Office%' AND DISPLAY_NAME NOT LIKE '%hotfix%' AND (1 in (select 1 from MACHINE, MACHINE_SOFTWARE_JT where SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID and MACHINE.NAME LIKE '%DNR%')) AND DISPLAY_NAME LIKE '%Office%' AND DISPLAY_VERSION NOT LIKE '%update%' GROUP BY SOFTWARE.ID ORDER BY DISPLAY_NAME asc,DISPLAY_VERSION asc
