/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Need a report to show all computers with only Office 2010 installed

10/21/2016 2249 views
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.
Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • any other suggestions rockhead44 or anyone else? report is still showing machines that also have 2013 or 2016 installed.

Answer Chosen by the Author

0

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.%'))
Answered 01/13/2017 by: JasonEgg
Red Belt

  • 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!!

All Answers

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
Answered 10/21/2016 by: rockhead44
Red Belt

  • Thanks rockhead!! What would I change SYSTEM_DESCRIPTION to in order to show the last logged in user?
    • Try USER or USER_FULLNAME
      • 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
 
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