Thursday, October 17, 2013

Custom SQL query to report MS-Excel and MS-Access

Here is a custom Report to list only MS-Excel (excel.exe) and MS-Access (msaccess.exe) with Office 2003, 2007 and 2010 versions. You can also use to query by editing it to fetch other components of Microsoft Office.

SELECT b.Netbios_Name0,
b.User_Name0,
CASE WHEN a.FileVersion LIKE '11.%' THEN 'Office 2003'
WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007'
WHEN a.FileVersion LIKE '14.%' THEN 'Office 2010'
ELSE 'NULL'
END AS 'Office Version',
a.FileName,
a.FileVersion
FROM v_GS_SoftwareFile a
JOIN v_R_System b ON a.ResourceID = b.ResourceID
WHERE (a.FileName = 'excel.exe' or a.FileName = 'msaccess.exe')
GROUP BY b.Netbios_Name0, b.User_Name0, a.FileName, a.FileVersion
ORDER BY b.Netbios_Name0


Note that you may get repeated machine names if the machine has got multiple updates installed for the MS office versions. 


No comments:

Post a Comment