SCCM Configmgr 2012 SSRS Report Count MS Office Versions

 

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.

31 Responses to "SCCM Configmgr 2012 SSRS Report Count MS Office Versions"

  1. 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
    1. 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
  2. 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
  3. 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
    1. 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
  4. 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
        1. Hi,
          Do you still see office 2016 doesnt have the hyperlink to see the list of machines ? it works for me.

          Regards,
          Eswar

          Reply
  5. 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
    1. 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
    1. Hi Simon,
      Haven't updated report for office 365 as i dont have any office 365 installed computers but will update it soon.
      Thanks
      Eswsr

      Reply
  6. 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
      1. Hi
        im edit data sources : location /ConfigMgr_MMK/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}

        Error
        An error has occurred during report processing. (RsProcessingAborted)
        Failed to execute the request for the data set "DataSet1". (RsErrorExecutingCommand)
        To obtain additional information about this error, navigate to the report server on the local server or enable remote errors

        Please help with the problem*(

        Reply

Leave a Reply to Kashan Nawaz Cancel reply