Wanncry Patched and Not patched reports

[FixitDave]

I know there are several reports floating round on this issue, but here is what we are using.

This will only list Servers due to the tsysOS.OScode Like '%s' filter.

Patched;

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblAssetCustom.Manufacturer,
  tblAssets.Lastseen,
  Floor((tblAssets.Uptime / 60) / 60) As Uptime
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssets.AssetID In (Select Top 1000000 tblQuickFixEngineering.AssetID
  From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
      On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
  Where tblQuickFixEngineeringUni.HotFixID In ('KB4012216', 'KB4012215',
    'KB4012217', 'KB4012212', 'KB4012204', 'KB4012213', 'KB4015551',
    'KB4019216', 'KB4015550', 'KB4019215', 'KB4013429', 'KB4019472',
    'KB4015217', 'KB4015438', 'KB4016635', 'KB4012598')) And
  tsysOS.OScode Like '%s' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

Not Patched;

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblAssetCustom.Manufacturer,
  tblAssets.Lastseen,
  Floor((tblAssets.Uptime / 60) / 60) As Uptime
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where
  tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
  From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
      On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
  Where tblQuickFixEngineeringUni.HotFixID In ('KB4012216', 'KB4012215',
    'KB4012217', 'KB4012212', 'KB4012204', 'KB4012213', 'KB4015551',
    'KB4019216', 'KB4015550', 'KB4019215', 'KB4013429', 'KB4019472',
    'KB4015217', 'KB4015438', 'KB4016635', 'KB4012598')) And
  tsysOS.OScode Like '%s' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName