WannaCry Patch Compliance Report

• 15 May 13:40 ,Updated the query to show Windows 10 (Build 15063) creators Update as Patched
• 15 May 14:10, Had made a mistake in the last version ,with the update status. fixed now. 
• 15 May 15:00 Expanded the list of updates.

• 16 May 10:00, This doesn't work as expected ,when a update is superseded the old one is set as "Not Requered". Got a new version almost ready that uses Baseline/CI with a WMI query against Win32_QuickFixEngineering, seems to work much better. Keep checking back, i'll post it soon. 
• 16 May 14:30 ,New version of the report that uses baseline available for download.
• 16 May 17:00 ,tweaked the baseline WMI query to support Builds higher than 15063
• 24 May ,Updated the Baseline ,added 3KB's for Windows 7 and fixed the buildnumber "check" (greater than 15053 statement should now work as expected)
• 29.June Added KB4022727  KB4022714 AND KB4022715 to the list (Windows 10 June Updates) Havent updated the CAB file ,just copy the powershell code bellow and update the CI.

I just got back from a one week vacation and the everybody is freaking out about the WannyCry ransomware.
I spend the whole morning working with people hanging over my shoulder watching me write SQL queries to get the information they where looking for.
The vulnerability that WannyCry uses was patched in Bulletin "MS17-010" ,but that update is expired and superseded by other updates that again are expired and superseded...and so on. This makes it hard to tell if computers are patched against WannaCry or not.

So! i created a report to show if machines in a collection are patched against WannaCry or not.
The way the report works is that I've created a list of Updates that contain the patch for the WannyCry vulnerability and if a machine have one of those patches installed it's shows as "Patched" if not it shows as "Not-Patched / Unknown".

The List of Updates that it checks against is easily changed. I've added the updates that i could find that fixes this issue, but this was a rush job so there may be errors in the list. If you find any errors or updates that are missing from the list ,post a comment bellow.

Created a new version of the report that uses Configuration Baselines to determine patch state ,it queries WMI on the machines(Win32_QuickFixEngineering) for one or more of the KB patches that fixes the problem.

New report is a bit more work since you have to deploy the Configuration Baseline (Included in the zip file), but the new report should give better results. Also slower since the baseline needs to be evaluated on the clients.

I've been testing it myself all morning and it seems to work as expected, let me know.
Old version is still available.

Download Report (SUP Version)
(If you still want to use this report ,check out this tip on Reddit. you can set updates not to expire immediately when superseded)

Download New Report (Baseline Version)
(New version have a version number in the footer)

Note! This is the first version of the report and it was created in a rush. If you find mistakes or errors post a comment bellow.
I'll update the report as soon as i can when we find errors or updates that should be added/removed from the the list.

List of update is based on this blog post and the "Microsoft WannaCrypt Customer Guidance Document":
https://www.askwoody.com/2017/how-to-make-sure-you-wont-get-hit-by-wannacrywannacrypt/ 

People don't seem to agree if Windows 10 versions other that Creators Update(1703) is immune or not. For now Windows 10 is part of the report.

Both versions of the report use the same logic to determine if a computer is patched or not.
The SUP version checks against the Software Update Views, but there is a issue with superseded updates if you don't increase the time between the update is superseded and the update expires.

The Baseline version checks for the updates in WMI on the clients and reports back compliance state that shown in the report. This is a better method ,but it's slower. You have deploy the Configuration Baseline and wait for the clients to report back compliance state.

To Deploy the Baseline Configuration 

This baseline only monitors ,it does not remediate anything ,should be safe to deploy.

1. Right Click Configuration Baselines (SCCM Console > Asset and Compliance Settings) and select "Import Configuration Data"
2. Click Add and select the CAB file included in the download (You get a warning that it's not signed ,click yes)
3. You should now have a WannaCry_Patched Baseline in the list ,Right Click it and select Deploy.
4. Choose a Collection and set the Schedule to 1 day or something faster. 

Can't import the baseline? Watch this video to learn how to create it manually.

Note! The Configuration Baseline should be called  "WannaCry_Patched",the rest don't matter
(You can call it something else ,but then you have to edit the report:-))

Here is the Powershell script for the baseline:

$OS = Get-WmiObject -Query "select * from Win32_OperatingSystem"

if ([convert]::ToInt32($os.BuildNumber) -cge 15063)

{

Return $true

}

else

