ConfigMgr SQL queries for helping the IT Pro report on KBs related to MS17-010


Update 05/15/2017: Initial post.

Update 05/17/2017: KB2919355 Queries were updated to address incorrect results.

Update 05/22/2017: Baseline Script updated to determine compliance based on versions of SRV.sys file.

Update 06/13/2017: Updated recommendations after June Updates are released.

Official Customer Guidance for WannaCrypt attacks:

https://blogs.technet.microsoft.com/msrc/2017/05/12/customer-guidance-for-wannacrypt-attacks/

General information on ransomware:

https://www.microsoft.com/en-us/security/portal/mmpc/shared/ransomware.aspx

Microsoft Malware Protection Center blog:

https://blogs.technet.microsoft.com/mmpc/2017/05/12/wannacrypt-ransomware-worm-targets-out-of-date-systems/

MS17-010 Security Update:

https://technet.microsoft.com/en-us/library/security/ms17-010.aspx

The simplest and most generally recommended approach is to deploy the latest Cumulative Update to Windows 10 or Server 2016 systems & the latest Monthly Rollup to pre-Windows 10 machines, and use the built-in ConfigMgr Compliance reports to determine overall compliance.

This blog post originally referenced SQL queries that could be leveraged to determine devices without MS17-010 installed, however these SQL queries would need to get updated every month to include newer Article ID’s as new updates are released. A better approach to determine compliance is to use a PowerShell script (sample script posted below) in a Configuration Baseline to check version of SRV.sys file, based on guidance provided in KB4023262. The original SQL queries are still posted in the blog for reference, but these queries cannot be used without modification after 06/13/2017.

Sample script provided by Aaron Harris to compare versions of SRV.sys with the known good versions:

# This script applies to Windows XP and Windows Server 2003 and later versions. It requires Windows PowerShell 2.0 or later.

[reflection.assembly]::LoadWithPartialName("System.Version")
$os = Get-WmiObject -class Win32_OperatingSystem
$osName = $os.Caption
$s = "%systemroot%\system32\drivers\srv.sys"
$v = [System.Environment]::ExpandEnvironmentVariables($s)
If (Test-Path "$v")
    {
    Try
        {
        $versionInfo = (Get-Item $v).VersionInfo
        $versionString = "$($versionInfo.FileMajorPart).$($versionInfo.FileMinorPart).$($versionInfo.FileBuildPart).$($versionInfo.FilePrivatePart)"
        $fileVersion = New-Object System.Version($versionString)
        }
    Catch
        {
        $state = $null
        Return $state
        }
    }
Else
    {
    $state = $null
    Return $state
    }
if ($osName.Contains("Vista") -or ($osName.Contains("2008") -and -not $osName.Contains("R2")))
    {
    if (([string]($version[3]))[0] -eq "1")
        {
        $currentOS = "$osName GDR"
        $expectedVersion = New-Object System.Version("6.0.6002.19743")
        } 
    elseif (([string]($version[3]))[0] -eq "2")
        {
        $currentOS = "$osName LDR"
        $expectedVersion = New-Object System.Version("6.0.6002.24067")
        }
    else
        {
        $currentOS = "$osName"
        $expectedVersion = New-Object System.Version("9.9.9999.99999")
        }
    }
elseif ($osName.Contains("Windows 7") -or ($osName.Contains("2008 R2")))
    {
    $currentOS = "$osName LDR"
    $expectedVersion = New-Object System.Version("6.1.7601.23689")
    }
elseif ($osName.Contains("Windows 8.1") -or $osName.Contains("2012 R2"))
    {
    $currentOS = "$osName LDR"
    $expectedVersion = New-Object System.Version("6.3.9600.18604")
    }
elseif ($osName.Contains("Windows 8") -or $osName.Contains("2012"))
    {
    $currentOS = "$osName LDR"
    $expectedVersion = New-Object System.Version("6.2.9200.22099")
    }
