SQL Query for MS Office with Hostname details

SCCM Report for all versions of MS Office with Service pack and Hostname details

SELECT  dbo.v_R_System.Name0,

dbo.v_R_System.user_name0,

dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],

arp.DisplayName0,

CASE WHEN arp.version0 LIKE '11.0.6361.0' THEN 'SP1'

     WHEN arp.version0 LIKE '11.0.7969.0' THEN 'SP2'

     WHEN arp.version0 LIKE '11.0.8173.0' THEN 'SP3'

     WHEN arp.version0 LIKE '12.0.6215.1000' THEN 'SP1'

     WHEN arp.version0 LIKE '12.0.6425.1000' THEN 'SP2'

     WHEN arp.version0 LIKE '14.0.6029.1000' THEN 'SP1'

ELSE 'NULL' END as 'Service Pack', arp.Version0

FROM dbo.v_Add_Remove_Programs AS arp

INNER JOIN dbo.v_R_System ON arp.ResourceID = dbo.v_R_System.ResourceID

INNER JOIN dbo.v_RA_System_SMSInstalledSites AS ASSG ON dbo.v_R_System.ResourceID = ASSG.ResourceID

INNER JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID

  --JOIN v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID

WHERE 
--v_FullCollectionMembership.CollectionID = ‘@CollectionID’ AND

(arp.DisplayName0 LIKE '%Microsoft Office%edition%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Standard 2007%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Enterprise 2007%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Professional%2007%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Standard 2010%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Enterprise 2010%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Professional%2010%' OR

 arp.DisplayName0 LIKE 'Microsoft Office 2000%' OR

 arp.DisplayName0 LIKE 'Microsoft Office XP%') AND

(arp.DisplayName0 NOT LIKE '%update%') AND

(arp.DisplayName0 NOT LIKE '%Microsoft Office XP Web Components') AND

(dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%server%') AND

  (arp.InstallDate0 NOT LIKE 'NULL')

ORDER BY arp.Version0, dbo.v_R_System.Name0 

Please note that the above query excludes Windows Server operating system records. You may comment Line-30 to include server OS records on the report.

If you wish to run the query to prompt for a collection, you can remove the comment on Line-16 and Line-18 to achieve it.