Connect ConfigMgr to Power BI

I sometimes hear device management professionals saying something like, “I haven’t got around to using PowerShell yet” or “I haven’t had time to look into InTune and Azure yet”. Most people in the industry will agree that this is the future of device management, but getting started with a new technology takes some effort getting into the jargon and learning the syntax, the possibilities and limitations. You have to crack the nut, so to speak, make your own little “hello world” demo before you can embrace a new technology. That’s how it is for me at least.

In this post, I will describe my “hello world” experience with Power BI in a ConfigMgr context. I have been working with SQL Server Reporting Services for some time now, writing SQL queries, designing reports with meaningful graphs and tables. Clearly, Power BI is the modern way of reporting on your ConfigMgr data. But Power BI can seem a bit daunting at first glance and the name itself covers several different concepts which I think are best described here:

https://docs.microsoft.com/en-us/power-bi/fundamentals/power-bi-overview

This article will not describe how to setup Power BI Report Server which requires the Power BI Premium license.

https://docs.microsoft.com/en-us/mem/configmgr/core/servers/manage/powerbi-report-server

For my demo, I had three goals:

• Create a nice visual report of some ConfigMgr data, in this case – content downloaded from ConfigMgr distribution points and other sources by clients.
• Publish the report to the Power BI service so that it can be easily accessed and even shared.
• Automate the data refresh, so that the report is always up to date.

So here goes…I hope this will inspire other device management pros to try out this impressive reporting and insight tool.

Pre-requisites

• An Azure AD tenant
• A Microsoft 365 (for business) account in above tenant
• An on-premises AD and ConfigMgr infrastructure
• A 64-bit version of Windows 8 or a 64-bit version of Windows Server 2012 R2 (minimum)
• .NET Framework 4.7.2 (minimum for Gateway release September 2019 and later)
• Local administrator permissions on the machine where you will be installing the gateway.
• Read permissions on ConfigMgr SQL server database

Download

Download the standard “On-premises data gateway” from
the Official Microsoft Download Center:

https://www.microsoft.com/en-us/download/confirmation.aspx?id=53127

“The on-premises data gateway acts as a bridge. It provides quick and secure data transfer between on-premises data, which is data that isn’t in the cloud, and several Microsoft cloud services. These services include Power BI.“

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem

Also download Power BI Desktop (64-bit):

https://www.microsoft.com/en-us/download/details.aspx?id=58494

Power BI Desktop will be used to create and design reports and then publish them to the Power BI service.

Install an On-premises data gateway

I chose to install the gateway on a separate Management Point server instead of the Primary Site Server (which also had the database role). You could install it on the PSS or even on a separate server (not a domain controller) or workstation in your domain (for better performance opening and editing reports in Power BI Desktop).

Double-click the installer and click Run. Verify that the minimum requirements are fulfilled, accept the terms and then click Install to proceed.

You may have to click Yes in several UAC prompts. After the installation completes, you have to sign in with a Microsoft 365 account in your tenant.

Then select Register a new gateway and click Next.

Give your gateway a name and type in a recovery password. Then click Configure.

If successful, you should end up seeing the On-premises data gateway GUI, which you can just close. Obvio, the gateway should be online and ready as shown below…otherwise you’ll have to descend to diagnostics 😉

