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.

(2770)

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 currently works in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.

comments
  • Pete M
    Posted at 13:29 November 7, 2017
    Pete M
    Reply
    Author

    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.

  • Jack
    Posted at 12:29 November 8, 2017
    Jack
    Reply
    Author

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

    • Jordan Benzing
      Posted at 14:40 November 8, 2017
      Jordan Benzing
      Reply
      Author

      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?

      • Jack
        Posted at 12:27 November 9, 2017
        Jack
        Reply
        Author

        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?

        • Jordan Benzing
          Posted at 15:37 November 9, 2017
          Jordan Benzing
          Reply
          Author

          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!

  • Jose
    Posted at 23:09 November 12, 2017
    Jose
    Reply
    Author

    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

    • Jordan Benzing
      Posted at 01:48 November 13, 2017
      Jordan Benzing
      Reply
      Author

      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/

  • Jonathan
    Posted at 00:41 November 14, 2017
    Jonathan
    Reply
    Author

    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!

    • Jordan Benzing
      Posted at 01:00 November 14, 2017
      Jordan Benzing
      Reply
      Author

      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!

    • Jordan Benzing
      Posted at 21:02 November 17, 2017
      Jordan Benzing
      Reply
      Author

      Jonathan I’ve released a new version that has Software Update Groups available as a drop down feature.

      https://www.scconfigmgr.com/2017/11/17/configmgr-patch-compliance-with-powerbi-v1-0-05/

  • Kevin Zachry
    Posted at 16:33 November 14, 2017
    Kevin Zachry
    Reply
    Author

    Just an FYI.

    Users who are not local admins of their machine will need to run PowerBI as an administrator when running this report to prevent this error:

    “An error happened while reading data from the provider: ‘Could not load file or assembly ‘System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a..”

    From https://community.powerbi.com/t5/Desktop/Unable-to-Connect-to-SQL-Server/td-p/202262

    • Jordan Benzing
      Posted at 16:50 November 14, 2017
      Jordan Benzing
      Reply
      Author

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

  • John
    Posted at 19:30 November 21, 2017
    John
    Reply
    Author

    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?

  • Leave a Reply