{

$queryresult = Get-WmiObject -query "select * from Win32_QuickFixEngineering
where HotFixID = 'KB4015553' OR HotFixID = 'KB4019215' OR HotFixID = 'KB4015549'
OR HotFixID = 'KB4015552' OR HotFixID = 'KB4012598' OR HotFixID = 'KB4019264'
OR HotFixID = 'KB4012215' OR HotFixID = 'KB4012213' OR HotFixID = 'KB4012212'
OR HotFixID = 'KB4012217' OR HotFixID = 'KB4015551' OR HotFixID = 'KB4019216'
OR HotFixID = 'KB4012216' OR HotFixID = 'KB4015550' OR HotFixID = 'KB4013429'
OR HotFixID = 'KB4019472' OR HotFixID = 'KB4015217' OR HotFixID = 'KB4015438'
OR HotFixID = 'KB4016635' OR HotFixID = 'KB4019473' OR HotFixID = 'KB4015219'
OR HotFixID = 'KB4013198' OR HotFixID = 'KB4012606' OR HotFixID = 'KB4015221'
OR HotFixID = 'KB4019474' OR HotFixID = 'KB4012214' OR HotFixID = 'KB4019265'
OR HotFixID = 'KB4019263' OR HotFixID = 'KB4015546' OR HotFixID = 'KB4022727'
OR HotFixID = 'KB4022714' OR HotFixID = 'KB4022715'"

if ($queryresult)

{

Return $true

}

else

{

Return $false

}

}

Here are some SQL queries to show a Computers status for these updates:

DECLARE @computername VARCHAR(40)
SET @computername = 'Insert Computername here'

SELECT A.resourceid,
       E.name0,
       e.user_name0,
       D.title,
       D.articleid,
       D.daterevised,
       A.status,
       CASE
         WHEN A.status = '0' THEN 'Detection state unknown'
         WHEN A.status = '1' THEN 'Update is not required'
         WHEN A.status = '2' THEN 'Update is required'
         WHEN A.status = '3' THEN 'Update is installed'
         ELSE ''
       END AS StatusName,
       F.statename
FROM   v_update_compliancestatusall A
       INNER JOIN v_updateinfo D
               ON A.ci_id = D.ci_id
       INNER JOIN v_r_system E
               ON A.resourceid = E.resourceid
       LEFT JOIN v_statenames F
              ON A.lastenforcementmessageid = F.stateid
                 AND F.topictype = 402
WHERE
  E.name0 = @computername
       AND D.articleid IN ( '4015553','4019215','4015549','4015552','4012598','4019264','4012215','4012213','4012212','4012217','4015551','4019216','4012216',
'4015550','4013429','4019472','4015217','4015438','4016635','4019473','4015219','4013198','4012606','4015221','4019474','4012214','4019265','4019263','4015546','4022727','4022714','4022715') 
ORDER  BY resourceid,
          D.daterevised DESC,
          F.statename ASC 


Or this one to show all Clients in a collection that are in the process of installing one or more of these updates:

DECLARE @Collection VARCHAR(40)
SET @Collection = 'Insert CollectionID Here'

SELECT A.resourceid,
       E.name0,
       e.user_name0,
       D.title,
       D.articleid,
       D.daterevised,
       A.status,
       CASE
         WHEN A.status = '0' THEN 'Detection state unknown'
         WHEN A.status = '1' THEN 'Update is not required'
         WHEN A.status = '2' THEN 'Update is required'
         WHEN A.status = '3' THEN 'Update is installed'
         ELSE ''
       END AS StatusName,
       F.statename
FROM   v_update_compliancestatusall A
       INNER JOIN v_fullcollectionmembership B
               ON A.resourceid = B.resourceid
       INNER JOIN v_collection C
               ON B.collectionid = C.collectionid
       INNER JOIN v_updateinfo D
               ON A.ci_id = D.ci_id
       INNER JOIN v_r_system E
               ON A.resourceid = E.resourceid
       LEFT JOIN v_statenames F
              ON A.lastenforcementmessageid = F.stateid
                 AND F.topictype = 402
WHERE  C.collectionid = @Collection
       AND A.status != 3
       AND F.statename IS NOT NULL  
       AND D.articleid IN ( '4015553','4019215','4015549','4015552','4012598','4019264','4012215','4012213','4012212','4012217','4015551','4019216','4012216',
'4015550','4013429','4019472','4015217','4015438','4016635','4019473','4015219','4013198','4012606','4015221','4019474','4012214','4019265','4019263','4015546','4022727','4022714','4022715') 
ORDER  BY resourceid,
          D.daterevised DESC,
          F.statename ASC