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 =@Versiongroup 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
10 Comments
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.
check if you have replaced the quotes for 11.0.6361.0 ? double check the syntax errors once again.
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.
Please replace the quotes "" as they are fancy quotes converted by blog.
Hi, I just seen that occasionally this site displays a 403 server error. I thought that you would like to know. Thanks
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.
Can you help me to create SCCM 2012 report for MS office with service pack/version
http://be.enhansoft.com/post/2013/09/04/Help-Choose-Enhansofts-Next-Free-SQL-Server-Reporting-Services-(SSRS)-Report-for-the-Month-of-October.aspx
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
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.