Configuration Manager SQL Server queries for compliance reporting related to MS17-010

This article provides some sample queries that can help IT Professionals do security update compliance reporting in regards to security bulletin MS17-010.

Caution These samples are not to be taken as a definitive source for compliance posture information. As with all software update compliance information, these queries rely on current and accurate scan result information in the Microsoft System Center Configuration Manager database. The sample queries have had limited testing against Configuration Manager version 1702 and Microsoft SQL Server 2016.

Please see the "References" section for more information about this attack and ransomware.

The simplest and most generally recommended approach is to deploy the latest cumulative update (CU) to Windows 10-based or Windows Server 2016-based computers, and deploy the latest monthly rollup to pre-Windows 10-based computers. Then, use the built-in Configuration Manager compliance reports to determine overall compliance.

Pre-Windows 10

Windows 8.1-based and Windows Server 2012 R2-based computers that do not report KB 2919355 as installed are returned by the query. This is because KB 2919355 is required for the later KBs to be reported as applicable. Therefore, you can consider these systems not to be updated, and further investigation will be necessary.

Computers that are running Windows Vista, Windows 7, Windows 8.1, Windows Server 2008 R2 SP1, Windows Server 2008 SP2, Windows Server 2012, or Windows Server 2012 R2 are returned by the query if they do not have the March 2017, April 2017, or May 2017 monthly rollups installed and if they report 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

Sample query

-- 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.

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 Windows Server 2016

Scenario 1

Customers who have a supersedence rule not set to "Immediately expire"

If the superseded updates are not expired and are, therefore, still available in Configuration Manager, you can use the following query to help determine which Windows 10 and Windows Server 2016 systems do not have the March CU or a later CU installed.

Note In order for the March CU data to be evaluated, the value in Configuration Manager for the months in which to wait before an update is expired must be set high enough so that the March update is not indicated as expired. The same consideration applies to the later updates. If this does not apply to your environment, you can try the information in Scenario 2.

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

• Windows 10 RTM: KB4012606, KB4019474, KB4015221, KB4016637
• Windows 10 Version 1511: KB4013198, KB4015219, KB4016636, KB4019473
• Windows 10 Version 1607 and Windows Server 2016: KB4013429, KB4015217, KB4015438, KB4016635, KB4019472

Sample query

-- This query is for Windows 10 computers that do not have the March 2017 update (or any of the superseding updates) installed and that could be 'unpatched'.
-- These queries are OS dependent. This is because we are querying individual KB's, and we have to compare those KB's against correct builds to avoid getting inaccurate results.

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 who have a supersedence rule set to "Immediately expire" (or not long enough)

Because CUs are superseded each month and expired if the Configuration Manager Supersedence Rules option is set to "Immediately Expire," compliance data is not available for the expired update. However, in this scenario, you will have compliance data about the newest CU available. Therefore, the simplest path forward is to deploy the latest CU, and then report against it.

The following alternative methods to Scenario 1 and Scenario 2 may help you determine which computers are not appropriately updated.

Alternatives for Windows 10 and Windows Server 2016

Extend Hardware Inventory to include the Win32_QuickFixEngineering class, and use this data to determine which computers have none of the March 2017, April 2017, or May 2017 CUs installed.

Note If you do not have this setting already enabled, and if you enable it now, you must first wait for all the clients to report their Hardware Inventory.

-- Customers with the Win32_QuickFixEngineering class enabled for HINV can use these queries.
-- This query is for Windows 10 computers that do not have the March 2017 update (or any of the superseding updates) installed and could be 'unpatched'.
-- These queries are OS dependent because we are querying individual KB's, and we have to compare those KB's against correct builds to avoid getting inaccurate results.
-- Query limits results for computers that have at least one row in the v_GS_Quick_Fix_Engineering class to make sure that there is some HINV data for the computer 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

Alternatives for all operating systems

Create a Configuration Item and Baseline that queries the March 2017, April 2017, and May 2017 CUs from the Win32_QuickFixEngineering class and that reports compliance.

The following sample Windows PowerShell script can be used in a DCM Baseline.

[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
        }
    }
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"
    }
Else
    {
    $state = "Patched"
    }
$state
 

Customer Guidance for WannaCrypt attacks

Malware Protection Center

Microsoft Malware Protection Center blog

Security Update MS17-010

How to verify that MS17-010 is installed