cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
keys_it
Engaged Sweeper III
Here is a report to find computers missing the emergency path from 3/29/2018 - KB4100480.

Select Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then 'Scanning Error: ' +
tsysasseterrortypes.ErrorMsg Else '' End As ScanningErrors,
Case When tblAssets.Lastseen Is Null Then 'Unknown' Else 'Vulnerable'
End As IsVulnerable,
Case When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2' Then 'KB4100480' Else Null
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
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblState On tblState.State = tblAssetCustom.State
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
Where
((tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4100480')) And
tsysOS.OSname = 'Win 7') Or tsysOS.OSname = 'Win 2008 R2') And
tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
tblAssets.AssetName
4 REPLIES 4
caverna
Engaged Sweeper III
Hi guys!
I found a problem with this report...
It shows some 2008 servers that already has the patch applied.
Poking around, I changed the last 'Where' clausule to:

Where
(tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4100480'))) And
(tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 2008 R2') And
tsysAssetTypes.AssetTypename Like 'Windows%'


Does anyone can validate that?

This is the complete report:

Select Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then 'Scanning Error: ' +
tsysasseterrortypes.ErrorMsg Else '' End As ScanningErrors,
Case When tblAssets.Lastseen Is Null Then 'Unknown' Else 'Vulnerable'
End As IsVulnerable,
Case When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2' Then 'KB4100480' Else Null
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
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblState On tblState.State = tblAssetCustom.State
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
Where
(tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4100480'))) And
(tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 2008 R2') And
tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
tblAssets.AssetName
keys_it
Engaged Sweeper III
You're welcome! Bruce, we originally thought that as well but based on https://support.microsoft.com/en-us/help/4088875 if you have the March Monthly Roll-up or Preview Monthly Roll-up, you need to have this emergency patch installed on Win 7/2008R2 machines to address a vulnerability that was released with the roll up patches. Hope this provides clarification to the need of the patch.
caverna
Engaged Sweeper III
keys_it wrote:
Here is a report to find computers missing the emergency path from 3/29/2018 - KB4100480.


I just saved me a lot of time!
Thank you very much!
Bruce_B
Lansweeper Alumni
Thank you for the report! In relation to CVE-2018-1038 we removed the January and February monthly roll-ups and security updates for Windows 7/2008R2 from our Meltdown&Spectre report last week.

From the information we've found, installing just the March monthly roll-up for the aforementioned operating systems is sufficient, however, KB4100480 does indeed address this issue specifically.