In my previous post we saw the steps to package and deploy Power BI Desktop using SCCM. In this post we will focus on SCCM Power BI dashboard installation and configuration. You can also call this as integrating Power BI with SCCM. Microsoft provides you Power BI solution template for System Center Configuration Manager.
The template is free to download. You can download the SCCM Power BI solution template here. The solution template is scalable and allows you to customize. You can add information about your users and computers and refine data the way you want to.
When you install this Power BI SCCM template, it is so easy to understand your environment at a glance. At the end of this post you get to see the screenshot of SCCM Power BI dashboard. A Power BI SCCM Dashboard provides high level overview of key SCCM metrics. This allows SCCM admins to quickly identify machines that are not patched with updates, failed mitigations to malware infections etc.
SCCM Power BI Dashboard Prerequisites
- Ensure you have a work email address to sign in to Power BI.
- Power BI SCCM template needs PowerShell v5 and .NET Framework 4.5.2 to be installed on machine.
- System Center 2012 Configuration Manager R2 SP1 or later is required.
- To access SCCM data you need a user account with read access to the SCCM DB.
- The machine where you install Power BI desktop should have IE version 9 or later. Also the machine should have .NET framework 4.5 installed.
- You need either Azure SQL database or SQL Server database (SQL Server 2008 R2 SP3 or later).
SCCM Power BI Dashboard Installation and Configuration
Lets start SCCM Power BI dashboard installation and configuration. To download the SCCM Power BI template, read the below update.
Update – The template download link was down since many months. Finally I got the update from Power BI team about the download. The SCCM Power BI solution template download has been moved to GITHub and you can download the template by clicking this link. You will find two templates. Download the one that you require.
- SCCMSolutionTemplate.pbix
- SCCMSolutionTemplateAS.pbix
Click Get it Now and sign in with your work account. On the Getting Started page, click Download. This will download system center configuration manager solution template on to your machine.
Run the Microsoft-SCCM template executable and on setup screen, click Install.
The installation is complete. Click Restart button.
SCCM Power BI Configuration
After the reboot, login to the machine and launch the Microsoft-SCCMTemplate application. This will bring you back to Getting Started page. Click Next.
On the Login page, specify your windows credentials to create a recurring schedule. The same credentials will be used to connect to SCCM DB as well. Click Validate and once the validation is successful, click Next.
This step is for connection Power BI to SCCM. On the Source page, specify windows credentials to connect to SCCM database. Click Validate and upon successful validation click Next.
On the Target page, specify the credentials to connect to your SQL database. Validate and click Next.
Since we have not selected any Azure DB options in the above step, we directly go to customize option. On the customize page, leave the options to default, click Validate and click Next.
Most of all you can specify email address to get the updates related to Power BI. You also get a deployment completion email to email address that you supply. Click Run to build the solution template.
After all the steps are complete, click Download Report. Save this template on your computer.
SCCM Power BI Dashboard Configuration
Launch the Power BI desktop and click File > Open and select the solution template. Once you open the solution template, you see something like shown in below screenshot. We need to configure few things, therefore DO NOT click Apply Changes at this step.
On the menu bar, click Edit Queries and then click Edit Queries. Notice on the left pane, you see exclamation icon under Queries. On the Query Editor window, click Edit Credentials. You can specify alternate credentials to access SCCM database. Finally click Connect.
If the credentials entered are correct, you will notice that exclamation symbol is gone. You also see site code, site name, version etc. Click Close and Apply to apply the changes that you just did.
Click on Overview tab at the bottom and you will now see dashboard. The below dashboard shows the information about clients and users that are part of my lab setup. When you install and configure this on your production setup, you would find more information about your SCCM infrastructure. If i get time I will update this post with screenshots of dashboards configured for my organization.
Hello and my best wishes for 2024.
I’got an issu with the PowerBI report… No way to update this… Do you have a solution ? i’m a beginner ++ with PowerBi and SQL.
Thanks to you for you’r site, you rock’s.
the link at the updated section doesn´t work 🙁
“Dieses Element ist möglicherweise nicht vorhanden oder nicht mehr verfügbar.
Möglicherweise wurde das Element gelöscht, die Gültigkeit kann abgelaufen sein, oder Sie verfügen nicht über die Berechtigung zum Anzeigen. Wenden Sie sich an den Besitzer des Elements, um weitere Informationen zu erhalten.”
Could you please update the link? 🙂
Thanks
Andreas
Hi Prajwal
Echo the comments of others
Please can you share an updated link?
Hi, would you please update the link?
the download link gives a 404.
this tool was previously installed, the DB views and tables already exist so if I reinstall this on the primary server which is the DB will it update those tables?
Is there an update to this report anywhere?
github link no longer available. Can you update please?
Hi Praj!
The link isn’t working.
I’d appreciate if you could fix it.
Thank you!
Best regards
Caio Cesar
Hi Prajwal,
It seems the download link doesn’t work, the folder is empty again.
I have just tried to figure out why the Dashboard shows data back to April 2022, and it seems you have the right answer.
Thanks,
Zsolt
Hi does anyone have a copy of the Microsoft-SCCM template executable? I can’t find it anywhere. Thanks
Same here, seems to have been removed.
Is it no longer relevant? Is there an easier way to use PBI Dashboards with current release of SCCM?
Hi Prajwal,
Thanks very much for this very important post.
We are thinking of implementing this SCCM PowerBI and that was what landed me on your page. Great post!
Anyway, I am not able to locate the files from the Github link you provided:
The message is always page NOT Found 404.
Please, can you repost the link ? Or is there something I am not doing well?
Thanks once again.
The download link has been updated.
Folder is empty pls update the link
Please check now.
hi does anyone have a copy of the Microsoft-SCCM template executable? I can’t find it anywhere. Thanks
The download link has been updated.
Hi. The download link is empty.
Has anyone figured out the issue with the error message “Action Failed Microsoft-CheckSCCMVersion”? I am stuck on trying to connect the source to Microsoft-SCCMtemplate.
Hello, I’m unable to get this working in SCCM 1910
Hello, can you update the dashboard so that it work with the latest version of SCCM.
I cannot get it working as it will show “Expression.Error:The key didn’t match any rows in the table..” so look like the table name changed with time.
You mean you see this error with ConfigMgr version 2006 ?.
change pbist_sccm to dbo and site to sites
Hi, how update table “Update”?, this moment not have information. I need verify compliance.
Power BI, not show info in articleid, bulletinid, ci_id, infoURL, etc .
Hi there, could someone explain why the Source and Target contain the same databases? Will it make a difference if the Target Database is different?
Hi my friend, how fix those information editing querie into templante?
Expression.Error: The key didn’t match any rows in the table.
Details:
Key=
Schema=pbist_sccm
Item=site
Table=[Table]
I have the same problem too and no one seams to be able to answer
I think this dashboard was compiled against an older version of SCCM, with different views available and used a different schema. Even if the equivalent views are found; they don’t seem to have the same columns in them. Some are just simple column renames in the “Transform Data” area of PBI – largely just adding spaces or removing zeroes. Others just simply aren’t there (eg Date_key in a couple of views).
…Or, alternatively, ignore what I wrote and pay attention to Tom’s post from 2nd Feb.
Quoted for convenience:
“Prajwal,
This step in your instructions causes a bit of confusion as the 2 pbix files do not start the installation of the solution which is described after
Navigate to Apps > Microsoft > Released > Microsoft-SCCMTemplate > Service > PowerBI. You will find two templates. Download the one that you require.
This should stop the Microsoft-SCCMTemplate folder and download Microsoft-SCCMTemplate.exe and run it.
Thanks for pushing the team to keep this template available as it provides good examples of setting up PowerBI to report on ConfigMgr. “
Hi prajwaldesai, I would like to use this template to analyze the system center data. But when there is new information in the database, I cannot extract the information, PowerBI updates but does not load the new information. I tried configuring GatewayBI but it also didn’t work. If PowerBI Desktop does not search for updated information GatewayBI will not bring it either. Can you help me ?
This has not been an easy thing to get loading with the data source. I am getting “the key didn’t match any rows in the table.” under the Site after applying query changes. Suggestions?
I too am having the same issues. It appears the Site table query needs to be updated, but I am not sure to what.
Hi Prajwal, great article! I am not using the installation tool which asks for connection to Azure since I’m not using Azure.
I’ve installed and launched PowerBI (Run as Admin) on the CM1902 Primary site server itself where the Database resides, have datareader permissions on the DB and Full Admin permissions in the console.
I am seeing exclamations for the items included in your screenshot. When I check the first one, Site for example, I can see under APPLIED STEPS the Navigation entry shows the following:
Expression.Error: The key didn’t match any rows in the table.
Details:
Key=
Schema=pbist_sccm
Item=site
Table=[Table]
For every other item with an exclamation mark, in APPLIED STEPS, there is also an entry for Navigation with this error. I know how busy you are but I’m guessing a lot of others are having the same issue and I have checked every site which has posted about this dashboard.
I think I figured it out, the query was pointing to the wrong table. Can you please tell us which tables are selected for each? For example, for Site – I have selected various tables which have Site in the name and none of them look like your screenshot (starting with Site Code, Site Name, Version).
Prajwal,
This step in your instructions causes a bit of confusion as the 2 pbix files do not start the installation of the solution which is described after
Navigate to Apps > Microsoft > Released > Microsoft-SCCMTemplate > Service > PowerBI. You will find two templates. Download the one that you require.
This should stop the Microsoft-SCCMTemplate folder and download Microsoft-SCCMTemplate.exe and run it.
Thanks for pushing the team to keep this template available as it provides good examples of setting up PowerBI to report on ConfigMgr.
How can I remove the SCCM Solution Template, including its PowerBI database and all associated files? One previous admin installed this on our environment and now we need to remove it, as we are not using it anymore and there is disk space being consumed. How to do that the safest way?
I never tried that but let me test that in my setup soon and update you.
Excellent updated explanation, you are always here when you are needed 🙂
I am trying to install the SCCM template and the installation is failing with Scheduled task exited with code 1 — Action failed Microsoft-GetDataPullStatus —Error ID:(4knh8fn8kvt). Think it is step 21. Both my source and target DB’s are on the same server so it isn’t a network error. All steps validated successfully and i pretty sure i have the needed permissions. I have also checked all the prereqs (.net and powershell, etc). Has anybody experienced this and solved it??? Thanks in advance!
Up.. up.. up.. Me too.. data not load in computer collection.
thanks worked fine…
Hi What is the difference between the two templates?
When I opened the AS template, it errored with reference to Analysis Services which are in Azure. So my deduction is that one template is for SCCM/SQL on-prem and the AS template is for Azure hosted services. Not 100% confirmed.
How do we update the tables that this setup creates?
is there anyone struck with “Load Cancelled by an error in loading previous table” ?
i don’t where to lookiing at
Hello , all ,
is there anyone struck with “Load Cancelled by an error in loading previous table” ?
i don’t where to lookiing at 😀
I was able to get Power BI installed, opened the Template, Connected to our DB, but when i go to edit credentials the Site works and the Triangle goes away, but the other Queries/tables still show the Triangle/!.. Expression.Error: The Key didn’t match any rows in the table, Details: Key=Record, Table=Table.. Is there a possible issue with the DB or Template ?
Grabbed the updated template from Github and it seems to follow the guide better, but it isn’t Validating on the Target Step, it validates Source, but on Target says No SQL Databases were found on SQL Server…
I will have a check on this.
Hi, I am seeing that exacts same issues as above? Has there been fixes/updates please
Just joining in to say, I’m experiencing the same error. Site is at v1810.
Expression.Error: The key didn’t match any rows in the table.
Details:
Key=Record
Table=Table
Is there any solution for this problem? A half year later i have the same problem. 🙁
Did anyone came to a solution on this? as of mar/2021 and the error persists
This doesn’t have the Microsft-SCCMTemplate file, so there is no way to configure the database. Can you share the file with us?
Hi Jordan, I don’t have it with me currently. I have asked the powerbi team to provide me the file. I haven’t received any response yet.
Hey Prajwal,
Did you ever find a valid link to download the template? I’m searching every where but can’t find it.
This is the only one I could find.
https://gallery.technet.microsoft.com/PowerON-Power-BI-ConfigMgr-0cd61c5f
The source SQL server has been upgraded to different server name. How can change source SQL server ? need your help
Hi,
If there is an update on the SCCM solution template will there be an impact for the existing power bi report which we have already deployed?
I don’t think so. I might test this once on my lab setup.
Great post, i have so many ideas from this, thanks.
Thank you so much.
good day Prajwal,
i am very new to PowerBI, it might be a stupid question,
i need to use PowerBI for our SCCM deployment reporting , i am a bit confused as we have SCCM application server and SCCM SQL Database Server , where should i install/ configure SCCMTemplete for Power BI, on Application server or SCCM SQL DB server, kindly reply.
Thanks.
@Ken Piper => have you found a solution ?
I m exactly facing the same situation , where machine to run the template is not on the same domain as the SCCM database, therefore as same credential as the service creation is used to connect to the sccm DB , I can not passthrough.
Would be valuable to be able to have different set of credential to connect to the SCCM DB.
Hi Prajwal!
Is there any way to export data of a specific deployment status (I have created a task sequence to update some computers on a specific collection) to a PowerBI template or something like that? Or, how can I import that info from SQL Server? As you can see, I’m a newbie on Power BI.
Thanks for advance!!
Hi i have a problem with this. In the window System Center Configuration Manager database, I can’t connect to database, error is: This does not look like an SCCM database or you do not have read permissions (Action Failed Microsoft-CheckSCCMVersion — Error ID:(hjwlffqdq7i))
What permission i need in the database??
Thanks
Im experiencing the same problem.
Did you find a solution for this problem?
No, sorry =(. I update SCCM version to 7010 and the problem persist. If you can find the solution, please tell me.
Any update on the link?
Nope.. I haven’t got any response from powerBi team. I am following up with them on twitter.
The link to the template now seems to be broken.
Has the template been pulled for some reason?
Anywhere else I can get it from?
Hi, I am checking with Power BI team on twitter. Looks like is removed from downloads.
The download link has been updated.
The link is still broken, could you make the download available again?
Any chance link can be updated? Cannot find these templates anywhere. Thanks
I’m confused between Source and Target setup page. Is it referring to the same SQL server ? Should Target database be the same SCCM source database ?
Any way to configure to access an SCCM server in a separate domain(or extract the template from the executable)?. I receive an error
“Login failed for user – View Details The security database on the server does not have a computer account for this workstation trust relationship” when attempting to connect to SCCMserver.sccmserverdomain with sccmserverdomain user credentials which have dbo permissions to the SCCM/SCCM ReportServices databases when logging in from a workstation on a domain without a trust relationship to sccmserverdomain.
Hi, we have a single server environment (one server does everything – SQL and SCCM) as we are a small environment. Do you know if there would be any issues installing this on the same server ?
Thanks
I don’t think so. PowerBI desktop can be installed on your laptop/system as well.
Thank you, I will try it
Thanks!! That’s an interesting tool for show on a big screen to my boss 😉