Are Your Windows 7, Server 2008 and Server 2008 R2 Machines Ready for the ESU?
With the Windows 7 and Windows Server 2008/Windows Server 2008 R2 getting close to their end of life date. It is important to prepare for what needs to happen after January 14, 2020. Whether it is transitioning to Windows 10 or other newer operating systems, or holding on and getting the extended security updates where possible.
Thanks to two community members, AndyCTC and RC62N who worked together to create this audit, you can now check whether your machines are eligible for the Extended Updates.
The report checks whether the required Windows updates are installed so you can install and activate ESU keys as listed by this Microsoft blog. Since it is color-coded, you can quickly see which machines are ready to go and which ones are missing updates.
Extended Security Update Readiness Audit Query
Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Case
When (sha2patch.AssetID Is Not Null) And (SSUMarApr.AssetID Is Not Null) And
(SSUSep.AssetID Is Not Null) And (MonthlyRollups.AssetID Is Not Null) 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.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lasttried,
Case
When sha2patch.AssetID Is Null Then
'SHA-2 code signing support update missing'
When SSUMarApr.AssetID Is Null Then
'Servicing stack update from March(W7/W2008R2) or April(W2008) missing'
When SSUSep.AssetID Is Null Then
'Servicing stack update from September missing'
When MonthlyRollups.AssetID Is Null Then 'Monthly rollup patch missing'
Else ''
End As [Missing Patches],
Convert(nvarchar,DateDiff(DAY, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
Case
When (sha2patch.AssetID Is Not Null) And (SSUMarApr.AssetID Is Not Null) And
(SSUSep.AssetID Is Not Null) And (MonthlyRollups.AssetID Is Not Null) 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 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4474419') As sha2patch On
sha2patch.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4493730', 'KB4490628')) As
SSUMarApr On SSUMarApr.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4516655', 'KB4517134')) As
SSUSep On SSUSep.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4519976', 'KB4519972',
'KB4525235', 'KB4525251', 'KB4530734', 'KB4520002', 'KB4520015',
'KB4525234', 'KB4525244', 'KB4530695')) As MonthlyRollups On
MonthlyRollups.AssetID = tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Distinct 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 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 Like 'Win 7%' Or tsysOS.OSname Like 'Win 2008 R2'
Or tsysOS.OSname Like 'Win 2008') And tblAssetCustom.State = 1 And
tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.AssetName