elseif ($osName.Contains("Windows 10"))
    {
    if ($os.BuildNumber -eq "10240")
        {
        $currentOS = "$osName TH1"
        $expectedVersion = New-Object System.Version("10.0.10240.17319")
        }
    elseif ($os.BuildNumber -eq "10586")
        {
        $currentOS = "$osName TH2"
        $expectedVersion = New-Object System.Version("10.0.10586.839")
        }
    elseif ($os.BuildNumber -eq "14393")
        {
        $currentOS = "$($osName) RS1"
        $expectedVersion = New-Object System.Version("10.0.14393.953")
        }
    elseif ($os.BuildNumber -eq "15063")
        {
        $currentOS = "$osName RS2"
        #"No need to Patch. RS2 is released as patched. "
        $state = "Patched"
        return $state
        }
    }
elseif ($osName.Contains("2016"))
    {
    $currentOS = "$osName"
    $expectedVersion = New-Object System.Version("10.0.14393.953")
    }
elseif ($osName.Contains("Windows XP"))
    {
    $currentOS = "$osName"
    $expectedVersion = New-Object System.Version("5.1.2600.7208")
    }
elseif ($osName.Contains("Server 2003"))
    {
    $currentOS = "$osName"
    $expectedVersion = New-Object System.Version("5.2.3790.6021")
    }
else
    {
    $currentOS = "$osName"
    $expectedVersion = New-Object System.Version("9.9.9999.99999")
    }
If ($($fileVersion.CompareTo($expectedVersion)) -lt 0)
    {
    $state = "NotPatched - $versionString"
    }
Else
    {
    $state = "Patched"
    }
$state

Sample Configuration Item implementing the script:

KB2919355 Confusion:

KB2919355 is a pre-requisite for most current updates for Windows 8.1 and Server 2012 R2 systems. These devices without KB2919355 installed would not report any updates for MS17-010 as applicable. KB2919355 was released in April 2014, and requires the servicing stack update KB2919442 to be installed for KB2919355 to be applicable. KB2919442 was released in March 2014 and has been superseded a few times, with the latest update being KB3173424. KB3173424 however requires KB2919355 to be installed, introducing a circular dependency and as a result, neither of these updates show as ‘Required’ on Windows 8.1 and Server 2012 R2 clients. Current recommendation for this scenario is to install KB3021910 (KB3173424 supersedes KB3021910) which would make KB2919355 applicable.

To identify Windows 8.1 and Server 2012 R2 systems that do not report KB2919355 as ‘Installed’, following query can be used:

-- Any Windows 8.1 and Server 2012 R2 machines which do not report ‘KB2919355’ as Installed.

DECLARE @KB2919355SRV NVARCHAR(50) = '8452bac0-bf53-4fbd-915d-499de08c338b'
DECLARE @KB2919355WSx86 NVARCHAR(50) = '4ca4dbaa-fae4-4a7c-9760-8e202d10128f'
DECLARE @KB2919355WSx64 NVARCHAR(50) = '26e2a7ee-34d5-4161-ab79-56625337046f'

-- Windows Server 2012 R2 machines that do not report KB2919355 as Installed.
SELECT 
       distinct RS.Name0,
       UI.ArticleID as ArticleID, 
       UI.BulletinID as BulletinID, 
       'KB2919355' as Title,      
       'Update is not Installed' AS State,
       NULL AS LastStateReceived,
       NULL AS LastStateChanged,
       'KB2919355' AS UniqueUpdateID
FROM v_Update_ComplianceStatusReported UCS
JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID = UCS.Status AND SN.StateID <> 3
JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = '9600' AND OS.Caption0 like '%Server 2012 R2%' -- Server 2012 R2
WHERE UI.CI_UniqueID = @KB2919355SRV -- Server 2012 R2
UNION 
-- Windows 8.1 x86 machines that do not report KB2919355 as Installed.
SELECT 
       distinct RS.Name0,
       UI.ArticleID as ArticleID, 
       UI.BulletinID as BulletinID, 
       'KB2919355' as Title,      
       'Update is not Installed' AS State,
       NULL AS LastStateReceived,
       NULL AS LastStateChanged,
       'KB2919355' AS UniqueUpdateID
FROM v_Update_ComplianceStatusReported UCS
JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID = UCS.Status AND SN.StateID <> 3
JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = '9600' AND OS.Caption0 like '%Windows 8.1%' -- Windows 8.1
JOIN v_GS_COMPUTER_SYSTEM CS1 ON CS1.ResourceID = RS.ResourceID AND CS1.SystemType0 = 'X86-based PC' -- x86
WHERE UI.CI_UniqueID = @KB2919355WSx86
UNION 
-- Windows 8.1 x64 machines that do not report KB2919355 as Installed.
SELECT 
       distinct RS.Name0,
       UI.ArticleID as ArticleID, 
       UI.BulletinID as BulletinID, 
       'KB2919355' as Title,      
       'Update is not Installed' AS State,
       NULL AS LastStateReceived,
       NULL AS LastStateChanged,
       'KB2919355' AS UniqueUpdateID
