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:
Please log in to answer
Posted by:
chucksteel
7 years ago
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 2 years 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 2 years ago