Now you can go ahead and sign in to office.com in a browser of your choice and open the Power BI app. You must at least have the free Power BI license in order to use this app. Sharing of Power BI reports requires the pro license at least (read more here: https://powerbi.microsoft.com/en-us/pricing/ ).

If you go to Settings and click Manage Gateways…

…you should now be able to see that your new gateway is Online.

Create a Power BI report

Back on the machine where you installed the On-premises gateway or alternatively a work station which can reach your ConfigMgr SQL server – install and start Power BI Desktop. Then sign in with your Microsoft 365 account. In your new empty report, click SQL Server in the Data section of the Home ribbon.

Type the FQDN of your ConfigMgr database server and the ConfigMgr database name. If you don’t use FQDN, refreshing the data in the report will fail (explained later). I recommend selecting Import. This will ensure that your report is available even if the ConfigMgr server is temporarily unreachable. Later, I will show how to setup a refresh schedule to keep the report up to date.

Select the account which will be used to access the SQL server database – Read permissions as a minimum. If you want to use a separate account you can type the credentials here and click Connect.

Now you should see a long list of tables and views in your ConfigMgr SQL database. You can narrow down the list by typing whatever you’re looking for, e.g. “clientdownload” as shown below. If you select a tabel or view, you can see a preview of the data in the right-hand side.

For the record, Microsoft only supports querying “views” and “stored procedures”, not the data tables directly.

“When you create an SQL statement for a custom report, don’t directly reference SQL Server tables. Always reference supported reporting SQL Server views from the site database. These views have names that start with v_[…] You can also reference public stored procedures from the site database. These stored procedures have names that start with sp_“.
https://docs.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/create-custom-reports-using-sql-server-views

Select the v_ClientDownloadHistoryDP_BG view and click Load in order to import the data into a Power BI dataset embedded in the report.

If you prefer to work with SQL queries you can click Transform data in the ribbon to open the Power Query Editor. Then you can create a new source and select SQL Server.

In the SQL Server database window, expand the advanced options, insert your SQL query and click OK.

A preview of the imported dataset will be shown. Click OK to proceed.

If you’re happy with the imported dataset, go ahead and click Close & Apply.

So now the dataset is ready to be visualized in a report like the one shown below, which tries to answer questions like “how much content was downloaded to ConfigMgr clients in the last few months?”, “which content is being downloaded the most?”, “where are clients downloading the content from?” and so on. By the way, I have done almost nothing to format the axes, scale or legends etc. It is done automatically by Power BI.

The really cool thing about Power BI is that you can click your data to tease out more information than would be possible from a SSRS report. In the screen shot below, I have clicked the client computer (clientID) which has downloaded content the most times. As you can see, the report immediately tells us which content was download, when it was downloaded and the type and name of the content.

I won’t go into detail about how to create a Power BI report since there are many guides about this already available. If you don’t feel like building your own report from scratch (which you should!) you could just grab one of several community ConfigMgr dash boards e.g.

https://github.com/Microsoft/BusinessPlatformApps/tree/dev/Source/Apps/Microsoft/Released/Microsoft-SCCMTemplate

https://gallery.technet.microsoft.com/Power-BI-SCCM-Dashboard-d1b7e688

Publishing your report

This is quite simple. Save your report and then click Publish in the Home ribbon.

Select the workspace created earlier when installing the on-premises data gateway and click Select.

If successful, you should see something like this.

That’s it! Now you can view the report in office.com.

Connecting the report to the source

To keep the report up to date, you have to connect the embedded dataset to a data source, in this case the on-prem ConfigMgr SQL server database. This connection is possible because we installed an on-premises data gateway earlier. Return to the Power BI app in office.com and open Settings.

Select the Datasets tab, select the dataset with the same name as your report and then expand Gateway connection. Now expand the gateway (under Actions) and click Add to gateway to create new data source for the dataset.

In the resulting form, give the data source a name and select Windows as authentication method. Then type the credentials of the account which will access the SQL database (Read permission) and click Add. Be sure to use domain\username syntax or the refresh will fail.

Expand the Gateway connection properties again for the dataset, verify that the gateway connection is running (green). Then click the Maps to: drop down menu and select the new data source.

Don’t worry about the warning about credentials being invalid – just click Apply and it goes away. In order to test the connection, click My workspace in the left hand menu, locate the new dataset and click the Refresh now button.

After a few seconds depending on the complexity and size of the dataset, you should be able to see a new timestamp for the last dataset refresh. If you open Settings for the dataset and click Refresh history, you can verify that the on demand refresh was completed.

When viewing the report e.g. by clicking on My workspace, you can also see when the data was last refreshed.

Setting up a refresh schedule

To automate refreshes of the report data, return once more to the dataset settings page and expand Scheduled refresh. Now you can set up whatever schedule you like and even get email notifications if the refresh should fail. You can configure up to 8 scheduled refreshes per day with the Power BI Free or Pro license.

As shown earlier, you can check the refresh history to monitor the status of the refresh schedule.

Sharing a Power BI Report

Locate the report in My workspace and click the Share button.

Now this is where you’ll need a Power BI Pro license, but luckily you get a 60 day trial, so go ahead and share the report with someone in your organization (who also needs a Pro license to view the report).

One more cool thing. Now that your report is available on office.com and is automatically refreshed, you can monitor the status of your ConfigMgr infrastructure anytime from anywhere and even on a tablet or a phone by using a browser or available apps.

It’s even possible to create mobile views and dashboards which focus on selected visuals from your report. Awesome!

Outro

Well, this article became a lot longer that anticipated! I hope you enjoyed reading along. Let me know if you have any comments or questions.