/build/static/layout/Breadcrumb_cap_w.png

Report showing computers with office 2k10, 2k13 which have checked in within the last 60 days

Good morning everyone.

I was wondering is someone could help me tweak a report the former sysadmin has on the K1000. My boss has asked for a report outlining all computers in our network that are still using office 2010 and 2013. with us moving to 365 we want everyone standardized. The issue I have is the report is showing computers that I know for a fact are no longer operational. (Yes, an inventory cleanup is desperately needed and it is on my todo list. The last guy aparently didn't care to mark dead systems as disposed in kace.) 

Here is what I am currently using (found it here)

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 '15.%' 
  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.%'))

Can someone show me how to tweak it to show 2010, 2013 but only for computers that checked in within the last 60 days?

thanks a lot.

0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 7 years ago
Red Belt
1

Top Answer

I use a different technique to find software installed on computers so my query is a bit different:
SELECT MACHINE.NAME AS SYSTEM_NAME, LAST_SYNC,
USER_LOGGED, 
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_DISPLAY_NAME_GROUPED ,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_VERSION SEPARATOR '\n') AS SOFTWARE_DISPLAY_VERSION_GROUPED
FROM MACHINE  
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) 
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) 
WHERE SOFTWARE.DISPLAY_NAME like '%Microsoft Office Professional%'
and substring_index(SOFTWARE.DISPLAY_VERSION, ",", 1) < 16
and LAST_SYNC > DATE_SUB(NOW(), INTERVAL 60 DAY)
GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME


Comments:
  • Exactly what I was looking for! report ran perfectly. Thank you for your help. - maniakmyke 7 years ago
    • Hello thanks for your script, excuse me but i need your help, add display software Microsoft Office Standard how add line in the sql code thanks for your help - kriz96 1 year ago
      • If you want just Standard and not Professional, then change this line:
        WHERE SOFTWARE.DISPLAY_NAME like '%Microsoft Office Professional%'
        To
        WHERE SOFTWARE.DISPLAY_NAME like '%Microsoft Office Standard%' - chucksteel 1 year 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