Getting started with the Power BI SCCM Template


This blog runs through the process of setting up Power BI and the SCCM template which will give you detailed information on your System Center Configuration Manager including client and server health, malware protection, software updates, and software inventory across your organisation.

First things first, to run the Power BI SCCM template you need to running PowerShell v5 on the device that you install it on.

Run a Get-Host on your device to see the PowerShell version installed.

2017-03-20 17_15_42-Administrator_ Windows PowerShell.jpg

If it’s not v5 then go to here and grab a copy and install.

Download the SCCM Template

Next head over to https://powerbi.microsoft.com/en-us/solution-templates/sccm/

Click ‘Install Now’ to start the installation of the Power BI SCCM template.

2017-03-20 16_41_30-Solution templates _ Microsoft Power BI ‎- Microsoft Edge.jpg

Scroll down and click ‘Sign In’

2017-03-20 16_41_44-Getting Started ‎- Microsoft Edge.jpg

Once you have signed in you will need to allow access to the Business Platform Solution Template. Click Accept to do this.

2017-03-20 16_42_00-Authorise Business Platform Solution Template ‎- Microsoft Edge.jpg

You’ll now be presented with a Download button.

2017-03-20 16_42_21-Getting Started ‎- Microsoft Edge.jpg

Install and configure the Microsoft-SCCMTemplate

Run the downloaded file ‘Microsoft-SCCMTemplate.exe’. Click Install.

2017-03-20 17_17_58-Microsoft-SCCMTemplate Setup.jpg

2017-03-20 17_18_05-Microsoft-SCCMTemplate Setup.jpg

Restart the device once setup is complete.

2017-03-20 17_24_03-Microsoft-SCCMTemplate Setup.jpg

Once the machine is back up and running you need to execute the ‘Microsoft-SCCMTemplate’ application.

2017-03-20 17_26_23-Greenshot.jpg

At the wizard click ‘Next’

2017-03-20 17_26_44-Solution Template Installer.jpg

Enter credentials that will be used to connect to the Configmgr DB and click ‘Validate’

2017-03-20 17_27_01-Solution Template Installer.jpg

Click ‘Next’ when validation is successful.

2017-03-20 17_27_43-Solution Template Installer.jpg

Now enter the name of the server hosting your site database and click ‘Validate’. Once validated successfully click Next.

2017-03-20 17_28_02-Solution Template Installer.jpg

Now we need to specify a server running SQL server to use as a target. I have installed a server called ConfigMgr2 one which SQL is installed. I have also created a database called PowerBI which I will use to host the data. Enter the server name, click Validate and the choose the correct DB from the drop down list. Click Next to continue.

2017-03-20 17_33_13-Solution Template Installer.jpg

Enter the relevant details for trigger time or compliancy and click Validate then click Next.

2017-03-20 17_33_42-Solution Template Installer.jpg

Click ‘Run’ to build the solution template.

2017-03-20 17_33_47-Solution Template Installer.jpg

This process can take a little while so grab a cuppa.

2017-03-20 17_35_48-Solution Template Installer.jpg

Until it has completed. Then click ‘Download Report’

2017-03-20 17_49_13-Solution Template Installer.jpg

Save the .pbix file.

2017-03-20 17_37_29-Save File.jpg

Install Power BI Desktop

Go to https://powerbi.microsoft.com/en-us/desktop/ and download the Power BI Desktop.

2017-03-20 23_48_23-Power BI Desktop _ Microsoft Power BI ‎- Microsoft Edge.jpg

Run the PBIDesktop_x64.msi on a desktop OS. Click Next.

2017-03-20 17_40_15-Microsoft Power BI Desktop (x64) Setup.jpg

Accept the licence agreement and click Next.

2017-03-20 17_40_20-Microsoft Power BI Desktop (x64) Setup.jpg

Enter a location to install and click Next.

2017-03-20 17_40_23-Microsoft Power BI Desktop (x64) Setup.jpg

Click Install and Finish to complete.

2017-03-20 17_40_26-Microsoft Power BI Desktop (x64) Setup.jpg

2017-03-20 17_40_34-Microsoft Power BI Desktop (x64) Setup.jpg

Configure Power BI Desktop to use the SCCM Template file

