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