Close Menu
    Saturday, October 11

    SCCM Configmgr 2012 SSRS Report Count MS Office Versions

    Eswar KonetiBy 2 Mins Read CM2012 15,382 Views

     

    Recently , I worked on MS Office upgrade project to 2013 and before I take any action on this,I need to identify /get count of all MS Office Versions installed on the computers that are managed by Configuration manager 2012.

    There is no default Configmgr report that provide you this information ,so you must create custom SSRS Report .I started working on Custom SSRS Report to get count of office versions .During my search results ,found a blog post from TechNet that provide you the count of MS Office Versions only but not what specific edition in each version (standard,professional).

    In this this blog post ,I will show you how to get the summary report for MS Office Version ,also get what editions in each version with its count. So to get this,I need to create 2 reports 1) summary count 2) Sub section to display the editions for each version.

    So, If you run the SQL query/SSRS report from the TechNet blog ,you get results something like below :

     

    image

     

    From the above report,you see MS office 20xx installed on X many clients but it doesn’t tell you what editions/versions they are installed .To see that, need to create another report and link to the above summary report.

    The 2nd report 2) Sub section to display the editions for each version looks something like this if you click on the blue numbers.

    image

    These 2 reports are linked each other .By chance ,if you change the name of the file,you have to edit the link also in the SSRS Design else ,will fail to run the reports.

    Note: Server OS is excluded from this report. The count you see above is only for workstations with client=Yes

    You can edit these RDL Files to customize the SQL Query.

    Download the RDL Files available from TechNet Gallary.

    Upload the RDL files into same folder into your SSRS Reports ,change the Data Source ,run the reports.

    Share.

    Related Posts

    31 Comments

    1. Kashan Nawaz on

      Hi

      Thanks for this great thing. I want this type report also for windows, can you help me please?

      Regards
      Kashan Nawaz

      Reply
    2. Bart on

      Hi

      I have a problem with reports, they dont't work. In SQL logs and I have
      System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'System_DISC', database 'CM_P01', schema 'dbo'.

      In database SQL i have preveligies in Membership db_datareader and db_owner and the same in Owned Schemas

      You have a solution ? ps.I download new reports

      Reply
      • Eswar Koneti on

        Hi Bart,
        Looks you are missing the required permissions to read data from SQL DB. Please show this error your SQL database admin or SCCM admin who can help you to get the permissions.

        Regards,
        Eswar

        Reply
    3. Branden on

      Hello. If I leave the query to pull form add_remove_programs_data I loose most installations. If I change it to add_remove_programs_64_data I pull in more installations; however, the drill down doesn't work. Trying to report on Microsoft Office 365 ProPlus - en-us installations. Any ideas?

      Reply
    4. Branden on

      Hello. If I leave the query to pull form add_remove_programs_data I loose most installations. If I change it to add_remove_programs_64_data I pull in more installations; however, the drill down doesn't work. Trying to report on Microsoft Office 365 ProPlus - en-us installations. Any thoughts?

      Reply
      • Eswar Koneti on

        Hi Branden,
        At this moment ,i do not have clients that are installed with office 365 So getting report on office 365 might take time .

        Regards,
        Eswar

        Reply
    5. x10 on

      Hi Sir, i had download the latest version of these rdl files and upload into report server. Then changed data sources.

      Then run List Clients with specific MS office Version report, got an error message sait The 'edition' parameter is missing a value . And the rest of rdl worked well. Could you help to check? thans!

      Reply
    6. Sooraj Sreenivas on

      Hi Eswar

      I edited the query as below to include office 2016. but no luck. clients with office 2016 not listing. Any idea.

      SELECT
      COUNT (Total.Name0) AS 'Total Clients',
      COUNT (office2003.Name0) AS 'Office 2003',
      COUNT (Office2007.Name0) AS 'Office 2007',
      COUNT (Office2010.Name0) AS 'Office 2010',
      COUNT (Office2013.Name0) AS 'Office 2013',
      COUNT (Office2016.Name0) As 'Office 2016'
      FROM

      ------------------------------
      -----------------------------------------

      (
      SELECT DISTINCT sys.Name0 ,
      displayname00 AS OfficeEdition ,
      version00 AS OfficeVersion
      FROM
      system_disc sys LEFT JOIN
      add_remove_programs_data arp ON sys.itemkey=arp.machineid
      WHERE(displayname00 LIKE 'Microsoft Office 2016'
      OR displayname00 LIKE 'Microsoft Office Professional Plus 2016'
      OR displayname00 LIKE 'Microsoft Office Standard 2016'
      OR displayname00 LIKE 'Microsoft Office Professional 2016'
      OR displayname00 LIKE 'Microsoft Office Home and Student 2016'
      OR displayname00 LIKE 'Microsoft Office Home and Business 2016'
      OR displayname00 LIKE 'Microsoft Office Professional Plus 2016 (Beta)'
      OR displayname00 LIKE 'Microsoft Office Starter 2016 - English')
      AND sys.client0 = 1) AS office2016 ON Total.Name0 =office2016.Name0

      Reply
      • admin on

        Is it listed in add remove programs view? If not, you need to check on that part. If exists, you will have to play around the query. I do not have office 2016 Intsalled in my lab but I can give a try when get time.

        Regards,
        Eswar

        Reply
    9. agapetosman on

      Thanks very much for this. Will be very helpful though if how to do these steps can be highlighted to beginners 🙂 - "change the Data Source ,run the reports."

      Reply

    Leave a Reply

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