Configmgr Comparison of Update Views

Inspired by this post on TechNet and trying to help out someone on the WinAdmins Discord, I took a bit of a dive into the v_Update_ComplianceStatus view and how it compares to v_Update_ComplianceStatusAll.

The v_Update_ComplianceStatus & v_UpdateComplianceStatus Views

I cover these views together because they are the same. The v_Update_ComplianceStatus view just pulls all records from v_UpdateComplianceStatus. I haven't looked into why, but I'd imagine one follows a newer name format but Microsoft didn't want to break old reports.

This view runs the following query:

 1select CI_ID
 2     , ResourceID=MachineID
 3	 , Status
 4	 , LastStatusCheckTime
 5	 , LastStatusChangeTime
 6	 , EnforcementSource
 7	 , LastEnforcementMessageID
 8	 , LastEnforcementMessageTime
 9	 , LastEnforcementStatusMsgID
10	 , LastErrorCode
11	 , LastLocalChangeTime 
12  from Update_ComplianceStatus cs 
13 where Status > 1 

The where clause, by including only records where status > 1, excludes unknown systems (Status = 0) as well as when the update isn't required (Status = 1). If it's important to know when the status of the deployment is unknown, you'll need to avoid this view.

The v_Update_ComplianceStatusAll View

This view is the most expensive of all of the status views because it does what's known as a CROSS JOIN between the updates view and the clients view. Here is what that looks like:

 1select ci.CI_ID, cm.ResourceID 
 2	 , Status=ISNULL(cs.Status, case when ss.ScanPackageVersion>=ci.MinSourceVersion then 1 else 0 end) 
 3	 , LastStatusCheckTime=ISNULL(cs.LastStatusCheckTime, ss.ScanTime) 
 4	 , cs.LastStatusChangeTime
 5	 , cs.EnforcementSource
 6	 , cs.LastEnforcementMessageID
 7	 , cs.LastEnforcementMessageTime
 8	 , cs.LastEnforcementStatusMsgID
 9	 , cs.LastErrorCode 
10	 , LastLocalChangeTime=ISNULL(cs.LastLocalChangeTime, ss.LastLocalChangeTime) 
11  from v_UpdateCIs ci  
12 cross join v_ClientMachines cm 
13  left join Update_ComplianceStatus cs on cs.CI_ID=ci.CI_ID and cs.MachineID=cm.ResourceID and (cs.Status>0 or ci.CIType_ID=9) 
14  left join Update_ScanStatus ss on ss.MachineID=cm.ResourceID and ss.UpdateSource_ID=ci.UpdateSource_ID 

What the CROSS JOIN does is take every record from v_UpdateCIs (which is every update, which is more than 35,000 records in my environment) and maps it to a row in v_ClientMachines. If you have 10,000 clients in your environment, this is returning 350 million records (35,000 x 10,000). This will run very, very slowly.