MSEndpointMgr

Patch Compliance Reporting in PowerBI Report Version 2.5

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!

Jordan Benzing

Jordan has been working in the Industry since 2009. Since starting he’s worked with Active Directory, Group Policy, SCCM, SCOM and PowerShell. Jordan most recently worked in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Jordan currently works as a Senior consultant for TrueSec Inc in the U.S. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.

24 comments

  • Can you filter compliance in Patches for Severity? I currently do not patch DTS time changes for Brazil for example but that takes into account and makes the dashboard show no compliant.

  • Unfortunately I cannot get this to work. I’ve tried editing the query as well.
    All of my patching collections have %Patching: % in the name. No matter what variation I try, it only pulls one single collection and ignores all the others.

    It worked once but on a refresh all the collections were gone.

  • Great reports. The only thing I’m having trouble with is the “Collections” tabs for Servers and Workstations is always Blank. No matter what filters I try, it never pulls collections. Thoughts?

  • 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?

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

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

  • 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.

    • 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.

  • 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.

  • 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

  • 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

    • Hi Gerry, I am seeing the same issue where I select an collection with 15 servers and the report will return back info only for two machines from that collection.
      Can you tell me please what I need to change so I can get in return info for all the machines from that collection?

      Thanks in advance.

      @Jordan, thanks for your hard work, SCCM is really missing good reports.

    • Jerry, sorry for the bump on this so long after, but how did you get that change working? My attempts at it make every device show up in every collection in the report.

      Thanks!

  • 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

  • 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

  • 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

    • 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.

      • 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.

      • 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.

      • Hello,

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

        Thx!!

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

  • 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! πŸ™‚

    • 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.

Sponsors