Case Study: The updates returned from the Windows update are less than what is returned from the Reports they are using in ConfigMgr.


Hi Folks, This post is about a case study that illustrates the troubleshooting steps taken to modify an existing custom ConfigMgr report to return the correct results when compared to Windows update when checked on Internet. I have included the details and the path taken to achieve the same.

 Issue:

The updates returned from the Windows update are less than what is returned from the Reports we are using in ConfigMgr.

When we run against the windows update we get only 2 updates marked as high importance. And when we run the report (Custom created) it returns as 11 updates required. As per them the report should return only 2 but its not.

Troubleshooting Steps:

From the initial thought it was sure that there was something definitely missing in the query. So have a look at the query:

 
 
SELECT CS.Name0, 
CS.UserName0, CASE WHEN (SUM(CASE WHEN 
UCS.status = 2 THEN 1 ELSE 0 END))
 
> 0 THEN ((CAST(SUM(CASE WHEN UCS.status = 2 THEN 1 ELSE 0 
END) AS varchar(10)))) ELSE 'Good Client' END 
AS 'Missing Patches',
 
ws.LastHWScan AS 'Last HW scan', v_R_System.AD_Site_Name0 as 'Location', 
v_GS_OPERATING_SYSTEM.Caption0 as 'OS'
FROM 
v_UpdateComplianceStatus AS UCS LEFT OUTER JOIN
 
v_GS_COMPUTER_SYSTEM AS CS ON CS.ResourceID 
= UCS.ResourceID INNER JOIN
 
v_CICategories_All AS catall2 ON catall2.CI_ID = UCS.CI_ID INNER JOIN
 
v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = 
catinfo2.CategoryInstance_UniqueID AND
 
catinfo2.CategoryTypeName = 'UpdateClassification' INNER JOIN
 
v_R_System ON UCS.ResourceID = 
v_R_System.ResourceID INNER JOIN
 
v_GS_OPERATING_SYSTEM ON UCS.ResourceID = 
v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER 
JOIN
 
v_GS_WORKSTATION_STATUS AS ws ON ws.ResourceID = CS.ResourceID LEFT OUTER JOIN
 
v_FullCollectionMembership AS FCM ON FCM.ResourceID = CS.ResourceID
WHERE (UCS.Status 
= '2') AND (FCM.CollectionID = @CollID)
GROUP BY CS.Name0, CS.UserName0, 
ws.LastHWScan, v_R_System.AD_Site_Name0, v_GS_OPERATING_SYSTEM.Caption0
 
ORDER BY 'Missing Patches', CS.Name0, CS.UserName0, 'Last HW scan'

So this query sums all the update for which the status compliance status is 2. The status 2 is for missing updates. It does not check in anywhere for any specific category updates. 

The next plan of steps was to modify the query to return the Update IDs also that are missing. By this we can check what all are the updates that are shown as missing and compare with the windows update results and check if it only shows the high importance updates or does it include the optional updates too.

Query modified added Unique ID:

 
SELECT CS.Name0, 
CS.UserName0, CASE WHEN (SUM(CASE WHEN 
UCS.status = 2 THEN 1 ELSE 0 END))
 
> 0 THEN ((CAST(SUM(CASE WHEN UCS.status = 2 THEN 1 ELSE 0 
END) AS varchar(10)))) ELSE 'Good Client' END 
AS 'Missing Patches',
 
ws.LastHWScan AS 'Last HW scan', v_R_System.AD_Site_Name0 as 'Location', 
v_GS_OPERATING_SYSTEM.Caption0 as 'OS', 
upd.CI_UniqueID as 'Update 
ID'
FROM 
v_UpdateComplianceStatus AS UCS LEFT OUTER JOIN
 
v_GS_COMPUTER_SYSTEM AS CS ON CS.ResourceID 
= UCS.ResourceID INNER JOIN
 
v_CICategories_All AS catall2 ON catall2.CI_ID = UCS.CI_ID INNER JOIN
 
v_UpdateCIs as upd on 
upd.CI_ID=ucs.CI_ID INNER JOIN
 
v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = 
catinfo2.CategoryInstance_UniqueID AND
 
catinfo2.CategoryTypeName = 'UpdateClassification' INNER JOIN
 
v_R_System ON UCS.ResourceID = 
v_R_System.ResourceID INNER JOIN
 
