[vc_row][vc_column][vc_column_text]
Due to the emergence of the WannaCrypt ransomware last week, SCCM admins worldwide were tasked to discover how many of their organization’s devices were at risk for becoming infected. Unfortunately, many organizations were learning lessons about patching, staying up to date with security, and prevention the hard way.
Most had gone through this drill before, but this time there was a new wrinkle. Late last year, Microsoft announced that they would change the way they released and handled supersedence for security updates. In March 2017, this new model was officially cut-over and replaced the way that things had been done for years. This was also the month that Microsoft released updates that protected against the vulnerabilities used by WannaCrypt to infect new systems.
Due to the newness of the update process, determining if a system was properly patched for this specific vulnerability became more complicated than before.
In this blog post, I am going to provide you with two resources. First, I’ll explain the new update model. Second, I’ll share a SQL query that provides detailed per-system status of the update process via SCCM against a specific Microsoft Update.
Using these two tools, you should be able to answer the questions you are dealing with currently, as well as in the future.
Making Sense of the New Update Model
In the past, all updates followed the same rules for supersedence and application. This is no longer the case.
The new model is really a few different models, depending on what Operating System you are dealing with.
The “Simple Method”
Windows 10, Windows Server 2016 and future operating systems use what I like to call the “Simple Method”.
Whenever Microsoft releases new security updates, they come as part of a cumulative update package that includes all security updates that apply to the operating system since it’s initial release. As such, you only need to have the latest installed to be current. These usually come monthly, but could come more frequently.
These updates are named in a specific way, for example:
YYYY-MM Cumulative Update for Windows xx Version xxxx for xxx-based Systems (KBxxxxxx)
– or –
YYYY-MM Cumulative Update for Windows Server xxxx for xxx-based Systems (KBxxxxxx)
Note: The “YYYY-MM” prefix was added starting in May 2017.
The “Hybrid Method”
Windows 7, Windows 8.1, Windows Server 2008 R2, Windows Server 2012 and Windows Server 2012 R2 use what I like to call the “Hybrid Method.” Just like the “Simple Method,” whenever Microsoft releases new security updates, they come as part of a cumulative update package that includes all security updates that apply to the operating system since it’s initial release. However, Microsoft will also release what they call a “Security Only Quality Update.” This is essentially just like the old method where the update contains only the new fixes, but not any of the old ones. Both of these also usually come monthly, but could come more frequently.
These are named in a specific way, for example:
Month, Year Security Only Quality Update for Windows xx for xxx-based Systems (KBxxxxxx)
– or –
Month, Year Security Only Quality Update for Windows Server xxxx for xxx-based Systems (KBxxxxxx)
– or –
Month, Year Security Only Quality Rollup for Windows xx for xxx-based Systems (KBxxxxxx)
– or –
Month, Year Security Only Quality Rollup for Windows Server xxxx for xxx-based Systems (KBxxxxxx)
Note: As of May 2017, the “Month, Year” prefix was replaced with the same “YYYY-MM” prefix used by the “Simple Method.”
The “Old Method”
Any updates released prior to October 2016, or new updates for older Operating Systems use the “Old Method.”
In this method, each update contains specific fixes and may or may not supersede older updates.
Most of these are named as follows:
Security Update for Windows xx (KBxxxxxxx)
– or –
Security Update for Windows Server xxxx (KBxxxxxx)
– or –
Security Update for Windows xx for xxx-based Systems (KBxxxxxxx)
– or –
Security Update for Windows Server xxxx for xxx-based Systems (KBxxxxxx)
What Update Do I Need to Patch WannaCrypt?
The fix for the WannaCrypt vulnerability was released as part of the March 2017 monthly patch cycle.
Windows 10 and Server 2016 need to have:
– the March 2017 Cumulative Update or a later one (currently April or May)
Windows Windows 7, Windows 8.1, Windows Server 2008 R2, Windows Server 2012 and Windows Server 2012 R2 need either:
– the March 2017 Cumulative Update or a later one (currently April or May)
– OR –
– the March 2017 Security Only Quality Update
– OR –
– the March 2017 Security Only Quality Rollup
Details for the March 2017 Updates can be found at https://technet.microsoft.com/en-us/library/security/ms17-010.aspx
Microsoft issued special updates for Windows XP, Windows 8, and Windows Server 2003. You can locate these updates if needed at https://blogs.technet.microsoft.com/msrc/2017/05/12/customer-guidance-for-wannacrypt-attacks/
How Do I Find Out If My Devices Are Patched?
At the end of this blog post, I have provided the code for a sql query that can be run against your SCCM Site Database. It will return the following information:
Client Device Details
ResourceID: The SCCM ResourceID of the device
Name: The Machine Name of the device
ADSite: The Active Directory Site of the Device when it was last discovered.
Active: This will show “Active” or “Inactive” based on the time periods you have configured within SCCM to determine client active status.
Client: “Client” if it is an SCCM Client, “NonClient” if it is not.
ClientVersion: The version of the SCCM Client (if installed).
Decomissioned: “Yes” means the device has been deleted from a child primary site, but has not yet been purged from your CAS. (only if you are running the query from your CAS).
OU: The AD OU the device resides in.
Type: “Physical” or “Virtual” based on the detection added in SCCM 2012 R2.
Obsolete: “Yes” means that this is a duplicate client record that SCCM will eventually delete.
OS: The Operating System of the device.
SP: The Service Pack of the device.
PrimaryUserID: The userid of the user that SCCM has identified as the “Primary User”
PrimaryUserName: The username of the user that SCCM has identified as the “Primary User”
PrimaryUserEmail: The Email of the user that SCCM has identified as the “Primary User”
ComplianceStatus
Shows the current status of the device for the update that matches the ArticleID you queried against.
It will show only one of the following possible values:
Installed – Pending Reboot: Update is installed but client reboot is needed.
Installed: Installed, no reboot needed.
Not Required: The update is Not Applicable/Not Needed for this device.
Unknown – Non SCCM Client: The device has no compliance status and isn’t an SCCM client.
Unknown – Old Scan Data: The last Software Update Scan of the device used old WSUS metadata that did not contain information about this update.
Unknown – Offline: This device has not been active in SCCM since the update was released by Microsoft.
Unknown – Scan Error: The last Software Update Scan of this device failed.
Unknown: Unknown for undetermined reason.
Required – Waiting for maintenance window: The device is waiting for a maintenance window before applying the update.
Required – Failed to download updates: The devices experienced an error when downloading the files for this update.
Required – Waiting for content: The client cannot find content on a DP and is waiting until it is available.
Required – Offline Since Deployment: This device has not been online since the deployment of this update started.
Required – Not Targeted by Deployment: This device needs the update, but is not part of a deployment for the update.
Required – Error ##########: This client encountered an error during install. The error code is provided as part of the status.
Required – Waiting for Enforcement Date: The client is targeted by a deployment for the update that has not yet reached it’s enforcement date.
Required – Reboot Pending: A previous installation required a reboot that must be completed before the update can be installed.
Required – In Progress: Installation is in progress, currently no issue.
Required: Required with a status not listed above.
To use the query below, replace 4019111 with the ID Number of the Article you want to query clients against.
Please contact us and let us know if you encounter an issue with this query, or if this article/query was of benefit to you. Also, take note of The Three Biggest Lessons to be Learned From WannaCrypt, and head out to YouTube to check out the recording of our webinar, How Windows 10 Drives Business Value, to learn more about how Windows 10 drives business value through security and other means.
Here is the query code:
Declare @ArticleID as int Set @ArticleID = 4019111 Declare @CollectionID as varchar(8) Set @CollectionID = 'SMS00001' --Client Info Select s.ResourceID, s.Name0 as Name, s.AD_Site_Name0 as AdSite, (Case when s. Active0 = 1 then 'Active' else 'Inactive' end) as Active, (Case when s.Client0 = 1 then 'Client' else 'NonClient' end) as Client, s.Client_Version0 as ClientVersion, (Case when s.Decommissioned0 = 1 then 'Yes' else 'No' end) as Decomissioned, (Replace(s.Distinguished_Name0,'CN=' + s.Name0 + ',','')) as OU, (Case when s.Is_Virtual_Machine0 = 1 then 'Virtual' else 'Physical' end) as Type, (Case when s.Obsolete0 = 1 then 'Yes' else 'No' end) as Obsolete, os.Caption0 as OS, os.CSDVersion0 as SP, scu.TopConsoleUser0 as PrimaryUserID, --v_R_User fields u.Full_User_Name0 as PrimaryUserName, u.Mail0 as PrimaryUserEmail, uss.LastScanPackageVersion, uss.LastErrorCode, cs.LastActiveTime into #ClientData from v_R_System s left join v_CH_ClientSummary cs on s.ResourceID = cs.ResourceID left join v_UpdateScanStatus uss on s.ResourceID = uss.ResourceID Left join v_GS_OPERATING_SYSTEM os on s.ResourceID = os.ResourceID left join v_GS_SYSTEM_CONSOLE_USAGE scu on s.ResourceID = scu.ResourceID left join v_R_User u on UPPER(scu.TopConsoleUser0) = UPPER(u.Unique_User_Name0) where s.ResourceID in (Select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID) --Update Info Select cir.toCIID as UpdateID, ui.CI_ID as GroupID, ui2.BulletinID, ui2.ArticleID, ui2.Title, ui2.DateRevised, uc.MinSourceVersion into #UpdateInfo from v_UpdateInfo ui left join v_CIRelation cir on ui.CI_ID = cir.FromCIID left join v_UpdateInfo ui2 on cir.ToCIID = ui2.CI_ID left join v_UpdateCIs uc on ui2.CI_ID = uc.CI_ID where ui.IsUserDefined = 1 and ui.IsBundle = 1 and cir.toCIID in (Select CI_ID from v_UpdateInfo where ArticleID = (Cast(@ArticleID as varchar(max)))) --Deployment Info Select uga.AssignmentID, uga.AssignedUpdateGroup, ds.CollectionID, ds.DeploymentTime, ds.EnforcementDeadline, (Case when DateDiff(MINUTE,GetDATE(),ds.EnforcementDeadline) < 0 then 'Enforced' else 'Pending Enforcement' end) as EnforcementStatus into #DeploymentInfo from vSMS_UpdateGroupAssignment uga left join v_DeploymentSummary ds on uga.AssignmentID = ds.AssignmentID where uga.AssignedUpdateGroup in (Select GroupID from #UpdateInfo) --Collection Member for Deployments Select distinct fcm.ResourceID into #DeploymentTargets from v_FullCollectionMembership fcm inner join #DeploymentInfo di on fcm.CollectionID = di.CollectionID --Compliance Info Select cd.ResourceID, dspa.AssignmentID, dspa.StatusTopicType, dspa.StatusStateID, ucs.CI_ID, ucs.Status, ucs.LastEnforcementMessageID, dspa.StatusErrorCode, dspa.StatusType into #ComplianceInfo from v_Update_ComplianceStatusAll ucs inner join #UpdateInfo ui on ucs.CI_ID = ui.UpdateID right join #ClientData cd on ucs.ResourceID = cd.ResourceID left join vSMS_SUMDeploymentStatusPerAsset dspa on ucs.ResourceID = dspa.ResourceID and dspa.AssignmentID in (Select AssignmentID from #DeploymentInfo) Select ResourceID, Name, ADSite, Active, Client, ClientVersion, Decomissioned, OU, Type, Obsolete, OS, SP, PrimaryUserID, PrimaryUserName, PrimaryUserEmail, ComplStatus as ComplianceStatus from ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER By Name, SortTrick ASC) as ROW FROM ( Select cd.*, (Case ------------------------------------------------------- when ci.Status = 3 and ci.LastEnforcementMessageID = 9 then 'Installed - Pending Reboot' when ci.Status = 3 then 'Installed' ----------------------------------------------------- when ci.Status = 1 then 'Not Required' ---------------------------------------------------- when ci.Status = 0 and cd.Client <> 'Client' then 'Unknown - Non SCCM Client' when ci.Status = 0 and (cd.LastScanPackageVersion < ui.MinSourceVersion) then 'Unknown - Old Scan Data' when ci.status = 0 and (DateDiff(hour, ui.DateRevised,cd.LastActiveTime)) < 0 then 'Unknown - Offline' when ci.Status = 0 and cd.LastErrorCode <> 0 then 'Unknown - Scan Error' when ci.status = 0 then 'Unknown' ---------------------------------------------------- when ci.Status = 2 and ci.StatusTopicType = 301 and ci.StatusStateID = 10 then 'Required - Waiting for maintenance window' when ci.Status = 2 and ci.StatusTopicType = 402 and ci.StatusStateID = 4 then 'Required - Waiting for maintenance window' when ci.Status = 2 and ci.StatusTopicType = 301 and ci.StatusStateID = 9 then 'Required - Failed to download updates' when ci.Status = 2 and ci.StatusTopicType = 402 and ci.StatusStateID = 2 then 'Required - Waiting for content' when ci.status = 2 and (DateDiff(hour, di.DeploymentTime,cd.LastActiveTime)) < 0 then 'Required - Offline Since Deployment' when ci.Status = 2 and di.EnforcementStatus is null then 'Required - Not Targeted by Deployment' when ci.Status = 2 and ci.StatusType = 5 then 'Required - Error: ' + Cast(ci.StatusErrorCode as varchar(max)) when ci.Status = 2 and di.EnforcementStatus = 'Pending Enforcement' then 'Required - Waiting for Enforcement Date' when ci.Status = 2 and ci.ResourceID is not null then 'Required - Reboot Pending' when ci.Status = 2 and ci.StatusType = 2 then 'Required - In Progress' when ci.Status = 2 then 'Required' ---------------------------------------------------- else 'Unknown' end) as ComplStatus, (Case when ci.STatus = 1 then 99 else 1 end) as SortTrick from #ClientData cd left join #ComplianceInfo ci on ci.ResourceID = cd.ResourceID left join #DeploymentInfo di on ci.AssignmentID = di.AssignmentID left join #UpdateInfo ui on ci.CI_ID = ui.UpdateID ) source ) finaldata where Row = 1 drop table #ClientData drop table #ComplianceInfo drop table #DeploymentTargets drop table #DeploymentInfo drop table #UpdateInfo
[/vc_column_text][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]About the author
Todd Linke
Senior Consultant, Model Technology Solutions
Todd became a professional “IT guy” 19 years ago, when he realized that playing with computers could become a real job. Since then he has gained a deep understanding of the inner workings of SCCM, PowerShell, SQL Reporting, Software Deployment, and is intimately familiar with the SCCM database. He is a firm believer in process automation combined with data warehousing. In short: he has become a data geek.[/vc_column_text][/vc_column][/vc_row]
