Power BI - SCCM Dashboard Reporting

Update 07/07/2016

Version 1.4 is now out, please refer to below link to view the latest version.

PowerON Power BI - ConfigMgr Dashboard



So I've recently joined the PowerOnPlatforms team and I've been working with SCCM and Power BI to develop some reporting dashboards I wanted to share :). This report displays some overview information for visually seeing a sites SCCM configuration through the free Power BI desktop.



Client Overview

This view contains a basic overview of client information such as, EndPoint 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 breakdown for update compliance, showing you missing updates by bulletin ID.


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.





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 now :). Feel free to leave feedback or if you have any ideas which may improve or add to this, feel free to contact me via twitter at @Raf_Delgado or comment below.



Update 1.1

Added DB Fragmentation Report into the dashboard. SQL query from Kent Agerlund's NIC 4th Edition.




Next PostNewer Post Previous PostOlder Post Home

16 comments:

  1. I want my reports to look like yours how do I make this happen?

    ReplyDelete
    Replies
    1. Hey Cory, thanks for the comment. How what have you done so far? Have you got the configuration pointed to your site server and DB? If so you should just reload the data and it should be OK.

      Delete
  2. Hello Raf ,

    I have a misktake when i run data updating process. =>
    DataSource.Error : enable to load the file or assembly System.transactions Version 4.0.0.0 etc...

    Do you know how i've got this mistake ?

    Thanks for your help.

    ReplyDelete
    Replies
    1. Hi Fahrenheit97,

      Sorry for the late reply.

      Someone else got this a while back on the technet gallery, they resolved this by running Power BI with an account with admin credentials.

      Thanks,

      Raf

      Delete
  3. Hi Fahrenheit97,

    Sorry for the late reply.

    Someone else got this a while back on the technet gallery, they resolved this by running Power BI with an account with admin credentials.

    Thanks,

    Raf

    ReplyDelete
  4. There appears to be an error in the logic applied to Malware Detection Details. If a machine has multiple "hits," the table will not load due to the one-to-many relationship that keys on ResourceID. Any suggestions on how to fix?

    ReplyDelete
  5. I followed all of your instructions, but I am getting an error. It seems to be timing out at the Support LifeCycle (Web Reference)
    DataSource.error:the Web.page function didn't finish within timeout of 100 seconds then all of the rest error out with load was cancelled by an error in loading a previous table. Any thoughts?

    ReplyDelete
    Replies
    1. Hi Matt,

      You'll need to decent connection to allow the web pages to refresh. Alternative

      Delete
  6. Hi Matt,

    You'll need to decent connection to allow the web pages to refresh. Alternatively, you can turn the refresh on the query for the suupport cycle off and use the current set.

    ReplyDelete
  7. Hi Raf,

    My database connection is fine but keep getting error at refresh of _Client information "Expression.Error: We cannot convert a value of type Function to type List." Ideas?

    ReplyDelete
  8. What do you put in the Database field under optional?

    ReplyDelete
    Replies
    1. You would have to change each query and hard code the variable which is the database.

      Thanks,

      Raf

      Delete
  9. Hi Raf,

    This is excellent! I love it. My only problem is the DeviceName under certain reports is an 8 digit number, not the machine name. Any ideas?

    Thanks!

    Mike

    ReplyDelete
    Replies
    1. Hi Mike,

      There is a parameter to set whether you want the device to be the computername or the resourceid from the DB, sounds like its the resourceid.

      Thanks,

      Raf :)

      Delete
  10. Great rev up. Had everything working great until recently. I'm now getting an error refreshing the data.

    SCCM Highest Current Version Number
    Expression.Error: The column 'Version' of the table wasn't found.

    ReplyDelete
    Replies
    1. The technet website changed its label, we've a new version coming out very shortly which fixes this.

      Delete