I’ve recently joined the PowerOn Platforms team and been working with SCCM and Power BI to develop some reporting dashboards which I wanted to share.
This blog displays some overview information for visually seeing a site’s SCCM configuration through Power BI. I’ve also developed some dashboards which can be found here.
Client Overview
This view contains a basic overview of client information such as, End Point Compliance, Client Activity and health.
Workstation / Server Breakdown
This contains a breakdown of your site workstations/Servers including manufacturer, operating systems, architecture and even chassis type info! The Chassis Type information goes off to a technet page so you may need internet access when initially loading your data.
Client Health Summary
Contains summary information on your clients health and activity status.
Update Compliance
This view contains a basic overview of client information such as, End Point Compliance, Client Activity and health.
EndPoint Compliance
This view contains summary information on client EndPoint compliance and protection status.
Site Admin
Now this is the cool part… The site admin view queries the configmgr dogs technet post for the latest SCCM release. It also queries the Microsoft support life cycle site and checks for expired or expiring operating systems currently deployed which you will need to address.
The Deployed Expired/Expiring Operating Systems slicer also allows you to filter the OS to give you an indication of whats expiring by count.
Installation
You can download the file from the Technet Gallery (please note this download is currently unavailable and we are working to provide the file via a different method)
Ensure you have at least read access to the SCCM SQL Database and you can access the Internet from this machine running Power BI.
Once installed Open Power BI.
1. Go to File > Options and Settings > Options
2. Under Privacy enable Fast Combine.
3. Under security, un-tick require user approval for native queries and close the options.
4. Select Edit Queries to load the Query Editor
5. Select the Configuration Query and invoke the function to specify the SCCM SQL Server and Database. (Note: If Configuration query already shows server, remove the “Invoked FunctionSQLSource” from the applied steps.)
6. Refresh the data from the top ribbon.
7. If prompted for credentials, specify account wit access to read the SCCM DB.
8. If prompted, press OK on the encryption support option.
9. Do one final refresh and your good to go!
Publishing to the Web
The desktop version of Power BI is free to use and I used this without publishing the report up to the web just to produce a quick report.
The data is on this report is not live as its only from the last time you load the data but you can easily reload the information on startup.
For an even better experience, you can publish the file up to the web and create a dashboard. though there can be cost implications but there is a free version. Check on Power BI’s website for further information on what best suits you.
The great thing about publishing is you can utilise the ask a question feature which is awesome!
Power BI also comes with a mobile app which allows you to access the report from your mobile device anywhere too.
Hopefully you’ll love the dashboard as much as I do! :). Feel free to leave feedback, or if you have any ideas which may improve or add to this. Don’t hesitate to contact me via twitter at @Raf_Delgado.
