Need a report to show all computers with only Office 2010 installed
Hi everyone,
Racking my brain over this and can't get it to work. I'm sure it could be done in SQL but I don't know SQL well enough to figure it out. Here's what I need...
We have a few scattered users in our company that are still running Office 2010 ONLY. We have others who have 2010 strictly for some excel usage, but have 2013 or 16 for their main office version. I need a report that will show me the computers that ONLY have Microsoft Office Professional Plus 2010. If they have 2013 or 16 installed then they are not shown in the report.
Thanks for any help, much appreciated.
Racking my brain over this and can't get it to work. I'm sure it could be done in SQL but I don't know SQL well enough to figure it out. Here's what I need...
We have a few scattered users in our company that are still running Office 2010 ONLY. We have others who have 2010 strictly for some excel usage, but have 2013 or 16 for their main office version. I need a report that will show me the computers that ONLY have Microsoft Office Professional Plus 2010. If they have 2013 or 16 installed then they are not shown in the report.
Thanks for any help, much appreciated.
1 Comment
[ + ] Show comment
-
any other suggestions rockhead44 or anyone else? report is still showing machines that also have 2013 or 2016 installed. - j.hough_FNP 8 years ago
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
JasonEgg
7 years ago
Top Answer
Not the prettiest or most efficient, but it works:
SELECT DISTINCT M.*
FROM MACHINE M
join MACHINE_SOFTWARE_JT SJT on M.ID = SJT.MACHINE_ID
join SOFTWARE S on S.ID = SJT.SOFTWARE_ID
WHERE S.PUBLISHER = 'Microsoft Corporation'
and S.DISPLAY_NAME like '%Office%'
and S.DISPLAY_VERSION like '14.%'
and S.DISPLAY_NAME != 'Microsoft Office File Validation Add-In'
and M.ID not in (select DISTINCT M.ID
from MACHINE M
join MACHINE_SOFTWARE_JT SJT on M.ID = SJT.MACHINE_ID
join SOFTWARE S on S.ID = SJT.SOFTWARE_ID
where S.PUBLISHER = 'Microsoft Corporation'
and S.DISPLAY_NAME like '%Office%'
and (S.DISPLAY_VERSION like '15.%' OR S.DISPLAY_VERSION like '16.%'))
Comments:
-
Thanks Jason, that seems to work perfectly. Returned 8 results and the first 2 only had office 2010 on them so I think it's good :). Thanks!! - j.hough_FNP 7 years ago
Posted by:
rockhead44
8 years ago
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE ((( exists (select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME = 'Microsoft Office 2010')) ))
Adjust SOFTWARE.DISPLAY.NAME= to meet your exact needs
Comments:
-
Thanks rockhead!! What would I change SYSTEM_DESCRIPTION to in order to show the last logged in user? - j.hough_FNP 8 years ago
-
Try USER or USER_FULLNAME - rockhead44 8 years ago
-
ok that did change it to the username. so thanks for that.
but, the first 2 machines that I checked on the results also have Office 2013 installed - j.hough_FNP 8 years ago
