MSEndpointMgr

Patch Compliance Reporting in Configuration Manager with PowerBI

If you’ve done any work with System Center Configuration Manager sooner or later, you’ll get asked about leveraging it for patching. It might even be one of the first questions you get from management. That’s great because after all, patching with ConfigMgr is relatively simple provided you are allowed time and resources to create and ENFORCE a patching plan. However, while patching itself is relatively ‘simple’, reporting on what the heck you did can be… well challenging doubly so when you have to articulate results to a manager. Now, some of you are nodding your heads with a smile and others are probably banging their head in frustration while reminiscing on all of the hours they’ve spent on reporting. I know I’ve spent hours creating unique reports for my organization and trying to make them look pretty and easy to consume. Then this thing happened.

I’ll be honest when PowerBI came out I didn’t think anything of it. I certainly didn’t think I would get questions about it within a week or that I would enjoy using it. Nevermind the fact that after I started playing with it I realized there were a lot of things I could do with it that I simply didn’t have the time to figure out in SSRS and when I made the foolish mistake of showing management how I could make nifty pie charts that could be changed without having to re-run a report every single time, well:

Now I understand that PowerBI and SQL are not everyone’s thing and writing DAX expressions in PowerBI to create meaningful and actionable data is tricky especially with how young the product is. However, once you figure out how it works together, it is capable of creating very pretty dynamic reports that managers can consume and you can turn into actionable data when remediating non-compliant devices.

To help you out so that you don’t have to spend hours trying to figure out how to report on patch compliance I’ve created a PowerBI template you can download from the TechNet Gallery:

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

Here’s a screenshot of it in my lab environment:

Now anytime I present a report to management, I always have to make sure I explain very carefully what the report means because data without context leads to sad times for everyone involved. So let us take a look at the SQL Query that collects the data first.

Select v_R_System.name0 as 'Name'
       , v_RA_System_ResourceNames.Resource_Names0 as 'FQDN'
       , v_R_System.Resource_Domain_OR_Workgr0 as 'DOMAIN'
       , v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System'
          , V_UpdateInfo.ArticleID
          , CASE 
                     WHEN v_Update_ComplianceStatus.Status = '2' THEN 'MISSING'
                     WHEN v_Update_ComplianceStatus.Status = '3' THEN 'INSTALLED'
              else 'UNKNOWN'
              END AS 'PatchStatus'
          , V_UpdateInfo.DateCreated
           , V_UpdateInfo.Title
           , V_UpdateInfo.InfoURL
           , V_R_System.ResourceID
from v_R_System
left join v_Update_ComplianceStatus on v_R_System.ResourceID = v_Update_ComplianceStatus.ResourceID
left join V_UpdateInfo on v_Update_ComplianceStatus.CI_ID = V_UpdateInfo.CI_ID
left join v_RA_System_ResourceNames on v_R_System.ResourceID = v_RA_System_ResourceNames.ResourceID
left join v_UpdateScanStatus on v_R_System.ResourceID = v_UpdateScanStatus.ResourceID
left join v_GS_WORKSTATION_STATUS on v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
left Join v_GS_OPERATING_SYSTEM on v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
where V_UpdateInfo.DateCreated BETWEEN '2017-01-1 00:00:00.00' and GetDATE()  and v_UpdateInfo.IsDeployed = '1' and v_UpdateInfo.CIType_ID = '8' and v_UpdateInfo.IsSuperseded = '0' and V_r_System.Operating_System_Name_and0 like '%server%' 

This query is set to look for the following criteria, and this information can be changed. If there is enough interest, I will include parameters within PowerBI to allow it to be changed without modifying the SQL source code and take other user feedback for improvements. I’ve intentionally written it this way to start with a smaller data set to avoid overwhelming the database.

  • Updates must be in a state of deployed for a device to be graded against the update
    • Devices will NOT be graded if a patch is not deployed but WILL be graded if the update is deployed ANYWHERE in the environment regardless of the target.
  • Compliance information will only return if the Operating System name includes ‘%Server%’
    • This can be changed by modifying ‘V_R_system.Operating_System_Name_and0 like ‘%Server%’ to ‘V_R_system.Operating_System_Name_and0 like ‘%Workstation%’ – Note this will only return workstations and depending on the size of your environment and cleanliness of DB may impact performance. I recommend testing the Query in SQL Server Management Studio before using the report OR changing the query.
  • Updates will only be evaluated if they are NOT superseded.
    • This is important for the cumulative rollups and how they function.
  • Updates must have been released between January 1st, 2017 and the date of the query being run.

