Jetzt Ausprobieren

Extended Security Update Readiness Audit

Operating System Security

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. Windows Extended Security Update

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

Show

Hide