FROM v_Update_ComplianceStatusReported UCS
JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID = UCS.Status AND SN.StateID <> 3
JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = '9600' AND OS.Caption0 like '%Windows 8.1%' -- Windows 8.1
JOIN v_GS_COMPUTER_SYSTEM CS1 ON CS1.ResourceID = RS.ResourceID AND CS1.SystemType0 = 'X64-based PC' -- x64
WHERE UI.CI_UniqueID = @KB2919355WSx64
 

Installation of KB3021910:

KB3173424 was revised on 05/05/2017 and as a result, the update it supersedes (KB3021910) can be deployed via ConfigMgr by customers with Supersedence rule set to “Expire after X number of days”, provided X is a value that allows ConfigMgr to still synchronize the superseded update KB3021910. Some customers may need to un-decline (set the update to ‘Not Approved’) KB3021910 in WSUS, if this update has been declined manually or by cleanup tasks.

For customers with Supersedence rule set to ‘Expire immediately’, they would need to either set the Supersedence rules to “Expire after X number of days” and run Software Update Synchronization to make KB3021910 appear in SCCM (as a superseded update) OR deploy KB3021910 (or KB2919442) via Software Distribution (or SCUP).

NOTE that most customers set the Supersedence rule to ‘Expire after 90 days’, so the only way to deploy KB3021910 after 08/05/2017 would be to use Software Distribution (or SCUP).

SQL Queries (Archived)

NOTE: This information is now available in multiple languages in the Microsoft Knowledge Base article Configuration Manager SQL Server queries for compliance reporting related to MS17-010.

The following is shared by CSS Support Escalation Engineer Vinay Pamnani, to help give the IT Pro some sample queries that may assist them in their security update compliance reporting as it relates to 'MS17-010'. It is provided as a sample and NOT to be taken as a definitive compliance posture information source. As with all Software Update compliance information, the queries below rely on current and accurate scan result information in the ConfigMgr database. The sample queries below have had limited testing against ConfigMgr version 1702 and SQL Server 2016.

These SQL queries rely on the Article ID’s for March/April/May 2017 updates, and as mentioned earlier, these SQL queries would not yield accurate results without adding the Article ID’s for updates that are released after May 2017.

What do these queries do?

Pre-Windows 10 machines:

Windows 8.1 and Server 2012 R2 machines that do not report KB2919355 as installed will be returned by the query. This is because KB2919355 is required for the later KBs to be reported as applicable. So, these systems can be considered unpatched and require further investigation.

For the Windows Vista, Windows 7, Windows 8.1, Windows Server 2008 R2 SP1, Windows Server 2008 SP2, Windows Server 2012, and Windows Server 2012 R2 queries below, the systems returned will be those that do not have either the March, April, or May monthly rollups installed -AND- are reporting the following specific ‘Security Only’ updates as ‘Required’:

Windows Vista and Server 2008 SP2: KB4012598
Windows 7 and Server 2008 R2 SP1: KB4012212
Windows Server 2012: KB4012214
Windows Server 2012 R2 and Windows 8.1: KB4012213

-- For Windows 7, Server 2008 R2 SP1, Windows Server 2012, Server 2012 R2 and Windows 8.1, Windows Vista and Server 2008 SP2
-- This query lists machines that are reporting any of the 'Security Only' updates as 'Required'.
-- If any machine has either March, April or May Monthly Rollup installed, then they wouldn't report March 'Security Only' update as 'Required', but look for the Monthly updates anyway.
-- Also include any Windows 8.1 and Server 2012 R2 machines which do not report ‘KB2919355’ as Installed.

DECLARE @MarchSecurityOnly TABLE (ArticleID NVARCHAR(20))
INSERT INTO @MarchSecurityOnly VALUES ('4012212')
INSERT INTO @MarchSecurityOnly VALUES ('4012213')
INSERT INTO @MarchSecurityOnly VALUES ('4012214')
INSERT INTO @MarchSecurityOnly VALUES ('4012598')

