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.
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
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

Answered 02/16/2017 by: chucksteel
Red Belt

  • Exactly what I was looking for! report ran perfectly. Thank you for your help.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share