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!

(8442)

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.

          • Arnaud
            Posted at 17:05 March 22, 2019
            Arnaud
            Author

            Hello,

            I have the same issue, too much information ๐Ÿ™‚
            can you give me the SQL query ?

            Thx!!

          • Jordan Benzing
            Posted at 22:05 April 7, 2019
            Jordan Benzing
            Author

            The query is in the report and also is posted on TechNet where the download is stored ๐Ÿ™‚

  • 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

  • Stephane
    Posted at 15:08 March 26, 2019
    Stephane
    Reply
    Author

    Hello , i put here same comment as in Technet

    This report is brilliant , we use it in Production , however compliance reported is fooled due to this particular KB : 890830 , same KB appears for february and march

    Windows Malicious Software Removal Tool x64 – February 2019 (KB890830)
    Windows Malicious Software Removal Tool x64 – March 2019 (KB890830)

    as a result , when i select the month february on Software Update Group then i see “Windows Malicious Software Removal Tool x64 – March 2019 (KB890830)” and i don’t see “Windows Malicious Software Removal Tool x64 – February 2019 (KB890830)” and because we have not yet release the patches for March then compliance is fooled.

    More , the installation for “Windows Malicious Software Removal Tool x64 – February 2019 (KB890830)” are override and not taking in consideration

    Hope i am clear

    Is this can be investigated ?

    Thanks!

    EDIT , the february KB has been superseded by the March KB , if i comment out “and v_UpdateInfo.IsSuperseded = ‘0’” on the Compliance Info Query then i see that KB for february in my february Software Update Group but i still see the march KB so we are still fooled.

    In other words , the compliance % for my february month is fooled as we have not yet release that March KB for malicious tool

  • revenueboomers.com
    Posted at 08:03 April 22, 2019
    revenueboomers.com
    Reply
    Author

    Fine way of explaining, and nice post to get facts regarding my presentation focus, which i
    am going to present in college.

  • yogapantsdistro.com
    Posted at 11:41 April 22, 2019
    yogapantsdistro.com
    Reply
    Author

    Appreciating the hard work you put into your website and detailed information you present.
    It’s good to come across a blog every once in a while that isn’t the same out of date rehashed material.
    Great read! I’ve bookmarked your site and I’m including your
    RSS feeds to my Google account.

  • Brian
    Posted at 14:31 May 17, 2019
    Brian
    Reply
    Author

    Love the Power BI Dashboard. One question, is there a way to see Unknowns added or do those get rolled into Non-Compliant. I just notice my collection count is off between console and the Dashboard.

    • Jordan Benzing
      Posted at 16:25 July 16, 2019
      Jordan Benzing
      Reply
      Author

      The report does not currently display unknown computers. This is because th the view that is used does not include that information. When you include that information at scale it often balloons the report to the point where it is unreasonable runtime. Most reports have this same challenge unless they only load a small pre-defined amount of data at which point the information is again segmented and no longer useful in the big picture view.

  • Matt
    Posted at 10:11 July 9, 2019
    Matt
    Reply
    Author

    Finally a useful report I can trust to provide useful information to all levels of management.
    One thing I can’t seem to get around though, i can only see the last 3 months worth of updates.
    I am using v2.5 which is where i believe this was sorted?

    • Jordan Benzing
      Posted at 16:26 July 16, 2019
      Jordan Benzing
      Reply
      Author

      Typically this behavior is caused by superseding your updates every three months.

      Check your rules for when you expire and clean up updates.

  • Leave a Reply to Anton Reiman
    Cancel Reply

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