DECLARE @MarchMonthly TABLE (ArticleID NVARCHAR(20))
INSERT INTO @MarchMonthly VALUES ('4012215')
INSERT INTO @MarchMonthly VALUES ('4015549')
INSERT INTO @MarchMonthly VALUES ('4019264')
INSERT INTO @MarchMonthly VALUES ('4012216')
INSERT INTO @MarchMonthly VALUES ('4015550')
INSERT INTO @MarchMonthly VALUES ('4019215')
INSERT INTO @MarchMonthly VALUES ('4012217')
INSERT INTO @MarchMonthly VALUES ('4015551')
INSERT INTO @MarchMonthly VALUES ('4019216')

DECLARE @KB2919355SRV NVARCHAR(50) = '8452bac0-bf53-4fbd-915d-499de08c338b'
DECLARE @KB2919355WSx86 NVARCHAR(50) = '4ca4dbaa-fae4-4a7c-9760-8e202d10128f'
DECLARE @KB2919355WSx64 NVARCHAR(50) = '26e2a7ee-34d5-4161-ab79-56625337046f'

SELECT 
       RS.Name0, 
       UI.ArticleID as ArticleID, 
       UI.BulletinID as BulletinID, 
       UI.Title as Title, 
       SN.StateDescription AS State,
       UCS.LastStatusCheckTime AS LastStateReceived,
       UCS.LastStatusChangeTime AS LastStateChanged,
       UI.CI_UniqueID AS UniqueUpdateID
FROM v_Update_ComplianceStatusReported UCS
JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID=2 AND SN.StateID = UCS.Status
WHERE UI.ArticleID IN (SELECT ArticleID FROM @MarchSecurityOnly) 
AND RS.Name0 NOT IN (
       -- Monthly is installed
       SELECT distinct RS.Name0 
       FROM v_Update_ComplianceStatusReported UCS
       JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
       JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
       JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID=3 AND SN.StateID = UCS.Status
       WHERE UI.ArticleID IN (SELECT ArticleID FROM @MarchMonthly) 
)
UNION
-- Windows Server 2012 R2 machines that do not report KB2919355 as Installed.
SELECT 
       distinct RS.Name0,
       UI.ArticleID as ArticleID, 
       UI.BulletinID as BulletinID, 
       'KB2919355' as Title,      
       'Update is not Installed' AS State,
       NULL AS LastStateReceived,
       NULL AS LastStateChanged,
       'KB2919355' AS UniqueUpdateID
FROM v_Update_ComplianceStatusReported UCS
JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID = UCS.Status AND SN.StateID <> 3
JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = '9600' AND OS.Caption0 like '%Server 2012 R2%' -- Server 2012 R2
WHERE UI.CI_UniqueID = @KB2919355SRV -- Server 2012 R2
UNION 
-- Windows 8.1 x86 machines that do not report KB2919355 as Installed.
SELECT 
       distinct RS.Name0,
       UI.ArticleID as ArticleID, 
       UI.BulletinID as BulletinID, 
       'KB2919355' as Title,      
       'Update is not Installed' AS State,
       NULL AS LastStateReceived,
       NULL AS LastStateChanged,
       'KB2919355' AS UniqueUpdateID
FROM v_Update_ComplianceStatusReported UCS
JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID = UCS.Status AND SN.StateID <> 3
JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = '9600' AND OS.Caption0 like '%Windows 8.1%' -- Windows 8.1
JOIN v_GS_COMPUTER_SYSTEM CS1 ON CS1.ResourceID = RS.ResourceID AND CS1.SystemType0 = 'X86-based PC' -- x86
WHERE UI.CI_UniqueID = @KB2919355WSx86
UNION 
-- Windows 8.1 x64 machines that do not report KB2919355 as Installed.
SELECT 
       distinct RS.Name0,
       UI.ArticleID as ArticleID, 
       UI.BulletinID as BulletinID, 
       'KB2919355' as Title,      
       'Update is not Installed' AS State,
       NULL AS LastStateReceived,
       NULL AS LastStateChanged,
       'KB2919355' AS UniqueUpdateID
