I wrote a different version to show me the latest version of office - in my enviroment we can have multple version of office installed.
Also remember that add/remove install date is broken in 7.1 and will reflect last patch date install instead of first installed date if you use the addremove install date
I also prefer to name the office version to what is corporate - even though I know that I have home version installed.
select
ac.Name,
convert(varchar, ac.[client date], 101) as 'ScanDate',
ac.name,ac.[OS Name] AS 'OS Name',
COALESCE(case when office1.[Display Version]='15' then 'Office 2013' ELSE NUll end,
case when office1.[Display Version] ='14' then 'Office 2010' ELSE NUll end,
case when office1.[Display Version] = '12' then 'Office 2007'ELSE NUll end,
case when office1.[Display Version] = '11' then 'Office 2003'ELSE NUll end,
case when office1.[Display Version] = '9.' then 'Office 2000'ELSE NUll end )
as 'Office Name' ,
office1.[Display Version] as 'Office Version',
convert(varchar,Officeuse.[Last Run],101) as 'Office Last Run',
Officeuse.usage,
officeuse.[Run Count],
CAST ((officeuse.[Total Run Time]/60) AS DECIMAL (10,2)) as 'Office Usage in minutes'
FROM dbo.Inv_AeX_AC_Identification ac
left JOIN vcomputer i ON i.Guid = ac._ResourceGuid
left join vAsset on vasset._ResourceGuid = ac._ResourceGuid
left join vComputer vc on vc.Guid =ac._ResourceGuid
left join ( select MAX (Left(prog.DisplayVersion,2))as 'Display Version', i.[guid]
FROM dbo.vComputer i INNER JOIN dbo.Inv_AddRemoveProgram prog
ON i.[Guid]= prog._ResourceGuid
where (
prog.DisplayName ='Microsoft Office Professional Plus 2007'
or prog.DisplayName ='Microsoft Office Professional Plus 2010'or prog.DisplayName ='Microsoft Office Professional Edition 2003'
or prog.DisplayName ='Microsoft Office 2010'or prog.DisplayName ='Microsoft Office Standard 2010'
or prog.DisplayName ='Microsoft Office 2000 SR-1 Professional'or prog.DisplayName ='2007 Microsoft Office system'
or prog.DisplayName ='Microsoft Office Enterprise 2007'or prog.DisplayName ='Microsoft Office Basic Edition 2003'
or prog.DisplayName ='Microsoft Office 2000 SR-1 Disc 2' or prog.DisplayName ='Microsoft Office Professional Plus 2013'
)group by i.guid ) office1
on i.Guid =office1.[guid]
left join ( SELECT DISTINCT vc.guid, vc.Name,adrp.InstallDate, adrp.DisplayName,vms.[Run Count],vms.[Total Run Time], vms.[Last Start] AS [Last Run],
CASE WHEN vms.[Run Count]>= 1 THEN 'Yes' ELSE 'No' END usage
FROM Vcomputer vc
LEFT JOIN Inv_AddRemoveProgram adrp ON vc.Guid = adrp._resourceguid
LEFT JOIN vAMMonthlySummary vms ON adrp._resourceguid = vms._resourceguid
AND vms.[Last Start] = (SELECT max(vms.[Last Start]) FROM vAMMonthlySummary vms WHERE vms._ResourceGuid = vc.Guid)
LEFT JOIN Inv_InstalledSoftware ins ON vms._ResourceGuid = ins._ResourceGuid
WHERE adrp.DisplayName in
( 'Microsoft Office Professional Plus 2007', 'Microsoft Office Professional Plus 2010','Microsoft Office Professional Edition 2003'
,'Microsoft Office 2010','Microsoft Office Standard 2010' ,'Microsoft Office 2000 SR-1 Professional','2007 Microsoft Office system'
,'Microsoft Office Enterprise 2007','Microsoft Office Basic Edition 2003' ,'Microsoft Office 2000 SR-1 Disc 2' ,
'Microsoft Office Professional Plus 2013' ) )
Officeuse on ac._ResourceGuid = officeuse.Guid
WHERE ac.[Client Date]>GETDATE ()-20
and ac.[OS Name] not like '%server%'
and vAsset.[Status] ='active'
order by ac.Name asc