SCCM Linked report MS Office Version with Service Pack installed machines

If you have referred to my previous report linked report http://eskonr.com/2012/05/sccm-linked-report-count-of-computers-assigned-to-specific-ad-site/ this would be easier for you to go.

You need to create 2 reports 1)Count of MS Office Versions with Service Pack 2) list the computers with MS Office and Service pack installed

Create new report from reports node for 2) list the computers with MS Office and Service pack installed using the below SQL query(quotes posted in this blog are converted to fancy,please replace them with correct ones in your report)  :

select sys.name0,sys.AD_Site_Name0,FCM.SiteCode,ARP.DisplayName0,ARP.Version0 from V_R_system sys
inner join v_Add_Remove_Programs ARP on arp.resourceID=sys.ResourceID
inner join v_FullCollectionMembership FCM on FCM.ResourceID=sys.ResourceID
and arp.DisplayName0=@Displayname and ARP.Version0 =@Version

group by sys.name0,sys.AD_Site_Name0,FCM.SiteCode,ARP.DisplayName0,ARP.Version0
order by sys.name0

Create prompt for Display name : Select DisplayName0 from v_Add_Remove_Programs

Prompt for Version : Select Version0 from v_Add_Remove_Programs

Now create another report 1)Count of MS Office Versions with Service Pack with below SQL query :

SELECT 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 '12.0.6612.1000' then 'SP3' when arp.version0 LIKE '14.0.6029.1000' then 'SP1'
  else ''
  end as 'Service Pack', arp.Version0, Count(DISTINCT v_r_system.resourceid) AS 'Count'
FROM v_ADD_REMOVE_PROGRAMS arp, v_r_system, V_RA_System_SMSInstalledSites ASSG
WHERE ARP.resourceid =  v_r_system.resourceid and v_r_system.resourceid = assg.resourceid 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 v_r_system.operating_system_name_and0 not like '%server%' and (InstallDate0 not like 'NULL')
group BY arp.DisplayName0,  arp.version0
ORDER  BY arp.DisplayName0,  arp.version0

Once done, right click on above report and go to properties of 1)Count of MS Office Versions with Service Pack ,click on link

in link type select link to another report under report select the 2nd report  2) list the computers with MS Office and Service pack installed

and click on the prompt (hand symbol) and select column 1 for Display name and Column 3 for Version as prompt shown like below.

You are done now ! 🙂

Full Details : http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/7baeb348-fb63-4115-8d76-2c884d18f708