Spectre Meltdown: alternative report

[Tom_P]

Support for modifying this report will end on the 2nd of April 2018, newly released KB after that date will need to be manually added.

The report found below can be used as an alternative report for the Spectre Meltdown report found in the following forum topic: https://www.lansweeper.com/forum/yaf_postst15707_Meltdown-and-Spectre.aspx#post52974.
Instead of returning only the assets that miss the specified KB's, this report will return all assets with a 'Yes/No' indication of the current patch status.

the main report.

Instructions for adding this report to your Lansweeper installation can be found here.

Select Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
Case When hasKB.AssetID Is Null Then 'No' Else 'Yes' End As [Patched?],
Case When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2' Then 'KB4088875'
When tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname =
'Win 2012 R2' Then
'KB4074597 or KB4056895 or KB4057401 or KB4074594 or KB4088876 or KB4088879'
When tsysOS.OSname =
'Win 2012' Then
'KB4056899 or KB4056896 or KB4057402 or KB4074593 or KB4088877'
When tblOperatingsystem.Version Like '%10240%' Then
'KB4056893 or KB4075199 or KB4077735 or KB4074596 or KB4088786'
When tblOperatingsystem.Version Like '%10586%' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then
'KB4056888 or KB4075200 or KB4074591 or KB4088779'
When tblOperatingsystem.Version Like '%14393%' Or
tsysOS.OSname =
'Win 2016' Then
'KB4056890 or KB4057142 or KB4074590 or KB4077525 or KB4088787'
When tblOperatingsystem.Version Like '%15063%' Then
'KB4056891 or KB4057144 or KB4074592 or KB4077528 or KB4092077 or KB4088782'
When tblOperatingsystem.Version Like '%16299%' Then
'KB4056892 or KB4073291(x86 only) or KB4058258 or KB4074588 or KB4090913 or KB4088776' Else 'No Patch Available' End As [Install one of these updates],
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblBIOS.SMBIOSBIOSVersion As BiosVersion,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Processor,
Case When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then 'Scanning Error: ' +
tsysasseterrortypes.ErrorMsg Else '' End As ScanningErrors,
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,
tblState.Statename As State
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
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblBIOS On tblBIOS.AssetID = tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
TsysLastscan.Lasttime As QuickFixLastScanned
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Inner Join tblAssets On tblAssets.AssetID = TsysLastscan.AssetID
Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned
On tblAssets.AssetID = QuickFixLastScanned.ID
Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Inner Join tblAssets On tblAssets.AssetID = tblErrors.AssetID
Group By tblAssets.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Left Join (Select Distinct tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4074597', 'KB4056899',
'KB4056893', 'KB4056888', 'KB4056890', 'KB4056891', 'KB4056892',
'KB4056895', 'KB4056896', 'KB4057144', 'KB4073291', 'KB4057142',
'KB4075200', 'KB4075199', 'KB4057401', 'KB4057402', 'KB4058258',
'KB4077735', 'KB4074594', 'KB4074588', 'KB4074592', 'KB4074590',
'KB4074596', 'KB4074591', 'KB4090913', 'KB4077528', 'KB4077525',
'KB4092077', 'KB4088782', 'KB4088776', 'KB4088787', 'KB4088779',
'KB4088786', 'KB4088875', 'KB4088876', 'KB4088877', 'KB4088879')) As hasKB
On hasKB.AssetID = tblAssets.AssetID
Where tsysOS.OSname != 'Win 2000 S' And tblAssets.Processor Like '%intel%' And
tsysAssetTypes.AssetTypename Like 'Windows%' And
tsysOS.OScode Not Like '10.0.17%'
Order By tblAssets.Domain,
tblAssets.AssetName

[Bruce_B]

If you have any questions regarding this report, please contact us via support@lansweeper.com.