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
Declare @ArticleID as int
Set @ArticleID = 4019111
Declare @CollectionID as varchar(8)
Set @CollectionID = 'SMS00001'
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,
u.Full_User_Name0 as PrimaryUserName, u.Mail0 as PrimaryUserEmail,
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)
cir.toCIID as UpdateID, ui.CI_ID as GroupID, ui2.BulletinID, ui2.ArticleID, ui2.Title, ui2.DateRevised,
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))))
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
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
ucs.CI_ID, ucs.Status, ucs.LastEnforcementMessageID,
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 (
ROW_NUMBER() OVER (PARTITION BY Name ORDER By Name, SortTrick ASC) as ROW
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
) finaldata where Row = 1
drop table #ClientData
drop table #ComplianceInfo
drop table #DeploymentTargets
drop table #DeploymentInfo
drop table #UpdateInfo
About the author
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.
Model Technology Solutions is a small but mighty band of infrastructure experts. We’ve helped companies in diverse industries to modernize and automate their infrastructures through effectively managing their Microsoft endpoint suite.
With us on your team, you’ll watch your security and compliance go up and your IT team’s costs (and headaches) go down. You’ll relax in knowing that your endpoints will be secure and online when your users need them most. And you’ll finally get back to your most-important tasks.