Configuration Manager SQL Server queries for compliance reporting related to MS17-010
Summary
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.
Query information
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
Also, see the following Microsoft Docs topic:
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 CumulativeINSERT INTO @MarchWin10 VALUES ('4019474')INSERT INTO @MarchWin10 VALUES ('4015221')INSERT INTO @MarchWin10 VALUES ('4016637')-- Windows 10 1511DECLARE @BuildNumber1511 INT = '10586'DECLARE @MarchWin101511 TABLE (ArticleID NVARCHAR(20))INSERT INTO @MarchWin101511 VALUES ('4013198') -- March CumulativeINSERT INTO @MarchWin101511 VALUES ('4015219')INSERT INTO @MarchWin101511 VALUES ('4016636')INSERT INTO @MarchWin101511 VALUES ('4019473')-- Windows 10 1607DECLARE @BuildNumber1607 INT = '14393'DECLARE @MarchWin101607 TABLE (ArticleID NVARCHAR(20))INSERT INTO @MarchWin101607 VALUES ('4013429') -- March CumulativeINSERT 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 RSJOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = @BuildNumber1607WHERE RS.Name0 NOT IN (SELECT RS.Name0FROM v_Update_ComplianceStatusReported UCSJOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_IDJOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceIDJOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID=3 AND SN.StateID = UCS.StatusJOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumber1607WHERE UI.ArticleID IN (SELECT ArticleID FROM @MarchWin101607))UNIONSELECT RS.Name0, OS.BuildNumber0 FROM v_R_System RSJOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = @BuildNumberRTMWHERE RS.Name0 NOT IN (SELECT RS.Name0FROM v_Update_ComplianceStatusReported UCSJOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_IDJOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceIDJOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID=3 AND SN.StateID = UCS.StatusJOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumberRTMWHERE UI.ArticleID IN (SELECT ArticleID FROM @MarchWin10))UNIONSELECT RS.Name0, OS.BuildNumber0 FROM v_R_System RSJOIN v_GS_OPERATING_SYSTEM OS ON RS.ResourceID = OS.ResourceID AND OS.BuildNumber0 = @BuildNumber1511WHERE RS.Name0 NOT IN (SELECT RS.Name0FROM v_Update_ComplianceStatusReported UCSJOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_IDJOIN v_R_System RS ON RS.ResourceType=5 AND RS.ResourceID = UCS.ResourceIDJOIN v_StateNames SN ON SN.TopicType=500 AND SN.StateID=3 AND SN.StateID = UCS.StatusJOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumber1511WHERE 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.
Alternatives for Windows 10 and Windows Server 2016
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 RTMDECLARE @BuildNumberRTM INT = '10240'DECLARE @MarchWin10 TABLE (ArticleID NVARCHAR(20))INSERT INTO @MarchWin10 VALUES ('4012606') -- March CumulativeINSERT INTO @MarchWin10 VALUES ('4019474')INSERT INTO @MarchWin10 VALUES ('4015221')INSERT INTO @MarchWin10 VALUES ('4016637')-- Windows 10 1511DECLARE @BuildNumber1511 INT = '10586'DECLARE @MarchWin101511 TABLE (ArticleID NVARCHAR(20))INSERT INTO @MarchWin101511 VALUES ('4013198') -- March CumulativeINSERT INTO @MarchWin101511 VALUES ('4015219')INSERT INTO @MarchWin101511 VALUES ('4016636')INSERT INTO @MarchWin101511 VALUES ('4019473')-- Windows 10 1607DECLARE @BuildNumber1607 INT = '14393'DECLARE @MarchWin101607 TABLE (ArticleID NVARCHAR(20))INSERT INTO @MarchWin101607 VALUES ('4013429') -- March CumulativeINSERT 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 RSJOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumberRTMJOIN v_GS_QUICK_FIX_ENGINEERING QFEALL ON QFEALL.ResourceID = RS.ResourceIDLEFT 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 NULLGROUP BY RS.Name0, OS.BuildNumber0, QFE.HotFixID0HAVING COUNT(QFEALL.HotFixID0) > 0UNIONSELECT RS.Name0, OS.BuildNumber0, QFE.HotFixID0, COUNT(QFEALL.HotFixID0) AS TotalHotfixes FROM v_R_System RSJOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumber1511JOIN v_GS_QUICK_FIX_ENGINEERING QFEALL ON QFEALL.ResourceID = RS.ResourceIDLEFT 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 NULLGROUP BY RS.Name0, OS.BuildNumber0, QFE.HotFixID0HAVING COUNT(QFEALL.HotFixID0) > 0UNIONSELECT RS.Name0, OS.BuildNumber0, QFE.HotFixID0, COUNT(QFEALL.HotFixID0) AS TotalHotfixes FROM v_R_System RSJOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = RS.ResourceID AND OS.BuildNumber0 = @BuildNumber1607JOIN v_GS_QUICK_FIX_ENGINEERING QFEALL ON QFEALL.ResourceID = RS.ResourceIDLEFT 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 NULLGROUP BY RS.Name0, OS.BuildNumber0, QFE.HotFixID0HAVING 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
Properties
Article ID: 4023171 - Last Review: May 22, 2017 - Revision: 35