v_GS_OPERATING_SYSTEM ON UCS.ResourceID = 
v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER 
JOIN
 
v_GS_WORKSTATION_STATUS AS ws ON ws.ResourceID = CS.ResourceID LEFT OUTER JOIN
 
v_FullCollectionMembership AS FCM ON FCM.ResourceID = CS.ResourceID
WHERE (UCS.Status 
= '2') AND (FCM.CollectionID = 
'J*****C8') 
and cs.name0='JA***002'
GROUP BY CS.Name0, CS.UserName0, 
ws.LastHWScan, v_R_System.AD_Site_Name0, v_GS_OPERATING_SYSTEM.Caption0,upd.CI_UniqueID
 
ORDER BY 'Missing Patches', CS.Name0, CS.UserName0, 'Last HW scan'

Output (As expected returned 11 results):

 

When compared the same with the windows update results found that the 2 high importance updates were present here. They are one which start with Update ID bd7d.. and c352..

Also, checked the optional updates that were returned by the windows update. There were 7 Software optional and 2 hardware optional updates. Then checked their update ids and found the same in the above list.

From this it was clear that the query was not only returning the High importance updates but also the software and hardware optional updates.

But now as I was doing this, The need then modified to change the query to return only the High priority updates from the reports.

Here the challenge was that Windows update divided the update in 3 categories i.e.

1.       High Importance

2.       Software optional

3.       Hardware optional

 In the ConfigMgr, we have the categories:

1.       Critical updates

2.       Definition updates

3.       Security updates

4.       Service packs

5.       Updates

6.       Drivers

7.       Tools etc.

So the point to check was on what basis the updates are categorized as High importance by windows update and under category they fall in the ConfigMgr.

After some research, We found that the security updates are always put under high importance and the one we checked in the optional were belonging to the software optional.

The next thing was where does the information on the updates category reside?  It does not took us much time and we found out that CategoryInstanceName column from view v_CICategoryInfo

Will yield us the category information (i.e. critical, security). So now I just needed to add one condition in the query that will tell it to filter for only the security updates.

Modified the query:

 
 
SELECT CS.Name0, 
CS.UserName0, CASE WHEN (SUM(CASE WHEN 
UCS.status = 2 THEN 1 ELSE 0 END))
 
> 0 THEN ((CAST(SUM(CASE WHEN UCS.status = 2 THEN 1 ELSE 0 
END) AS varchar(10)))) ELSE 'Good Client' END 
AS 'Missing Patches',
 
ws.LastHWScan AS 'Last HW scan', v_R_System.AD_Site_Name0 as 'Location', 
v_GS_OPERATING_SYSTEM.Caption0 as 'OS'
FROM 
v_UpdateComplianceStatus AS UCS LEFT OUTER JOIN
 
v_GS_COMPUTER_SYSTEM AS CS ON CS.ResourceID 
= UCS.ResourceID INNER JOIN
 
v_CICategories_All AS catall2 ON catall2.CI_ID = UCS.CI_ID INNER JOIN
 
v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = 
catinfo2.CategoryInstance_UniqueID AND
 
catinfo2.CategoryTypeName = 'UpdateClassification' and 
catinfo2.CategoryInstanceName='Security 
updates'INNER 
JOIN
 
v_R_System ON UCS.ResourceID = 
v_R_System.ResourceID INNER JOIN
 
v_GS_OPERATING_SYSTEM ON UCS.ResourceID = 
v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER 
JOIN
 
v_GS_WORKSTATION_STATUS AS ws ON ws.ResourceID = CS.ResourceID LEFT OUTER JOIN
 
v_FullCollectionMembership AS FCM ON FCM.ResourceID = CS.ResourceID
WHERE (UCS.Status 
= '2') AND (FCM.CollectionID = @CollID)
GROUP BY CS.Name0, CS.UserName0, 
ws.LastHWScan, v_R_System.AD_Site_Name0, v_GS_OPERATING_SYSTEM.Caption0
 
ORDER BY 'Missing Patches', CS.Name0, CS.UserName0, 'Last HW scan'

Cloned the existing report with the above modified query. It worked for us and retuned only the two updates that we needed.

 Hope you liked it.

Umair Khan

Support Escalation Engineer | Microsoft System Center ConfigMgr 

 

Disclaimer:
This posting is provided "AS IS" with no warranties and confers no rights.

Comments (2)

  1. samson says:

    good article !

Skip to main content