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.
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:
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.
Finally you will see the following prompt. Click Cancel to stop the data refresh.
Configure the Data Connections
Now go to the Data ribbon, and click Connections.
You will see a number of data connections that will vary depending on the report.
Each 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.
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.
There’s some request which need to be change to reflect the database name. Like CH Settings ending with: “FROM CM_D1G.dbo.v_CH_Settings v_CH_Settings”
Thanks for pointing that out! I’ve cleaned up the SQL queries and uploaded a new report 🙂
Thanks so much. Only thing that seems to not work is the site information: =Table_Query_from_ConfgMgr11[SiteCode] does not seem play nice.
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.
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!
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),””)
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.
Nevermind, I found it. There was a hidden_data worksheet. Thanks!
👍
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’
Hi,
‘db_datareader’ should be the only permission required on the ConfigMgr database, which allows to run Select statements on any table or view. Here are some instructions to set that:
http://www.joellipman.com/articles/microsoft/sql-server/create-read-only-database-user-in-sql-server.html
Make sure there are no conflicting permissions set, like ‘db_denydatareader’
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
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.
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
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!
Hi Is there a way to add custom error codes to the ErroCodes Database? How would we find the Hexadecimal/signed/unsigned integers
Yes of course you can do that. You can find info here on translating error codes: https://smsagent.wordpress.com/2015/06/25/translating-error-codes-for-windows-and-configuration-manager/
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.
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?
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
Hi, drop me a message using the contact form on the About menu, and I’ll give you a custom version with the AD site added.
Hi Trevor Great toolset! but I get an error when trying to download your Configmgr Deployment Reporter this message comes from endpoint protection Trojan:Win32/Bitrep.A
Yeah can happen with some AV engines unfortunately. I think it’s due to the way PS code is wrapped in an exe. You would need to whitelist it.
Hi Trevor, awesome tool. I’m using this with Excel 2013 and SCCM CB1702 and it works great. Do you have anything built like this that separates servers from workstations?
Sorry, no!
I think you can modify each SQL query to filter on the OS version
What would we fill in for WSID in the data connection string? It looks required for ‘Site Info’ ‘Missing Client’ ‘Last reboot’ ‘Client Version’ ‘CH Settings’ The rest of the connections work.
That’s the name of the workstation you are connecting from.
ok
Hi Trevor, I want to add another Tab to Excel with another result from an SQL query on CM database. How do I create another connection like the one existing? I can’t duplicate one of the existing ones and modify it? If I do from scratch I need to create the tab, set the columns and then create a connection with SQL query but how I do the last part?