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

image

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.

image

You are done now ! 🙂

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

10 Responses to "SCCM Linked report MS Office Version with Service Pack installed machines"

  1. JosĂ© A. Muñoz Jr. · Edit

    Thank you! That worked! I removed all fancy quote ("'') and the error went away. But now I'm getting a whole new one. I'm getting an error message that says that there is another syntax error near 6361. I get that error with or without commas in the code.

    Reply
  2. JosĂ© A. Muñoz Jr. · Edit

    Hi Eswar, I copied the SQL (from Step 1: Count of MS Office Versions with Service Pack) but I am getting an error message. I'm very new to SCCM (and am not terribly familiar with SQL) and so I might be completely missing something, but I copied the exact same SQL text you have and received this message:

    [42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near "".

    Any idea what I could be doing wrong? After I created the first report (report 2), I got a must declare scalar variable "@Displayname" error, but that went away after I deleted the @ symbols.

    Any help would be greatly appreciated. Thank you.

    Reply
    1. thanks for letting me know.How frequent does it occur ? i haven't noticed any such .just to make sure if everything is on track.

      Reply
  3. Hi Eswar, will this be possible to tel step by step because things are shuffled and i m getting error on both SCCM Report and SQL query.

    Tel me which steps to be followed 1st, 2nd and 3rd then it will useful for us

    Thanks
    Srini

    Reply
    1. it is clearly given in blog saying create report 2) first and then go with 1 and then link these report.
      What are you finding difficult ? if you get any errors with syntax,try replacing the quotes (',"") again since blog converts them to fancy quotes.

      Reply

Leave a Reply to Eswar Koneti Cancel reply