I recently ran a poll (back over Christmas) asking the community about what they would like to see and by popular demand people asked for an update to the PowerBI patching dashboard and man with all of the little bugs I found am I glad I listened to the community. If you are looking to download the next version of the template you can download it from TechNet here:

https://gallery.technet.microsoft.com/Patch-Compliance-Reporting-95fa01f7

In this version I spent a lot of time working on fixing various small visual bugs, and pulling back MORE data. The first thing that’s an important note is that I have REMOVED the date range restriction on how far back we retrieve compliance information. Originally, I limited the number of updates that could be retrieved by only going back two years in time. Now I go back to get compliance information against all patches in the database. This can cause the performance of the report to really struggle however it provides a more holistic overview of the environment for patch compliance. I have only tested the report in an environment of 400 machines and NOT at a large scale of thousands of machines. I strongly recommend running the SQL queries one by one to check the run time for each one. The used SQL queries are featured on the TechNet page.

Now onto bug fixes:

  1. Fixed Many to One and Many to Many relationships
  2. Adjusted Date Range of past updates (No longer only retrieve patches from last 90 days)
  3. Fixed Source Query being broken due to an upgrade
  4. Removed some dials from Organization Overview Corrected
  5. Corrected the filters on the front page to all use the same data table
  6. Corrected the Servers Page text (Said Workstations in some spaces)
  7. Corrected the filters on the Servers/Workstations page.

Feature Changes:

  1. Updated color scheme. Red and Black was unclear on compliance information
  2. Added SUG selection to front page and made it something you could interact with to view an ‘overview’ dial based on SUGS
  3. Updated the workstation/server pages to include the last time the patch was evaluated for compliance.
  4. Added a collection filter – you can now only pull back some specific collections to filter the data against.

This has been really fun to work on and now to show some pretty cool features when you first open the PowerBI Template you will be presented with a prompt for some parameters:

Each of these parameters is relatively self explanatory but just in case:

CollectionFilter:

This is a filter to only retrieve collections based on a filter. This uses SQL wild cards and allows you to look at the patch compliance of machines in specific collections. An example would be if you had some maintenance window collections and wanted to return everything that matched your maintenance window naming standard you would enter something like %MAINT%

ServerName:

This is the name of the server that runs the SQL database for configuration manager. Enter the server name here you can use FQDN you will also need to be running PowerBI as a user that has rights to the ConfigMgr database and depending on your security levels may have to run PowerBI as an admin.

DatabaseName:

This is the name of the data base for your site. Typically something like CM_PR1 or CM_P01.

Once you enter the correct information you will be prompted for permission to run those queries against the database. Approve them and then you’ll see it run for a period of time before it returns some information back. That information will look like this:

 

From there you’ll be able to get a good overview image of your environment. You’ll also be able to select a software update group from here to cause the dials to change. By default you’ll notice a target goal over on the side for the radials. I have set the target to be at least 80% client install and 80% patch compliance.

At the bottom of course we have our standard compliance tabs for different operating systems with different ways of filtering the data.

And the final page which shows the client penetration in your environment. I think this will be the next area that gets some love to include some of the client health information that is out there.

Finally a little video of the dashboard in use using all the little dials and buttons.

Let me know what you would like to see next!

(2361)

comments
  • Steven
    Posted at 20:39 February 1, 2019
    Steven
    Reply
    Author

    Hi Jordan! Could you filter or create another tab that is exclusively for the same type of metrics you have but just for 3rd party updates now that SCCM has native 3rd party update support built in? Currently, we use both PatchMyPC and Dell Command | Update Catalog. We have been using the basic SCCM PowerBI Template from Microsoft which is great but that solution wasn’t built in mind of these things. Unfortunately, it combines all of these update types into a single pane of glass and skews our compliance with that solution because it measures all updates types, and classifications together. We’d like to see a solution that has MSFT updates as one compliance item and another as 3rd Party. Thank you! 🙂

    • Jordan Benzing
      Posted at 14:40 February 4, 2019
      Jordan Benzing
      Reply
      Author

      I could absolutely create something for third party patches. The challenge would be in finding something to filter on to make sure I break them apart. Since ConfigMgr treats updates as updates. I’ll have to add a third party catalogue do some testing and get back to you 🙂

      The SCCM PowerBI template from Microsoft is pretty great but its a LOT of data to answer some really simple questions.

  • Anton Reiman
    Posted at 09:13 February 5, 2019
    Anton Reiman
    Reply
    Author

    Hi, I am trying to run this on a site with 8000 workstations and the compliance info query has been running for about 2 hours and loaded over 250 000 rows… Not sure if this is expected

    • Jordan Benzing
      Posted at 14:42 February 5, 2019
      Jordan Benzing
      Reply
      Author

      I made a change between this version and the previous version due to numerous requests to go all the way back in time for ALL updates in the database. If you want to limit the number of updates compliance information is returned for to the past two years you’ll want to edit the complianceinfo query to use a where statement to only retrieve updates retrieved within last X number of days. Let me know if you would like the updated query so you can update it.

      • Anton
        Posted at 04:19 February 6, 2019
        Anton
        Reply
        Author

        I am not that big with SQL queries so I would appreciate an updated statement to include -30 days for example. Happy to change the PowerBI side, that is easy! Thank you for your assistance.

        • Jordan Benzing
          Posted at 15:16 February 6, 2019
          Jordan Benzing
          Reply
          Author

          I’ll update that and get something to you. I used to have a parameter that allowed you to only return updates released within the last X number of days based on a parameter.

  • Gregor
    Posted at 22:40 February 5, 2019
    Gregor
    Reply
    Author

    hi
    we use the on-premise Power BI Server, (January) and i got this:
    Error Message:
    This file uses many-to-many relationships or a composite model which combines DirectQuery sources and/or imported data. These models aren’t currently supported in Power BI Report Server.

    its possible to get a version for on-premise?

    thx

  • Jerry
    Posted at 13:35 February 6, 2019
    Jerry
    Reply
    Author

    Hi, Love this report – I have been using one of your early ones and changed it as I needed but thought I would try and update it with your new queries etc – I had added a collection filter before myself but good to see you have one in yours now. To my question – when I copy the SQL from your TechNet page and run it in Management studio I get a list of 42 Collections that begin with “WSUS” but when it runs in your report there are only 17 – any ideas what would be causing this – I can’t see anywhere were you would exclude any etc

  • Jerry
    Posted at 13:42 February 6, 2019
    Jerry
    Reply
    Author

    to my previous question – I found the issue – the collections are limited by a “distinct” so if they are in multiple different collections then it will only return the first one.
    “= Table.Distinct(Source, {“Name”})”
    I’ll change it in my environment – we have all our updates controlled by collections that are controlled by ad groups so a server or indeed a workstation can be in 4 or 5 different collections to do with WSUS

  • Leave a Reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.