Instructions

Before you can use the reports, you will need to configure the SQL data connections to use your own ConfigMgr database.

Security

You will need to have at least db_datareader access to the ConfigMgr database with your logged-on account.

When you first open a report, you may get prompted with a security warning (depending on how your security settings are configured) such as the ‘Data connections have been disabled‘ warning below.  If you do get this prompt, you can go straight to Configure the Data Connections section below, before clicking Enable Content.  If you clicked Enable Content first, proceed to Cancel the Data Connection Prompts.

Capture

 

Cancel the Data Connection Prompts

After you have enabled the data connections, they will attempt to connect to the SQL server in the current connection string, and after a few seconds will fail with an error like this:

Capture1

The data connections are set to refresh upon opening, so that the report updates itself with the current data whenever it is opened.

Click OK.  Then you will see the SQL Server Login window.  Simply click Cancel to close it.

Capture2Finally you will see the following prompt.  Click Cancel to stop the data refresh.

Capture3

Configure the Data Connections

Now go to the Data ribbon, and click Connections.

Capture4

You will see a number of data connections that will vary depending on the report.

Capture5Each of these data connections contains a connection string that needs to be updated with your own ConfigMgr database details.

Select one of the connections, and click Properties.  On the Definition tab, you will see the Connection string field.

capture2

 

Simply remove the existing connection string and paste in the connection string to your own database.  All you need in the string is the driver name (should be “SQL Server”), SQL server name (and instance) if applicable, and the database name, like this:

DRIVER=SQL Server;SERVER=MYSRVLAB-SQL01\SCCM2012;DATABASE=CM_ABC

Excel will complete the rest of the string for you after you save the connection.  Once you have updated all the data connections in the workbook, the report will display the data for your environment.

 

 

 

Advertisements

21 thoughts on “Instructions

  1. Thanks so much. Only thing that seems to not work is the site information: =Table_Query_from_ConfgMgr11[SiteCode] does not seem play nice.

    1. Hi Andrew, is it that no data is retrieved in the Site Info section, or does it give some error? The SQL query that populates those cells is simply:

      SELECT v_Site.SiteCode, v_Site.SiteName, v_Site.Version, v_Site.ServerName
      FROM v_Site v_Site

      It assumes that only one site is being returned.

  2. yeah, that SQL Query works fine, however; since it returns all of our sites it seems to fail populating the data to the cell. We have 1 primary site server and 8 secondary site servers. I tried using top(1), however; in this case that did not work from excel. This is awesome by the way. Thanks so much for sharing your hard work!

    1. You’re welcome, glad it helps! I only have one primary site, so I didn’t really design it for multiple sites. But you could try this formula instead, which will at least get the first site in the list:

      =IFERROR(INDEX(Table_Query_from_ConfgMgr11[SiteCode],1,0),””)

  3. That worked great. Do you know how I can modify the query in the tables in Excel 2013? I see them under “existing connections” under the tables tab, however; I wanted to modify the site table with a Where clause to pull just the primary site server.

  4. Hi Trevor, i love this report, it works great on my home lab with the default instructions.

    On my production server, it didnt connect because the instance was named differently, managed to sort that, and it also seems my standard, non-admin user doesnt have sufficient permissions to the DB, but i am not sufficient SQL skilled to know how to rectify so i can forward this report to other staff that might find it useful.

    Can you interpret this error better than i can?

    The SELECT permissions was denied on the object ‘v_R_System’, database ‘CM_secret’, schema ‘dbo’

  5. Hi

    Is there a way to setup the ConfigMgr Reporter on a remote computer other than the SCCM server? I’d like to be able to connect and use the reporter from a Workstation other than the primary server where the database is stored.

    What are the requirements for this to work, so far I can only get it to work on the Primary SCCM server.

    Thanks.

    Great tool btw

    1. Hi, the tool uses your ConfigMgr database and has no other dependency on ConfigMgr, so it can be run on any workstation. That’s partly why I created the tool, so I can run it on my workstation independently of the SCCM server. The code should run on PowerShell 2 although .Net 4.5 is probably required. To be honest I have only tested it on up-to-date systems so not sure of the exact requirements.

  6. Hi can you advise the No client installed what is that number made up of and where can I obtain the machine names which have no client installed?
    Also regarding the ConfigMgr Reporter the Per Device Tab is it possible to change that to a text box instead of a drop down, reason when you have hundreds of machines to search the scroll function works really erratically and slowly.
    Thanks

    1. Hi, the “no client installed” is calculated from the following SQL query and returns the number of systems where no client version is recorded in the database.

      select Count(sys.Name0)
      from v_R_System sys
      left join v_ActiveClients act on sys.ResourceID = act.MachineResourceID
      where Client_Version is NULL
      Group By act.Client_Version

      The list of systems with no client installed you can find from the “ClientVer” tab, filtering for blanks on the “Client Version” column.

      I will have a look at the Per Device Tab drop-down when I have time!

  7. Hi Is there a way to add custom error codes to the ErroCodes Database? How would we find the Hexadecimal/signed/unsigned integers

  8. Could we add additional fields to report on in the reports or is it possible to see the source to make modifications? I’d like to add AD site Name to be reported in the Deployment reporter.

    1. In the Client Health Excel report, yes you can as the SQL queries are contained within the data connections. In the Deployment Reporter, no as the code is packaged as a EXE file. Where in the app do you want to add the AD site?

  9. Thanks Im looking for the AD Site to be added for the Deployment Reporter, mainly looking to have the AD site appear along side the devicename when reviewing the deployment status for compliant/error/ etc.So when exporting to CSV we can do additional filtering on Site Status, would be really handy.

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s