Almost two weeks ago I released a pretty generic report on reporting patch compliance using PowerBI and System Center Configuration Manager and I was shocked at the amount of feedback I got from the community! So first I want to say thank you and then I want to let you all know that I’ve got a new and improved version of the report!

If you just want to go download the version of the report and start playing with it you can find it on TechNet Gallery: Patch Compliance With PowerBI – V1.0.0.7

Major Updates:

  • Organization Overview Tab – Consolidated view of patch compliance for your organization – Shows Server Patching, Desktop Patching and, ConfigMgr client penetration.
  • Workstation Patch Compliance – Previously only had a single tab for Server Patch Compliance (I’m primarily a server admin forgive me).
  • Server Client Penetration – A very rough spin into finding clients that don’t have the agent installed filtered by has the device authenticated to AD within X number of days.
    • Includes count of client versions in the environment
  • Workstation Client Penetration – A very rough spin into finding clients that don’t have the agent installed filtered by if the device has authenticated to AD within X number of Days
    • Includes count of client versions in the environment
  • Workstation/Server Tab now includes a drop down to filter Articles by membership in a specific Software Update Group.

Minor Updates:

  • Color Theme Change – Based on some conversation with members of the community realized the default colors might be hard to see so the report has been skinned using a custom theme that should hopefully be more color-blind friendly.
  • SCConfigMgr Logo – Added the website logo to it, you can of course replace it with your own.
  • Additional SQL Queries – In order to support some of the changes in the Major Updates section minor SQL query changes were made, and some new queries were added, more on that later.
  • Graphics Changes/improvements

Possible Future Updates:

  • Clients Last WSUS Scan time
  • Last WSUS Scan Server Location
  • Last WSUS Scan Status Code – (And maybe a suggestion what’s broken if something is broken)
  • Possibly including devices where path status is ‘unknown’
    • Currently, this is not done as it pulls in a very large amount of data as it requires usage of V_Update_ComplianceStatusAll which is a much larger dataset.
  • Additional Generic Client Health Maybe?

In order to facilitate a large number of these changes, I had to modify some existing SQL and add an additional query or two. I strongly encourage you to run the queries that power the report on their own in SQL Server Management Studio to assess the impact on your environment especially the query that collects patch compliance information.

SQL Queries Leveraged

Update Compliance Information

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' 
       , CASE v_GS_OPERATING_SYSTEM.ProductType0 
            WHEN 1 THEN 'WORKSTATION' 
            WHEN 2 THEN 'DOMAIN CONTROLLER' 
            WHEN 3 THEN 'SERVER' 
        END AS 'OSCLASS' 
       , 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'

Client Info Query

select V_R_System.Name0 
    , CASE V_R_System.Client0 
              WHEN 1 THEN 'INSTALLED' 
              ELSE 'NOT INSTALLED' 
          END AS CLIENTSTATUS 
    , V_R_System.Client_Version0 
        , V_R_System.Operating_System_Name_and0 
        , v_R_System.Last_Logon_Timestamp0 
        , v_GS_OPERATING_SYSTEM.Caption0 as 'OSIfClientInstalled' 
from v_R_System 
Left OUTER Join V_GS_Operating_System on V_R_System.ResourceID = V_GS_Operating_System.REsourceID

Software Update Group Members

with SUGInfo as 
( 
    Select V_UpdateInfo.CI_ID 
        , v_UpdateInfo.CIType_ID 
        , V_UpdateInfo.Title 
    from v_UpdateInfo 
    where v_UpdateInfo.CIType_ID = '9' 
) 
Select SUGInfo.Title as 'SoftwareUpdateGroup' 
    , v_CIRelation.FromCIID 
    , v_CIRelation.ToCIID 
    , V_UpdateInfo.ArticleID 
    , V_UpdateInfo.Title     
from SUGInfo  
Left Outer Join v_CIRelation on SUGInfo.CI_ID = v_CIRelation.FromCIID  
Left outer Join v_UpdateInfo on v_CIRelation.ToCIID = V_UpdateInfo.CI_ID

