Collections to check the status of the WSUS Client State
One of the questions i get a lot is:
“What is the status of our WSUS patch deployments?”
So i have a series of collections that tell me where things site. I’m not sure where i got the first 3 collections.
So thank you to whom ever posted them originally. The 4th one was created by Robert Jones, one of the admin that work on the sites that are connected to our central site.
Anyway here are the collections:
ABC - WSUS Client State –> place holder collection
-->ABC - Clients Pending reboot
-->ABC - Failed to install update
-->ABC - Machines that failed to scan
-->ABC - Waiting for another installation to complete
(note ABC stands for site code here)
Collection Queries
ABC - Clients Pending reboot
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from sms_r_system AS sms_r_system inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid where c.LastEnforcementMessageID = 9
ABC - Failed to install update
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from sms_r_system inner join SMS_UpdateComplianceStatus on SMS_UpdateComplianceStatus.machineid=sms_r_system.resourceid where SMS_UpdateComplianceStatus.LastEnforcementMessageID = 11
ABC - Machines that failed to scan
This collection is based on http://www.myitforum.com/articles/1/view.asp?id=5578
Create this collection and add a query but don't put anything in the query.
Then open the SQL Server Management Studio and connect to the SCCM DB
Then open the views and find the view that matches the collection number that you just created.
It should look something like this dbo._res_coll_ABC00123
Right click the view and choose edit.
Then paste this statement into the query, make sure that you change the collection ID to match your Collection ID
Then execute it. This will update the collection query.
USE [SMS_PA1]
GO
/****** Object: View [dbo].[_RES_COLL_ABC00123] Script Date: 06/18/2009 13:55:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER view [dbo].[_RES_COLL_PA100165] as
SELECT cm.MachineID
, cm.ArchitectureKey
, cm.Name
, cm.SMSID
, cm.SiteCode
, cm.Domain
, cm.ClientType
, cm.IsDirect
, cm.IsAssigned
, cm.IsClient
, cm.IsObsolete
, cm.IsActive
, cm.IsDecommissioned
, cm.AMTStatus
, cm.AMTFullVersion
, cm.SuppressAutoProvision
, ck.ApprovalStatus AS IsApproved
, ck.IsRevoked AS IsBlocked
, ck.AlwaysInternet AS IsAlwaysInternet
, ck.InternetEnabled AS IsInternetEnabled
FROM dbo.CollectionMembers AS cm INNER JOIN
dbo.Update_ScanStatus ON cm.MachineID = dbo.Update_ScanStatus.MachineID LEFT OUTER JOIN
dbo.ClientKeyData AS ck ON cm.SMSID = ck.SMSID
WHERE (cm.SiteID = 'ABC00123') AND (dbo.Update_ScanStatus.LastErrorCode <> 0)
ABC - Waiting for another installation to complete
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from sms_r_system AS sms_r_system inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid where c.LastEnforcementMessageID = 3
See next Post for how to fix Machines that failed to scan.
http://myitforum.com/cs2/blogs/cstauffer/archive/2009/06/18/fixing-broken-wsus-clients.aspx