/build/static/layout/Breadcrumb_cap_w.png

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.

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 7 years ago

Answers (2)

Answer Summary:
Posted by: JasonEgg 7 years ago
Red Belt
0

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 7 years ago
Red Belt
0
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 7 years ago
    • Try USER or USER_FULLNAME - rockhead44 7 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 7 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