Update Information 

Select V_UpdateInfo.ArticleID 
     , V_UpdateInfo.Title 
     , V_UpdateInfo.InfoURl 
from V_UpdateInfo 
Where V_UpdateInfo.ArticleID is NOT Null and V_UpdateInfo.ArticleID !=''

Just as a quick review when you download the template and open it you’ll be greeted by the same prompts as before here is a quick video example of how to change the datasets. I also want to give a big shout out to Maurice Daly for all of his edits and help in making this look more streamlined.

 

Now, you might be prompted by Native Database Query Security and you’ll be asked for approval to run each query individually. This is a security feature of the product and CAN be disabled. I’ll show you how.

If you want to disable the above annoying query approval from popping up every time you put a new query into PowerBI – I for one do disable it because the only person who puts queries in there is me, and only after I’ve tested them in SSMS – you can do so by doing the following. File->Options and Settings -> Options -> Security

In the security tab of the Options popup un-check the ‘Native Database Queries’ option that requires approval and hit OK. Please, only do this if you know what you’re doing and you are absolutely sure queries that go in PowerBI have been tested.

 

If you have any questions about what the different rings mean and the criteria I’ve used please feel free to ask questions.

 

 

(1634)

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
  • Chris
    Posted at 02:19 November 21, 2017
    Chris
    Reply
    Author

    Thank you for this report. Just one question – What’s the difference between non-complaint and atriskdevices? Just implemented SUP and deployed ADR to a few groups (few hundred machines). My at risk devices are almost triple my non compliant devices?
    Compliant-200
    Non-Compliant- 100
    At Risk- 290

    • Jordan Benzing
      Posted at 03:43 November 21, 2017
      Jordan Benzing
      Reply
      Author

      The ‘At Risk’ column is built on a couple of pieces of information and depends on how you are filtering the report. The idea is to give an overall ‘risk footprint’ idea based on all patches released within the last 365 days that you have deployed. The ‘At Risk’ measure is a cumulative count of all devices – server and desktop – that are either non-compliant for patches OR are missing the SCCM agent. If you are looking to compare compliance level against a specific SUG/ADR I suggest using the Server and Desktop tabs and evaluating them individually as the dashboard tab is designed as a true ‘overview’ of an environment. If you think there is an issue with one of the measures let me know and I’ll double check the DAX expressions.

      • Chris
        Posted at 08:04 November 21, 2017
        Chris
        Reply
        Author

        Ahh , thanks for the response. Was able to get the correct numbers with the correct filter added

  • Henrik Hoe Nielsen
    Posted at 13:23 November 21, 2017
    Henrik Hoe Nielsen
    Reply
    Author

    Hi Jordan,

    Been looking at the queries and i seem to be at a loss when it comes to a minor change ive made… I simply added IsDeployed from the v_UpdateInfo view – problem is that i see updates that i know are deployed, register with a “0” in IsDeployed. So I’m wondering whether the data is “correct” when looking at views used. Perhaps if you can confirm whether my finding is correct or faulty?

    /H

    • Jordan Benzing
      Posted at 16:28 November 21, 2017
      Jordan Benzing
      Reply
      Author

      Hello Henrik!

      Sounds like what you’ve done is just added a column to the query then removed the where filter on ‘IsDeployed’ and are seeing that some of the updates that are set to 0 are actually deployed in the environment? That shouldn’t be the case. I would start by double checking the data in your console. Find the article ID of a specfic update you think is deployed and check the console to confirm under ‘Software Lbrary -> Software Updates -> All Software Updates’

      Cheers,

      -J

      • Henrik Hoe Nielsen
        Posted at 08:06 November 22, 2017
        Henrik Hoe Nielsen
        Reply
        Author

        I can confirm that this is exactly the case, the specific update is registered as IsDeployed in the Console but not in the DB view.

  • Cyndy
    Posted at 18:54 November 22, 2017
    Cyndy
    Reply
    Author

    Nice report, I do not seem to see any of the 2016 servers in the server patch compliance tab. The 2088 and 2012 work fine.

    • Jordan Benzing
      Posted at 19:10 November 22, 2017
      Jordan Benzing
      Reply
      Author

      Take a look at filters should see them in there might be something that is causing them to get pulled out but they should show up in the default way.

      Don’t worry about the double post I cleaned em up 😀

      • Cyndy
        Posted at 21:11 November 22, 2017
        Cyndy
        Reply
        Author

        I have opened up everything. I have 20 servers 10 of which are server 2016. I know they all need 11/14/2017 patches. I only see 10 compliant and nothing under non compliant. I selected all domains, all Software update groups, patch status select all, Only 10 servers show up as non compliant non are my 2016 servers. Am I missing something? I can see in other reports that they are non compliant.

  • kimmiez
    Posted at 18:17 November 23, 2017
    kimmiez
    Reply
    Author

    Is there any way to get information regarding when a patch was actually installed on the machine? i’ve been looking in the SQL view documentation but can’t seem to get around it.

    that’s the last piece of my puzzle i’m missing :p

    • Jordan Benzing
      Posted at 18:48 November 23, 2017
      Jordan Benzing
      Reply
      Author

      So… ‘Kinda’ there are two answers to this question.
      1) If you are extremely trusting of WSUS you can use the V_UpdateCompliance.LastStatusChangeTime field and that ‘in theory’ should tell you the last time the compliance changed and assuming that last change was from non-compliant to compliant. You’re all set.

      2) Implement the QFE hardware inventory information/collection and link that. This also comes with its own caveats of – not all updates are stored in quick fix engineering, and your now dependent on hardware inventory AND compliance data linking up together.

      Two methods, just depends on which way you want to go.

      Hope that helps.

      -J

      • kimmiez
        Posted at 19:39 November 23, 2017
        kimmiez
        Reply
        Author

        Hey Jordan,

        So i was actually looking at the right field with the laststatuschangetime.
        I was testing it before by adding it to your query but i was getting dates for patches that are still missing. So I got confused about it.

        But basically it means that when a patch is missing that field is filled in with the date/time of the last compliance scan. Meaning it will update the moment I install the patch?

        Tomorrow i’m going to check with the Quick fix engineering hw inventory, did not know about that, so thanks for that!

        just curious what option you would pick ? :p

        again many thanks for the awsome dashboard !!
        following closely on twitter in case you make updates on it 😉

        • Jordan Benzing
          Posted at 20:02 November 23, 2017
          Jordan Benzing
          Reply
          Author

          “But basically it means that when a patch is missing that field is filled in with the date/time of the last compliance scan. Meaning it will update the moment I install the patch?”

          It won’t update the EXACT moment you install it but after it installs, and then the NEXT compliance scan. Now if you have configured your Software Update Deploymetns to Scan after install and reboot… thats pretty close.

          Quick Fix Engineering with HW inventory is pretty cool, I think we have a post on it somewhere… I’ll look and if we don’t I’ll make one!

          I use both fields as sometimes the data I want is in one sometimes the other just depends on what I’m looking for.

          Thanks for the follow on twitter! I’m planning on some new updates coming in the next week or two!

          • kimmiez
            Posted at 21:31 November 23, 2017
            kimmiez
            Author

            Yeah i’ve got mine configured like that, so that should be reliable data!

            ooh, I think i’ve missed that post if there was any , Adding that one to my list to check tomorrow ! going to test it tomorrow and see if i can alter your query to get that data in it.

            Really, many thanks for your kind reply & awsome Dashboard !
            a few weeks ago i was really lost in getting some good compliance reports, this dashboard is really helping me reporting wise for SOx !

            Cheers Jordan !

  • Rich
    Posted at 18:19 December 1, 2017
    Rich
    Reply
    Author

    Very informative patch status dashboard! nice work! one suggestion would be to add some compliance settings CIs to report back awaiting reboot status on patches and incorporate that data into the dashboard.

  • Leave a Reply