SCCM Power BI Solution Template preview

  • Article

Introduction

A few days ago Microsoft released a public preview of the System Center Configuration Manager Power BI solution template

“Stand up a scalable and extensible System Center Configuration Manager dashboard in a few hours. Information is collected daily so you can see not only how your organization’s computer health looks like today, you can also see how those key metrics change over time. Quickly identify machines not up-to-date with software updates, successful and failed mitigations to malware infections to be able to act quickly.”

More information on this template can be found here and you can download the template from here 

Requirements

System Center 2012 Configuration Manager R2 SP1 or later. Read access to System Center Configuration Manager database is required.

  • Destination database: Azure SQL database or SQL Server database (SQL Server 2008 R2 SP3 or later).
  • For the machine where the installation is run, Microsoft .NET Framework 4.5 or later & PowerShell version 3.0 or later.
  • Power BI Desktop (latest version)
  • Power BI Pro (to share the template with your organization)

Lab Setup

In this Wiki post, I am going to test installing the public preview of the SCCM Power BI Solution template in my lab which has:

  • ConfigMgr Current Branch 1606 & SQL server 2012 SP3
  • Windows Server 2012 R2  with Microsoft .Net Framework 4.5 installed
  • Azure SQL Database as my target database.

Setting up the Azure SQL Database as the target database

The SCCM Power BI solution template collects data from your SCCM installation and stores it in a target database. For this, I have chosen to create my target database as an Azure SQL database with my MSDN subscription in Azure.

Login to the Azure portal https://portal.azure.com

Below, I have clicked on Add, then given my database a name, created a new resource group, chosen a blank database, created a new SQL server, and used “S0 Standard” pricing tier as my ConfigMgr site is a very small lab.

When you create the new SQL Server, take a note of the login and password as you will need this later for the ConfigMgr Power BI Solution template setup.

https://brotechcm2012.files.wordpress.com/2016/09/powerbi2.jpg?w=640

Once my SQL database deployment has finished, I have gone into the SQL database overview and copied down my Server Name as it is required for later.

https://brotechcm2012.files.wordpress.com/2016/09/powerbi6.jpg?w=640

Installing the Power BI solution template for SCCM 

Next up in my lab I have installed Microsoft-SCCMTemplate.exe which I downloaded earlier from here  . Once finished installing, you can configure the solution template. to point it to the SCCM site server and database, and the Azure SQL target database. Click Next.

https://brotechcm2012.files.wordpress.com/2016/09/powerbi41.jpg?w=640

Enter in your source ConfigMgr database server details and select your ConfigMgr database, then validate and click Next:

https://brotechcm2012.files.wordpress.com/2016/09/powerbi51.jpg?w=640

Next, I will enter my target database which is my Azure SQL database name. I have selected “Using Azure SQL” . Make sure in the Azure portal you enter in your public IP in the SQL Server firewall in your SQL Server settings in https://portal.azure.com otherwise you will get the error below as it cannot connect. Steps to add your IP to the firewall are here.

https://brotechcm2012.files.wordpress.com/2016/09/powerbi7.jpg?w=640

This is how it should look:

https://brotechcm2012.files.wordpress.com/2016/09/powerbi8.jpg?w=640

On the Customize page, I have left settings as default and clicked Next.

On the Progress page, you can download your PBIX file and open it up with Power BI Desktop. You can download PowerBI Desktop from here if you do not have it installed.

https://brotechcm2012.files.wordpress.com/2016/09/powerbi111.jpg?w=640

Viewing the dashboard in Power BI Desktop

Once I have opened my downloaded PBIX file and opened it up in Power BI Desktop, I clicked on Refresh so it can get the latest data. It popped up for me to enter credentials to my Azure SQL database. Make sure you click on Database instead of Windows to enter your credentials, otherwise you will not have permission. 

https://brotechcm2012.files.wordpress.com/2016/09/powerbi12.jpg?w=640

Once it has pulled the latest data, you can view the Overview as shown in the screenshot below, or you can view the other tabs Protection, Malware, Updates Compliance and Software.

https://brotechcm2012.files.wordpress.com/2016/09/powerbi14.jpg?w=640

Here is an example showing Update Compliance

https://brotechcm2012.files.wordpress.com/2016/09/powerbi15.jpg?w=640