Launch the Power BI Desktop and open the Solution Template file saved earlier.

In the ribbon click the Edit Queries button and choose ‘Edit Queries’.

2017-03-20 22_48_44-Greenshot.jpg

Highlight one of the nodes in the left hand nav with a warning triangle, I have chosen ‘Site’, and click ‘Edit Credentials’.

2017-03-20 22_49_22-SolutionTemplate - Query Editor.jpg

I’m using the current credentials as these will have access to the PowerBi database. Click Connect.

2017-03-20 22_49_29-.jpg

Click OK

2017-03-20 22_49_37-.jpg

The warning triangles will disappear from the left hand hav as the query connections are made successfully.

2017-03-20 22_49_50-SolutionTemplate - Query Editor.jpg

Close the query editor and select the ‘Overview’ tab from the main screen. If the dashboard comes back as BLANK then hit refresh in the Ribbon bar.

2017-03-20 22_50_47-.jpg

Once refreshed the dashboard should populate with data with some pretty cool info straight out of the box.

2017-03-20 22_52_19-SolutionTemplate - Power BI Desktop.jpg

2017-03-21 00_17_34-SolutionTemplate - Power BI Desktop.jpg

 

 

 

38 comments

  1. All went good until summary and then gave error about “Scheduled task exited with code 267045 — Action Failed Microsoft-GetDataPullStatus — Error ID:(tvm5sgof01nstt9” and then once again clicking on RUN at Summary Page it started but its been 4 hours still saying “Getting your PowerBI File ready”

      1. I was getting Message as “All done you can download report” but was never getting option to save file. so I stopped the SCCM template tool and then restarted server. After that install PowerBi Desktop tool and import the template file automatically and recreated connect from datasources and dashboard all information that required.

  2. Hi,

    Richard from the Power BI solution template team here. If you’e seeing Action Failed Microsoft-GetDataPullStatus, can you please contact us at pbisolntemplates@microsoft.com? We’d like to help and diagnose what’s up to see if there is something we need to do.

    Cheers,
    richard

  3. Hi, just wanted to say this looks real nice.
    As a heads up you really should use $PSVersionTable and look at the PSVersion property instead of using Get-Host.
    Get-Host might not return the correct version if in a remoting session but $PSVersionTable will always return the correct PS version.

    //Tommy

  4. Very nice. Any tips on how to alter or add to datasource. I need to add domain, computermodel, machinetype etc.

  5. This does not seem to allow credentials / login across domains if you have the tool installed on a standard PC and the SQL server is on another domain – one that is accessible for remote access from the first.

  6. Thanks for the great article. It is very helpful. I use the template for weeks now and I have to state that the PowerBI database on SQL-Server holds old data. du you have any idea how to get the PowerBI-Database up-to-date?
    Thanks

    1. Markus, there should be a scheduled task on the server holding the PowerBI DB that runs once a day to copy the data over from the ConfigMgr DB. Take a look at that to see if it executing correctly.

      1. Is it the same box as the one you ran the SCCM Template on? Thinking about it that will be the one with the scheduled task. If one doesn’t exist then re-run the wizard and then check again.

      2. yes, it is. When I re-run the wizard, will my custom changes on the template be wiped-out?

  7. so i need a power BI PRO lic just to set this up for my self?? I have a SQL server for SCCM and only wanna see the stuff for me right now. Basically im asking if i can do this for Free just to test it out.

    1. You can for free. There was a limitation introduced around June. I believe you need to use PRO to share dashboards. But you could sign up for the free trial version of PRO to test this.

  8. Hi, Where do I find the “Power BI Solution Template – SCCM” task. I need to change the login credentials. I’ve looked on my PC as well as the server where the tables are stores but do not find it in the Task Scheduler application.

  9. I have a doubt about the step five, that talk about of a creation of a new database to host the data. My doubt is if this template change any structure in SCCM DB, and in this new database (PowerBI_DB), what this template use of disk space.

  10. I have everything working but I’m having a hard time figuring out how to show more information. Like I want to have see a report for Computer Name, Serial Number and OS. They already have Computer Name and OS but I can’t figure out how to populate Serial Number.

Leave a Reply to Markus Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s