Creating Dynamic Reports for Configuration Manager with Microsoft Excel

Today I reached 10,000 hits on my blog, very cool, and what better way to celebrate than with a new post 🙂  Thanks to everyone who has stopped by, I hope that you have found something helpful and continue to do so!

In today’s post, I want to look at how to create dynamic reports for ConfigMgr using Microsoft Excel.  Excel has the ability to create and store a SQL server connection, so we can use it to talk directly with the ConfigMgr database and pull data into our worksheets, where we can summarize, combine, perform functions, display charts etc.  In addition, the SQL connection can be refreshed meaning that the report can always have the most recent data from the database. Excel is a very handy tool to use to create reports from the simple to the complex, and is a great option for use with Configuration Manager.

What about SSRS?

The built-in reporting capability of Configuration Manager is of course very good.  The SQL Server Reporting Services reports are all predefined for you and allow you to get some very useful information.  You can also create custom reports, subscriptions, export data and share reports with interested parties.  But I find there are still some limitations that can make Excel an attractive alternative when creating custom reports.  For one thing, finding the data you want can sometimes mean drilling through more than one report and selecting various options.  And then I often find that the data I want is not readily available in a single report and I have to create a custom report to get it.  But creating custom reports with SSRS is a bit of a slow and cumbersome process.

Excel has some advantages here worth considering:

• You can create multiple worksheets and use multiple SQL queries to return your results into a single workbook
• Creating charts to visually display the data is quick and easy
• Data can be persisted to create a ‘snapshot’ report, or dynamically refreshed to keep the report always-up-to-date
• You can leverage the power of both SQL language and Excel formulas to manipulate your data

I have used Excel for reporting to great effect, and if time permits I will share some of the reports I’ve created in future posts, including a spreadsheet to dynamically monitor SCCM OS Deployments and a single report that will get the current deployment status of any ConfigMgr Application.

In this post, I will focus on the fundamentals and create a simple report that will give me a dynamic inventory of my servers with various data such as make, model, OS version, IP address, MAC address, RAM, disk space, serial tag etc

Security

The first thing to do is to decide which account will be used to get access to the ConfigMgr database.  If you are creating a report that is for your own use only, then there is no need to use specific account credentials, you can use the built-in Windows authentication option (assuming you have access to the database with your account).  If your report will be made available to other users, then those users will need at least read access (db_datareader) to the SQL database so the SQL query that runs in the background has permission to do so in their user context.  There is, however, a viable alternative if you don’t want to assign SQL permissions to all the users who need to view the report – you can create a local SQL account that has db_datareader access to the database, and embed these credentials in the report.  There is a security consideration here, as the local SQL credentials will be stored in clear text within the workbook, but since the permission level is read-only that should not be a major concern, and this is the method I will use in this post.

Create a Data Connection

Open Microsoft Excel (I’m using 2013, but it’s almost the same in 2010) and go to the Data ribbon. In the Get External Data section, click From Other Sources, then From SQL Server.
In the Data Connection Wizard window, enter the name of your SQL server and instance that hosts the ConfigMgr database, and enter the SQL credentials to connect to it.
Select the ConfigMgr database in the drop-down list.  At this point we cannot enter a specific SQL query, so we have to pull in any random table for now, then we’ll update the connection later.

Click Next.  You can change the File Name and Friendly Name of the data connection to make it more obvious as to what data it is getting, and to distinguish it from other data connections you may create in the workbook.

Click Finish.

Edit the Connection Properties

At the Import Data window, click on Properties.

In the Connection Properties window, on the Usage tab, make sure that Enable background refresh is selected, and also Refresh data when opening the file.

This allows us to run the SQL queries in the data connections automatically every time we open the Workbook, and also perform a manual refresh when desired, from the Data ribbon. Optionally, you can also refresh the data regularly using the Refresh every .. minutes option.

On the Definition tab, check the option Save password.  You will be prompted to allow the saving of the password unencrypted in the Excel file.  Click Yes.

In the Command Type field, change to SQL, then enter your SQL query in the Command text field.  In this example, I’m using the following query which gives me an inventory of all my servers (change the sys.Name0 like ‘%srv%’ statement in the where clause to suit your environment):

