Find All Computers Vulnerable to ‘WannaCry’ (‘WannaCrypt’, ‘WCRY’)
This cyber-attack has affected over 230 000 computers in more than 150 countries. Microsoft has released a number of updates to mitigate the MS17-010 vulnerability which the ransomware program targets with doing an SMB exploit. Lansweeper can be used to find machines that do not have the hotfixes installed to mitigate the SMB vulnerability. Find more info about this exploit in our Wannacry blog post.
WannaCry MS17-010 Query
Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
Case
When tsysOS.OSname = 'Win XP' And SubQuery1.PatchIDMax >= 4012598 Then
'Up to date'
When tsysOS.OSname = 'Win 2003' And SubQuery1.PatchIDMax >= 4012598 Then
'Up to date'
When tsysOS.OSname = 'Win 2003 R2' And SubQuery1.PatchIDMax >= 4012598 Then
'Up to date'
When tsysOS.OSname = 'Win Vista' And SubQuery1.PatchIDMax >= 4012598 Then
'Up to date'
When tsysOS.OSname = 'Win 2008' And SubQuery1.PatchIDMax >= 4012598 Then
'Up to date'
When tsysOS.OSname = 'Win 7' And SubQuery1.PatchIDMax >= 4012212 Then
'Up to date'
When tsysOS.OSname = 'Win 7 RC' And SubQuery1.PatchIDMax >= 4012212 Then
'Up to date'
When tsysOS.OSname = 'Win 2008 R2' And SubQuery1.PatchIDMax >= 4012212 Then
'Up to date'
When tsysOS.OSname = 'Win 2012' And SubQuery1.PatchIDMax >= 4012214 Then
'Up to date'
When tsysOS.OSname = 'Win 8' And SubQuery1.PatchIDMax >= 4012598 Then
'Up to date'
When tsysOS.OSname = 'Win 8.1' And SubQuery1.PatchIDMax >= 4012213
Then 'Up to date'
When tsysOS.OSname = 'Win 2012 R2' And SubQuery1.PatchIDMax >= 4012213 Then
'Up to date'
When tsysOS.OScode Like '10.0.10240' And SubQuery1.PatchIDMax >= 4012606
Then 'Up to date'
When tsysOS.OScode Like '10.0.10586' And SubQuery1.PatchIDMax >= 4013198
Then 'Up to date'
When tsysOS.OScode Like '10.0.14393' And SubQuery1.PatchIDMax >= 4013429
Then 'Up to date'
When tsysOS.OSname = 'Win 2016' And SubQuery1.PatchIDMax >= 4013429 Then
'Up to date'
When tsysOS.OScode Like '10.0.15063' Then 'Up to date'
When tsysOS.OScode Like '10.0.16299' Then 'Up to date'
When tsysOS.OScode Like '10.0.17134' Then 'Up to date'
When tsysOS.OScode Like '10.0.17763' Then 'Up to date'
When tsysOS.OSname = 'Win 2019' Then 'Up to date'
When tsysOS.OScode Like '10.0.18362' Then 'Up to date'
When tsysOS.OScode Like '10.0.18363' Then 'Up to date'
When tsysOS.OScode Like '10.0.19041' Then 'Up to date'
When tsysOS.OScode Like '10.0.19042' Then 'Up to date'
Else 'Out of date'
End As [Patch status],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
Case
When tsysOS.OScode Like '10.0.10240%' Then '1507'
When tsysOS.OScode Like '10.0.10586%' Then '1511'
When tsysOS.OScode Like '10.0.14393%' Then '1607'
When tsysOS.OScode Like '10.0.15063%' Then '1703'
When tsysOS.OScode Like '10.0.16299%' Then '1709'
When tsysOS.OScode Like '10.0.17134%' Then '1803'
When tsysOS.OScode Like '10.0.17763%' Then '1809'
When tsysOS.OScode Like '10.0.18362%' Then '1903'
When tsysOS.OScode Like '10.0.18363%' Then '1909'
When tsysOS.OScode Like '10.0.19041%' Then '2004'
When tsysOS.OScode Like '10.0.19042%' Then '20H2'
End As Version,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
SubQuery1.PatchIDMax As [Latest Patch Scanned],
Case
When tsysOS.OSname = 'Win XP' And SubQuery1.PatchIDMax >= 4012598 Then ''
When tsysOS.OSname = 'Win 2003' And SubQuery1.PatchIDMax >= 4012598 Then ''
When tsysOS.OSname = 'Win 2003 R2' And SubQuery1.PatchIDMax >= 4012598 Then
''
When tsysOS.OSname = 'Win Vista' And SubQuery1.PatchIDMax >= 4012598 Then ''
When tsysOS.OSname = 'Win 2008' And SubQuery1.PatchIDMax >= 4012598 Then ''
When tsysOS.OSname = 'Win 7' And SubQuery1.PatchIDMax >= 4012212 Then ''
When tsysOS.OSname = 'Win 7 RC' And SubQuery1.PatchIDMax >= 4012212 Then ''
When tsysOS.OSname = 'Win 2008 R2' And SubQuery1.PatchIDMax >= 4012212 Then
''
When tsysOS.OSname = 'Win 2012' And SubQuery1.PatchIDMax >= 4012214 Then ''
When tsysOS.OSname = 'Win 8' And SubQuery1.PatchIDMax >= 4012598 Then ''
When tsysOS.OSname = 'Win 8.1' And SubQuery1.PatchIDMax >= 4012213 Then ''
When tsysOS.OSname = 'Win 2012 R2' And SubQuery1.PatchIDMax >= 4012213 Then
''
When tsysOS.OScode Like '10.0.10240' And SubQuery1.PatchIDMax >= 4012606
Then ''
When tsysOS.OScode Like '10.0.10586' And SubQuery1.PatchIDMax >= 4013198
Then ''
When tsysOS.OScode Like '10.0.14393' And SubQuery1.PatchIDMax >= 4013429
Then ''
When tsysOS.OSname = 'Win 2016' And SubQuery1.PatchIDMax >= 4013429 Then ''
When tsysOS.OScode Like '10.0.15063' Then ''
When tsysOS.OScode Like '10.0.16299' Then ''
When tsysOS.OScode Like '10.0.17134' Then ''
When tsysOS.OScode Like '10.0.17763' Then ''
When tsysOS.OSname = 'Win 2019' Then ''
When tsysOS.OScode Like '10.0.18362' Then ''
When tsysOS.OScode Like '10.0.18363' Then ''
When tsysOS.OScode Like '10.0.19041' Then ''
When tsysOS.OScode Like '10.0.19042' Then ''
Else Case
When tsysOS.OSname = 'Win XP' Or tsysOS.OSname = 'Win 2003' Or
tsysOS.OSname = 'Win 2003 R2' Then 'KB4012598'
When tsysOS.OSname = 'Win 2008' Or
tsysOS.OSname = 'Win Vista' Then 'KB4012598'
When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2' Then 'KB4012212'
When tsysOS.OSname = 'Win 2012' Or
tsysOS.OSname = 'Win 8' Then 'KB4012598'
When tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2' Then 'KB4012213'
When tsysOS.OScode Like '10.0.10240' Then 'KB4012606'
When tsysOS.OScode Like '10.0.10586' Then 'KB4013198'
When tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016' Then 'KB4013429'
End
End As [Install one of these updates],
Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
Case
When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) > 3 Then
'Windows update information may not be up to date. We recommend rescanning this machine.'
Else ''
End As Comment,
Case
When tsysOS.OSname = 'Win XP' And SubQuery1.PatchIDMax >= 4012598 Then
'#d4f4be'
When tsysOS.OSname = 'Win 2003' And SubQuery1.PatchIDMax >= 4012598 Then
'#d4f4be'
When tsysOS.OSname = 'Win 2003 R2' And SubQuery1.PatchIDMax >= 4012598 Then
'#d4f4be'
When tsysOS.OSname = 'Win Vista' And SubQuery1.PatchIDMax >= 4012598 Then
'#d4f4be'
When tsysOS.OSname = 'Win 2008' And SubQuery1.PatchIDMax >= 4012598 Then
'#d4f4be'
When tsysOS.OSname = 'Win 7' And SubQuery1.PatchIDMax >= 4012212 Then
'#d4f4be'
When tsysOS.OSname = 'Win 7 RC' And SubQuery1.PatchIDMax >= 4012212 Then
'#d4f4be'
When tsysOS.OSname = 'Win 2008 R2' And SubQuery1.PatchIDMax >= 4012212 Then
'#d4f4be'
When tsysOS.OSname = 'Win 2012' And SubQuery1.PatchIDMax >= 4012214 Then
'#d4f4be'
When tsysOS.OSname = 'Win 8' And SubQuery1.PatchIDMax >= 4012598 Then
'#d4f4be'
When tsysOS.OSname = 'Win 8.1' And SubQuery1.PatchIDMax >= 4012213
Then '#d4f4be'
When tsysOS.OSname = 'Win 2012 R2' And SubQuery1.PatchIDMax >= 4012213 Then
'#d4f4be'
When tsysOS.OScode Like '10.0.10240' And SubQuery1.PatchIDMax >= 4012606
Then '#d4f4be'
When tsysOS.OScode Like '10.0.10586' And SubQuery1.PatchIDMax >= 4013198
Then '#d4f4be'
When tsysOS.OScode Like '10.0.14393' And SubQuery1.PatchIDMax >= 4013429
Then '#d4f4be'
When tsysOS.OSname = 'Win 2016' And SubQuery1.PatchIDMax >= 4013429 Then
'#d4f4be'
When tsysOS.OScode Like '10.0.15063' Then '#d4f4be'
When tsysOS.OScode Like '10.0.16299' Then '#d4f4be'
When tsysOS.OScode Like '10.0.17134' Then '#d4f4be'
When tsysOS.OScode Like '10.0.17763' Then '#d4f4be'
When tsysOS.OSname = 'Win 2019' Then '#d4f4be'
When tsysOS.OScode Like '10.0.18362' Then '#d4f4be'
When tsysOS.OScode Like '10.0.18363' Then '#d4f4be'
When tsysOS.OScode Like '10.0.19041' Then '#d4f4be'
When tsysOS.OScode Like '10.0.19042' Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID,
Max(Cast(Right(tblQuickFixEngineeringUni.HotFixID, 7) As bigint)) As
PatchIDMax
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where Right(tblQuickFixEngineeringUni.HotFixID, 7) Not Like '%[^0-9]%'
Group By tblQuickFixEngineering.AssetID) As SubQuery1 On tblAssets.AssetID =
SubQuery1.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Distinct Top 1000000 TsysLastscan.AssetID As ID,
TsysLastscan.Lasttime As QuickFixLastScanned
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
tblAssets.AssetID = QuickFixLastScanned.ID
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Where tsysOS.OSname <> 'Win 2000 S' And tblAssetCustom.State = 1 And
tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
tblAssets.AssetName
