Close Menu
    Facebook X (Twitter) Instagram
    Sunday, July 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»SCCM Reports»SCCM Linked report MS Office Version with Service Pack installed machines

    SCCM Linked report MS Office Version with Service Pack installed machines

    Eswar KonetiBy Eswar KonetiMay 30, 9:34 pm2 Mins Read SCCM Reports 6,619 Views
    Share
    Facebook Twitter LinkedIn Reddit

    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

    CM07 configuration Manager Configuration Manager report Count of MS office with Service Pack Linked report for MS office version Service Pack SCCM report for MS office with Service Packs installed SCCM report MS office version
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    SCCM SQL Report – Compare Installed Apps on TWO Different Computers

    July 13, 10:35 am

    Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

    October 11, 8:48 pm

    SCCM report list collections with no deployments

    December 05, 12:04 pm

    10 Comments

    1. José A. Muñoz Jr. on April 25, 2014 8:56 PM

      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
      • Eswar Koneti on April 29, 2014 8:39 AM

        check if you have replaced the quotes for 11.0.6361.0 ? double check the syntax errors once again.

        Reply
    2. José A. Muñoz Jr. on April 24, 2014 11:35 PM

      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
      • Eswar Koneti on April 25, 2014 6:13 AM

        Please replace the quotes "" as they are fancy quotes converted by blog.

        Reply
    3. Mark Jums on March 6, 2014 1:06 PM

      Hi, I just seen that occasionally this site displays a 403 server error. I thought that you would like to know. Thanks

      Reply
      • Eswar Koneti on March 7, 2014 11:57 AM

        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
    4. Jijo Joseph on September 2, 2013 5:31 PM

      Can you help me to create SCCM 2012 report for MS office with service pack/version

      Reply
      • Eswar Koneti on September 16, 2013 9:20 AM

        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

        Reply
    5. srini on May 23, 2013 12:14 AM

      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
      • Eswar Koneti on May 23, 2013 11:04 AM

        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 ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.