select sys.Name0 as 'Server Name',
 case when comp.DomainRole0 = 3 then 'Server'
 when comp.DomainRole0 = 4 then 'Domain Controller'
 when comp.DomainRole0 = 5 then 'Primary Domain Controller'
 End as 'Server Type',
comp.Manufacturer0 as 'Manufacturer', comp.Model0 as 'Model', comp.Roles0 as Roles, os.Caption0 as 'OS', os.CSDVersion0 as 'SP Level',
comp.SystemType0 as 'Architecture',
os.Description0 as Description, nic.IPAddress0 as 'IP Address', nic.MACAddress0 as 'MAC Address', cpu.Name0 as 'CPU', comp.NumberOfProcessors0 as 'No. of Processors',
CAST(CAST(comp.TotalPhysicalMemory0 AS NUMERIC) / 1024 / 1024 as DECIMAL(10,2)) as 'Total Physical Memory (GB)',
disk.Partitions0 as 'No. of Disk Partitions (Primary Disk)', CAST(CAST(disk.Size0 as NUMERIC) / 1024 AS DECIMAL(10, 2)) as 'Total Disk Capacity (GB) (Primary Disk)',
bios.SerialNumber0 as 'Serial Tag', os.InstallDate0 as 'OS Install Date', os.LastBootupTime0 as 'Last Bootup Time',
sys.Last_Logon_Timestamp0 as 'Last Logon Timestamp', sys.User_Name0 as 'Last Logged On User'
from v_R_System sys
inner join v_GS_Operating_System os on sys.ResourceID = os.ResourceID
inner join v_GS_Computer_System comp on sys.ResourceID = comp.ResourceID
inner join v_GS_PC_BIOS bios on sys.ResourceID = bios.ResourceID
inner join v_GS_Network_Adapter_Configuration nic on sys.ResourceID = nic.ResourceID
inner join v_GS_Processor cpu on sys.ResourceID = cpu.ResourceID
inner join v_GS_Disk disk on sys.ResourceID = disk.ResourceID
where os.ProductType0 in ('2','3') and nic.IPAddress0 is not null and cpu.DeviceID0 = 'CPU0' and sys.Name0 like '%srv%' and disk.MediaType0 = 'Fixed hard disk media' and disk.DeviceID0 = '\\.\PHYSICALDRIVE0'
order by 'Server Name'

You can expand the window if you have a large query and need to see more of it.

Click OK, and you will be prompted that the connection has now changed in the Workbook, click Yes to accept.

Back in the Import Data window, make sure that Table is selected and the cell range =$A$1 in the Existing worksheet, then click OK.

The query will  run and pull the data into the worksheet.  Now I have a nice inventory of my servers:

You can see that it places a filter on all the column headers, making it easy to filter your data.

Edit the SQL Query

If you need to edit your SQL query, you can do so from the Data ribbon.  Click Connections, select the data connection and click Properties.

You can now go to the Definition tab and edit your SQL query as required.

Microsoft Query

An alternative way to get data from ConfigMgr into Excel is to use Microsoft Query.  This is a kind of ‘gui’ way to construct a query and may be useful if you are not familiar with creating SQL queries directly, although I find it easier to create the queries myself!

We won’t cover Microsoft Query in this post, but to get started with it, simply click on the Data ribbon in Excel, and choose Get External Data > From Other Sources > Microsoft Query.

Querying the SQL Database

Your report will only be as good as your SQL querying skills, your Excel skills and your knowledge of the ConfigMgr database of course, but on the latter Microsoft has provided some nice documentation (here) to help you find your way around the available SQL views.  In most cases you will query the views not the tables as they are designed for that, so take a look through the Microsoft documentation to help you identify where to find the data you need.  If you are familiar with querying WMI for ConfigMgr, then you should already find the database friendly as many of the views have similar names to the classes used in WMI.

Complex Reports

This was a simple example of a report that is not much different than running a WQL query in ConfigMgr directly.  However, you can begin to understand the potential here if you create additional worksheets with their own data connections, and you have multiple SQL queries pulling data into one report.  You can then create a summary sheet, for example, that summarises data from each of the worksheets, and populates graphs for a visually pleasing display.  You can store this report in any network share and control access by NTFS permissions, or make it available in a document repository such as Sharepoint.  The next time someone asks you for some data from ConfigMgr, you can simply send them a link.

Happy reporting!