thaxor

Working on my OpenAudit view.  Trying to get down to 1 report that I can use to prioritize software/hardware upgrade paths for all staff.

This test is just to pull out Office and Windows verision/keys. Not interested in extra stuff like Visio or Project right now since they’re special cases.

Had to do some hacking because even though only Outlook from SBS 2003 is installed on a few computers OpenAudit reads it as full Office 2003 messing up the reporting. To fix this on machines that had OEM Office 2000 installs that don’t register I added the search for ‘Microsoft Office Outlo’

SELECT s.net_user_name, s.system_name, m1.ms_keys_uuid,
if( left( m1.ms_keys_name, 22 ) = ‘Microsoft Office Outlo’, ‘n/a’, m1.ms_keys_name ) AS OfficeVersion,
if( left( m1.ms_keys_name, 22 ) = ‘Microsoft Office Outlo’, ‘n/a’, m1.ms_keys_cd_key ) AS OfficeKey,
m2.ms_keys_name as WindowsVersion, m2.ms_keys_cd_key as WindowsKey
FROM `system` AS s
INNER JOIN `ms_keys` AS m1 ON s.system_uuid = m1.ms_keys_uuid
LEFT JOIN `ms_keys` AS m2 ON m1.ms_keys_uuid = m2.ms_keys_uuid
WHERE left( m2.ms_keys_key_type, 6 ) = ‘window’
AND (left( m1.ms_keys_name, 22 ) = ‘Microsoft Office Profe’
OR left( m1.ms_keys_name, 22 ) = ‘Microsoft Office Small’
OR left( m1.ms_keys_name, 22 ) = ‘Microsoft Office Outlo’)
GROUP BY m1.ms_keys_uuid