So you’ve run the SQL query didn’t have any performance issues, installed PowerBI and now you’re ready to use the shiny template to look at your servers and their patch compliance. Fantastic here is a quick video on how to implement.

Remember this video assumes you are logged in and using an account that has view rights to the database. If you are trying to do this while logged in with an account that does not have ConfigMgr rights, you can but will need to edit the credentials to use a different windows account and if you are using a non-domain joined device you CAN but you will need to launch the PowerBI.EXE with the runas /netonly command.

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.

30 comments

  • I seem to have an issue where by some clients seem to be missing CI_ID and therefore don’t show in the report. Have you seen this before? When checking the clients are in the SUG and Collection i have referencing

    Thanks for the report , great work

    • That’s interesting, clients should never be missing a CI_ID as that’s one of the primary ways they are referenced in WMI. I have seen other objects lose their CI_ID in ‘strange’ scenarious but not usually machines. Can you give me some more specifics?

  • Interestingly, in my environment (at least), nothing comes back as “Unknown”. There is only status 2 and 3 in v_Update_ComplianceStatus. When I look at a specific update in the console, however, I see over 100 unknown. Are we sure we are gathering the right data here?

    • The data is right for what it should be representing.

      If you want to go ‘deeper’ into the weeds you have to look at the ALL status’s view in SQL. however this view can be so big that it makes SQL unhappy when you try to load it. Unknown is usually a whole different animal as well.

  • Hi Jordan, absolutely love the PowerBi implementation of a decent software update dashboard, I’m pretty new to PowerBI and I’ve added what I want in terms of a control field for the server dashboard (added CollectionID query below as a query in query editor).
    Basically I want to use a collection to control what is presented rather than “SoftwareUpdateGroup” – the way we chop up our monthly cycle the Update group is of no use, here is the SQL I use for an SSRS report, how easy / how would I replace the softwareupdategroup with a collection picker

    let
    Source = Sql.Database(“SQLSERVER”, “SITE”, [Query=”SELECT DISTINCT SiteID AS ‘Value’, CollectionName AS ‘Label’ FROM v_Collections
    where CollectionName like ‘wsus%’
    ORDER BY ‘Label’”, CommandTimeout=#duration(0, 0, 5, 0)])
    in
    Source

    • Jerry,

      There should actually be a new option to do a collection Picker in the newest version of the dashboard.

  • Hey Jordan. Thanks for your help!

    I’d love to ping each other offline about this. Maybe we can help each other out. Can you email me at the address this comment is under? 🙂

  • Would it be possible to get the option to choose a collection to view compliance against on this dashboard?

    • This can be tricky as some locations have a LOT of collections. I was hesitant to add such a feature without some filtering in place. This WOULD be a relatively easy dataset to gather though you would just need to query the V_FullCollectionMembership and then tie the information up to the V_G_Collection/s table to filter back the ones you wanted visible in the report and then attach it to the data mappings. Basically you would be adding another relationship in. It would look something like this:

      select v_FullCollectionMembership.Name
      , v_Collection.CollectionID
      , v_Collection.Name
      from v_FullCollectionMembership
      Left Outer Join v_Collection on v_FullCollectionMembership.CollectionID = V_Collection.CollectionId
      where V_Collection.Name like ‘%’

      And replace the % sign with format of your patching collections, hopefully you have a good naming standard.

  • Jordan, I’m new to Power BI but this report is awesome to show management. I noticed in the workstation compliance that it doesn’t seem to show the Windows 10 patches deployed. Did I miss something when I was settings this up?

      • Can I simply update the SQL queries, or do I need to start over?

      • I’m not 100% sure what you mean. What SQL Queries are you trying to update?

        You should be able to just download the new version of the template update the data source information to point to your SQL server and refresh the data.

      • Yeah sorry, I thought I might be able to just copy and past SQL queries instead of downloading the new template. I ended up downloading your newest release, but still having a bit of trouble with Windows 10 updates showing under Workstation Compliance. The Windows 7 ones show up, but only one of my (recent) Windows 10 Software Update Groups shows. I’ll keep digging.

      • Ah, you could I technically suppose copy paste them but the new template has a bunch of newer things/changes in it.

        You should see more recent software update groups if they meet the criteria just a matter of finding the filter causing the behavior.

        -J

    • Good catch I forgot to mention that in the post, I encountered that when testing, but forgot to make a note of it. Thanks.

  • This is great. If I wanted to further limit this to looking at just the updates in a SUG how would I modify the query for that? I have a query I use in SSRS but when I modified this query to be similar it seemed to duplicate the updates for each system. Thanks!

    • The ‘simplest’ way to do this would be to modify the where statement. This method is a little dirty but it should be very efficient.
      1) Copy Article ID’s from your SUG
      2) Arrange in following format (‘ArticleID’ , ‘ARticleID2’ , ‘ARticleID3’) and so on. You can do this quickly if you use notepad ++ find and replace with \n and \r commands to replace all of the return keys and new lines with ‘ , ‘
      3) Modify the where statement: where V_UpdateInfo.articleID in (‘ArticleID’ , ‘ArticleID2’ , ‘ArticleID3’) and v_UpdateInfo.IsDeployed = ‘1’ and v_UpdateInfo.CIType_ID = ‘8’ and v_UpdateInfo.IsSuperseded = ‘0’
      4) Run the query.

      This should get you the results you want. Now it’s not nearly as pretty as using the SUG or the appropriate relationship logic or the function that does the gather but I think that might deserve its own dedicated post and is more writing than a quick comment. Let me know if this helps you out!

  • Really nice post Jordan. I have a question about PowerBi and maybe you can help, is there a way to implement RBAC on this kind of reports or on PowerBi itself?
    I look after a multi tenant environment on SCCM and we’d like to start using PowerBi but every tenant should only see the data pertaining to them. Like the SRSS supports using the functions.

    Thanks

    • This is a great question and is probably deserving of its own post. There are a couple of different ways you can do permissions some within PowerBI others are well not. Like a lot of things for permissions can get really complex really fast and I would suggest thinking about your specific implementation of PowerBI. Here is one of the methods you could investigate using RLS (Row Level Security). This would actually allow you to use the same report but restrict certain users from not being able to see specific rows in that report. I’ll put something about permissions on my list of things to write about in the coming weeks thanks for the feedback!

      https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

  • I would love to see more posts on SCCM & PowerBI reporting – this is such a great tool.

    • Thanks I’m glad you like it! I’ve got a few ideas for additions to the report, anything in particular in reporting/PowerBI you would like to see specifically?

      • As an addition to the current one perhaps a section that displays a list of clients ordered by how many updates are missing? It’d be useful to be able to target those users/devices and see why they aren’t updating.

        The new place i’m working at current doesn’t have SCCM – I was wondering if this is possible by just looking at WSUS database?

      • In regards to the addition, I do have it set up to list the number of updates that are missing per client in the bottom right hand corner but you make a good point about error codes. I’ve got a pretty comprehensive list of WSUS error codes but sometimes the issue is the environment and not the client and that starts leading down the path of Client health. I’ll think about it and see what I can come up with on that front.

        The data in this PowerBI dashboard does rely on SQL tables that exist within SCCM and are not native to WSUS. However, most of the data you’re talking about comes from WSUS and goes into ConfigMan in your case, assuming its WSUS on SQL and not WID, it would just be a matter of re-formatting the SQL query to pull the info from the right tables in WSUS you could probably still leverage most of the framework in the PowerBI template as a good starting point!

      • Hi Jordan,

        I will give this a try next week. I wanted to know if you have a PBI report showing clients that are 30, 60, or 90 days out of patching compliance? Basic report for Management. I looked around online for an SSRS report and have not found anything.

      • This would certainly give you a good start. Step one is defining what does 30/60/90 days look like for your organization.

        I know this sounds silly but its a serious debate, are we talking 30 days from patch release date? 30 days from when the patches were approved in your environment? Important things to factor in include how supercedence works now with the cumulative updates. It’s easy to forget that with the cumulative model essentially every 30 days our reports have to change because our compliance metric has changed and no longer ‘matters’.

        There isn’t really a one size fits all answer to this you’ve got to have a serious conversation with management first and determine the true business needs/demands that also meet security ‘best practice’.

  • This report is great! Glad I could help you test it out last week. I am now using this in my environment to pinpoint non-compliance and eliminate it with great prejudice. I will be looking at PowerBI for custom reporting in the future as the possibilities are endless.

Sponsors