FROM v_Update_ComplianceStatusReported UCS
JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID = UCS.Status AND SN.StateID <> 3
JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = '9600' AND OS.Caption0 like '%Windows 8.1%' -- Windows 8.1
JOIN v_GS_COMPUTER_SYSTEM CS1 ON CS1.ResourceID = RS.ResourceID AND CS1.SystemType0 = 'X64-based PC' -- x64
WHERE UI.CI_UniqueID = @KB2919355WSx64

Windows 10 and Server 2016

For the Windows 10 and Server 2016 queries, there are 2 scenarios that may apply depending on an environment’s configuration on the expiry of superseded updates in ConfigMgr. For more information on this, see the Supersedence rules section on TechNet and this.

Scenario 1: Customers with Supersedence rule NOT set to ‘Immediately expire’:

If the superseded updates are not expired and therefore still available in ConfigMgr, you can use the following query to help identify Windows 10 and Windows Server 2016 systems that do not have the March CU or a subsequent CU installed. Please note that for the March CU data to be evaluated, the months to wait before an update is expired value in ConfigMgr must be set to a high enough value such that the March update was not expired. The same consideration applies to the subsequent updates. If this does not apply to your environment, the information in Scenario 2: Customers with Supersedence rule set to ‘Immediately expire’ (or not long enough) can be tried.

For the following Windows 10 and Server 2016, the query below returns systems that do not have any of the following monthly CUs, released in March or later (through the date of this post), installed:

Win10  RTM: KB4012606, KB4019474, KB4015221, KB4016637
Win10 1511: KB4013198, KB4015219, KB4016636, KB4019473
Win10 1607/Server 2016: KB4013429, KB4015217, KB4015438, KB4016635, KB4019472

-- Windows 10 machines that do not have the March (or any of the superseding updates) installed, and could be 'unpatched'.
-- These queries are OS dependent, since we are querying individual KB's, and need to compare those KB's against proper builds to prevent getting inaccurate results.

-- Windows 10 RTM
DECLARE @BuildNumberRTM INT = '10240'
DECLARE @MarchWin10 TABLE (ArticleID NVARCHAR(20))
INSERT INTO @MarchWin10 VALUES ('4012606') -- March Cumulative
INSERT INTO @MarchWin10 VALUES ('4019474')
INSERT INTO @MarchWin10 VALUES ('4015221')
INSERT INTO @MarchWin10 VALUES ('4016637')

-- Windows 10 1511
DECLARE @BuildNumber1511 INT = '10586'
DECLARE @MarchWin101511 TABLE (ArticleID NVARCHAR(20))
INSERT INTO @MarchWin101511 VALUES ('4013198') -- March Cumulative
INSERT INTO @MarchWin101511 VALUES ('4015219')
INSERT INTO @MarchWin101511 VALUES ('4016636')
INSERT INTO @MarchWin101511 VALUES ('4019473')

-- Windows 10 1607
DECLARE @BuildNumber1607 INT = '14393'
DECLARE @MarchWin101607 TABLE (ArticleID NVARCHAR(20))
INSERT INTO @MarchWin101607 VALUES ('4013429') -- March Cumulative
INSERT INTO @MarchWin101607 VALUES ('4015217')
INSERT INTO @MarchWin101607 VALUES ('4015438')
INSERT INTO @MarchWin101607 VALUES ('4016635')
INSERT INTO @MarchWin101607 VALUES ('4019472')

SELECT RS.Name0, OS.BuildNumber0 FROM v_R_System RS
JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = @BuildNumber1607
WHERE RS.Name0 NOT IN (
SELECT RS.Name0
FROM v_Update_ComplianceStatusReported UCS
JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID=3 AND SN.StateID = UCS.Status
JOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumber1607
WHERE UI.ArticleID IN (SELECT ArticleID FROM @MarchWin101607)
)
UNION
SELECT RS.Name0, OS.BuildNumber0 FROM v_R_System RS
JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = @BuildNumberRTM
WHERE RS.Name0 NOT IN (
SELECT RS.Name0
FROM v_Update_ComplianceStatusReported UCS
JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID=3 AND SN.StateID = UCS.Status
JOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumberRTM
WHERE UI.ArticleID IN (SELECT ArticleID FROM @MarchWin10)
)
UNION
SELECT RS.Name0, OS.BuildNumber0 FROM v_R_System RS
JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = @BuildNumber1511
WHERE RS.Name0 NOT IN (
SELECT RS.Name0
FROM v_Update_ComplianceStatusReported UCS
JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID=3 AND SN.StateID = UCS.Status
JOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumber1511
WHERE UI.ArticleID IN (SELECT ArticleID FROM @MarchWin101511)
)

