“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 can be found here and you can download the template from here
- 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)
In this post, I am going to test installing the public preview of 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.
First thing I am going to do is create my target SQL server which will be an Azure SQL database.
Login to 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.
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.
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. Again the requirements are listed. Click Next.
Enter in your source ConfigMgr database server details and select your ConfigMgr database, then validate and click Next:
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 for 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.
This is how it should look:
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.
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.
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.
Here is an example showing Update Compliance