Scenario 2: Customers with Supersedence rule set to ‘Immediately expire’ (or not long enough):

Since CUs are superseded each month, and expired due to the ConfigMgr Supersedence Rules option being set to ‘Immediately Expire’, compliance data is not available on the expired update – in this scenario, you will, however, have compliance data on the newest CU available, so the simplest path forward would be to deploy the latest CU and report against it.

Utilizing Hardware Inventory

For customers who have extended Hardware Inventory and enabled the Win32_QuickFixEngineering class, they could use the hardware inventory data to report if the systems are non-compliant.

Below is a sample SQL query that utilizes the Hardware Inventory data from Win32_QuickFixEngineering class to determine if March/April/May updates are installed on Windows 10 & above devices. Similar logic can be used to write queries to retrieve this data for Pre-Windows 10 devices. Once again, the SQL query would need to updated each month to include newer Article ID’s.

-- Customers with Win32_QuickFixEngineering class enabled for HINV can use these queries.
-- Windows 10 machines that do not have the March (or any of the superseding updates) installed and could be 'unpatched'.
-- These queries are OS dependent, since we are querying individual KB's, and need to compare those KB's against proper builds to prevent getting inaccurate results.
-- Query limits results for machines that have at least one row in v_GS_Quick_Fix_Engineering class to ensure there is some HINV data for the machine for this class.

-- Windows 10 RTM
DECLARE @BuildNumberRTM INT = '10240'
DECLARE @MarchWin10 TABLE (ArticleID NVARCHAR(20))
INSERT INTO @MarchWin10 VALUES ('4012606') -- March Cumulative
INSERT INTO @MarchWin10 VALUES ('4019474')
INSERT INTO @MarchWin10 VALUES ('4015221')
INSERT INTO @MarchWin10 VALUES ('4016637')

-- Windows 10 1511
DECLARE @BuildNumber1511 INT = '10586'
DECLARE @MarchWin101511 TABLE (ArticleID NVARCHAR(20))
INSERT INTO @MarchWin101511 VALUES ('4013198') -- March Cumulative
INSERT INTO @MarchWin101511 VALUES ('4015219')
INSERT INTO @MarchWin101511 VALUES ('4016636')
INSERT INTO @MarchWin101511 VALUES ('4019473')

-- Windows 10 1607
DECLARE @BuildNumber1607 INT = '14393'
DECLARE @MarchWin101607 TABLE (ArticleID NVARCHAR(20))
INSERT INTO @MarchWin101607 VALUES ('4013429') -- March Cumulative
INSERT INTO @MarchWin101607 VALUES ('4015217')
INSERT INTO @MarchWin101607 VALUES ('4015438')
INSERT INTO @MarchWin101607 VALUES ('4016635')
INSERT INTO @MarchWin101607 VALUES ('4019472')

SELECT RS.Name0, OS.BuildNumber0, QFE.HotFixID0, COUNT(QFEALL.HotFixID0) AS TotalHotfixes FROM v_R_System RS 
JOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumberRTM
JOIN v_GS_QUICK_FIX_ENGINEERING QFEALL ON QFEALL.ResourceID = RS.ResourceID
LEFT JOIN v_GS_QUICK_FIX_ENGINEERING QFE ON QFE.ResourceID = RS.ResourceID AND QFE.HotFixID0 IN (SELECT 'KB' + ArticleID FROM @MarchWin10)
WHERE QFE.HotFixID0 IS NULL 
GROUP BY RS.Name0, OS.BuildNumber0, QFE.HotFixID0
HAVING COUNT(QFEALL.HotFixID0) > 0
UNION
SELECT RS.Name0, OS.BuildNumber0, QFE.HotFixID0, COUNT(QFEALL.HotFixID0) AS TotalHotfixes FROM v_R_System RS 
JOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumber1511
JOIN v_GS_QUICK_FIX_ENGINEERING QFEALL ON QFEALL.ResourceID = RS.ResourceID
LEFT JOIN v_GS_QUICK_FIX_ENGINEERING QFE ON QFE.ResourceID = RS.ResourceID AND QFE.HotFixID0 IN (SELECT 'KB' + ArticleID FROM @MarchWin101511)
WHERE QFE.HotFixID0 IS NULL 
GROUP BY RS.Name0, OS.BuildNumber0, QFE.HotFixID0
HAVING COUNT(QFEALL.HotFixID0) > 0
UNION
SELECT RS.Name0, OS.BuildNumber0, QFE.HotFixID0, COUNT(QFEALL.HotFixID0) AS TotalHotfixes FROM v_R_System RS 
JOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumber1607
JOIN v_GS_QUICK_FIX_ENGINEERING QFEALL ON QFEALL.ResourceID = RS.ResourceID
LEFT JOIN v_GS_QUICK_FIX_ENGINEERING QFE ON QFE.ResourceID = RS.ResourceID AND QFE.HotFixID0 IN (SELECT 'KB' + ArticleID FROM @MarchWin101607)
WHERE QFE.HotFixID0 IS NULL 
GROUP BY RS.Name0, OS.BuildNumber0, QFE.HotFixID0
HAVING COUNT(QFEALL.HotFixID0) > 0

Comments (22)
  1. dandy says:

    That’s great and all right now, given we know the KB article IDs for March/April/May, but if I build a new system next month and apply the corresponding Cumulative Update from June, then it’ll be compliant (given that a CU, by definition, includes all previous patches), but the query as it exists today will report the system is non-compliant because it’s only querying for updates it knows about at the time the query is written. Obviously it can’t anticipate ahead of time what the KB ID is going to be for the CU for June.

    Are we destined to have to add more and more KB numbers in the query every month, or is there something else we can look for so this doesn’t become a maintenance nightmare? For Windows 10 and Server 2016, I would think that we could verify whether the **build number** is >= the value that was assigned as of March 2017 (14393.953, according to https://support.microsoft.com/en-us/help/4013429/windows-10-update-kb4013429), but build numbers don’t increase with each CU for 7, 8.1, 2008 R2, 2012 and 2012 R2, AFAIK. For older, out-of-support versions of Windows, my understanding is that as long as 4012598 is present, the system is compliant.

    1. Phil_GoCubs says:

      Dandy,
      I agree 100%, this is not a dynamic report and would require monthly maintenance which is an administrative nightmare. Hopefully Microsoft can provide a better long term SCCM report. Ideally, it would be a dynamic report that simply shows the compliancy for all machines in a collection for a given Bulletin (e.g Show me all [workstations] vulnerable for [MSxx-yyy]).

    2. dandy says:

      No follow-up to this?

      I have now put together a script that’ll handle Win 10 / Server 2016 correctly moving forward, and all the out-of-support OSes, but I’m still looking at alternatives for OSes that still get a monthly cumulative update that’ll supersede the previous one. Obviously I don’t want to keep adding KB numbers every month.

      Is there a file on disk, for example, that *must* be at least version X, so if I find X+1 or X+10, I can assume it’s still a patched version…? This might not be so straightforward for an SCCM query, but I’m working with a PowerShell script – which I suppose would be suitable as a script CI for a baseline.

      Anyone?

      1. Yes we will be updating the blog with a new DCM Powershell script that queries file SRV.sys.

      2. The queries in the post were not meant to be a long-term approach, as it would require monthly updates to accommodate new KB’s. Using a PS script in a Configuration Baseline is the best way to monitor, and we are working on a script you can use for this based on the guidance provided in https://support.microsoft.com/en-us/help/4023262, which would compare the version of srv.sys file to determine compliance.

        1. dandy says:

          Perfect. This validates the script I had already come up with on my own, after I had confirmed that each update replaces srv.sys. I built a table of version numbers based on what each KB has reported, and the script then confirms that the version present is >= the earliest fixed update.

  2. Sai Kurapati says:

    This is a great post and very helpful. Can you clarify the requirements for Windows 8.1 and 2012 R2? this article points out that KB2919355 is a pre-requisite to apply recent i.e. May 2017 Monthly Quality Rollup. But I have systems without KB2919355 but applied May Rollup successfully and file versions match as well. So what’s the real urgency of applying KB2919355? is it Mandatory to protect from WannaCrypt attack?

  3. Sai Kurapati says:

    Ignore my previous post, looks like there is something wrong in the Pre-Windows 10 Query. Please validate it again, its also listing the machines that have KB2919355 as missing.

    1. Thanks for the input we will make the required changes to the logic of that and then verify and let us know.

  4. The blog was published and removed yesterday. I see the blog is back now, is there any changes made to the content?

    1. Nothing much, we have modified a bit in formatting and change some variable names in Script. Content is pretty much the same.

  5. The part of the query for 2919355 should be changed with the following (to avoid false positives):

    SELECT
           distinct RS.Name0,
           UI.ArticleID as ArticleID,
           UI.BulletinID as BulletinID,
           UI.Title as Title,    
           ‘Update is not Installed’ AS State,
           NULL AS LastStateReceived,
           NULL AS LastStateChanged,
           ‘KB2919355’ AS UniqueUpdateID,
    UCS.Status
    FROM v_Update_ComplianceStatusReported UCS
    JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
    JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
    JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID = UCS.Status AND SN.StateID 3
    JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = ‘9600’ — Windows 8.1 and Server 2012 R2
    WHERE UI.ArticleID = @KB2919355 and UI.Title like ‘Windows Server%’

    1. Correction
      it should be

      SELECT
             distinct RS.Name0,
             UI.ArticleID as ArticleID,
             UI.BulletinID as BulletinID,
             ‘KB2919355’ as Title,    
             ‘Update is not Installed’ AS State,
             NULL AS LastStateReceived,
             NULL AS LastStateChanged,
             ‘KB2919355’ AS UniqueUpdateID
      FROM v_Update_ComplianceStatusReported UCS
      JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
      JOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceID
      JOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID = UCS.Status AND SN.StateID = 2
      JOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = ‘9600’ — Windows 8.1 and Server 2012 R2
      WHERE UI.ArticleID = @KB2919355

  6. Please test the queries and let us know for any feedbacks in the comments below.

    1. will says:

      Umair, appreciate the queries. We wrote our own internally and built a pivot table off of it. One discrepancy I found – if a machine has no SW update compliance data, it will not show up in your report because you are doing an inner join. In our organization we consider machines that are ‘unknown’ at risk.

    2. Steve says:

      This query does not appear to be working properly. When I run the topmost query in our system it returns a few hundred systems as “Update is Not Installed” . However, upon deeper dive, if I run Get-Hotfix against the systems, the majority, if not all, show that the Hotfix was installed quite a while ago in most cases. Furthermore, if I look at Compliance 8 report – Computers in a specific state for an update (secondary), it shows the systems under the status of “Update is not required”. It should show the systems under the status of “Update Installed” since we can verify that the status is in fact installed according to the local systems. So, it seems something isn’t quite right with how this patch is reporting its status.

      I’ve opened a case Microsoft on the reporting issue.

      Has anyone else validated the results from this query? Specifically where it is saying 2919355 is not installed on Windows Server 2012 R2 systems, but the system itself shows it to be installed and Compliance Reporting shows it to not be required?

      Thanks!

      1. Steve, Your looks to be a state message issue. Since you have already opened a case we will try to get to the bottom of it.

      2. Joe says:

        I find the same results as Steve

  7. Jeremy says:

    I have found the same issue as Steve, any updates or workarounds to this would be very helpful! Thanks all for contributing.

    1. Hi Jeremy,

      As was already mentioned by Umair and noted at the beginning of the blog, since the ConfigMgr queries provided leverage Software Update compliance data in the ConfigMgr database, accurate scan result data reporting from clients is required. If state messaging problems, or any other environmental dependency problem, such as successful scanning against a SUP, those issues need to be resolved for the clients in question.

      1. n412 says:

        No – It’s a problem with the query itself – not with state messaging or environment. When so many people are reporting the exact same problem running the exact same query – the chances of everyone having a “state messaging problem” is slim to none.

        Console shows accurate compliance numbers on 2919355 (fully compliant). Query (incorrectly) shows nearly every server needs this update – even when already have it. State messaging is working just fine.

  8. Aaron Harris says:

    Please let us know if you have any issues with the updated PowerShell script. It can be deployed in a baseline, and reports compliance by determining the version of srv.sys, based on the expected/patched version documented in the KB article, so it can still be used after new updates are published in the coming months.

Comments are closed.

Skip to main content