90 days Windows Update Audits

[nateideal]

I'd like a report for both workstations and servers that shows those that have not installed any updates in over 90 days. Can anyone help me out? I tried modifying the Last Windows Update Audit report available but couldn't get it to just show me the 90+ day machines.

- Thanks

[moakyz03]

nateideal wrote:
I'd like a report for both workstations and servers that shows those that have not installed any updates in over 90 days. Can anyone help me out? I tried modifying the Last Windows Update Audit report available but couldn't get it to just show me the 90+ day machines.

- Thanks

This report is based on the Microsoft February 2022 Patch Tuesday Audit report with the added option of not installed Windows updates in 90 days:

Select Distinct Top 1000000 Coalesce(tsysOS.Image,
  tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblState.Statename As State,
  Case tblAssets.AssetID
    When SubQuery1.AssetID 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.Userdomain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Version,
  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.AssetID = SubQuery1.AssetID Then ''
    Else Case
        When tsysOS.OSname = 'Win 2008' Then 'KB5010384 or KB5010403'
        When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
          tsysOS.OSname = 'Win 2008 R2' Then 'KB5010404 or KB5010422'
        When tsysOS.OSname = 'Win 2012' Or
          tsysOS.OSname = 'Win 8' Then 'KB5010392 or KB5010412'
        When tsysOS.OSname = 'Win 8.1' Or
          tsysOS.OSname = 'Win 2012 R2' Then 'KB5010419 or KB5010395'
        When tsysOS.OScode Like '10.0.10240' Then 'KB5010358'
        When tsysOS.OScode Like '10.0.10586' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.14393' Or
          tsysOS.OSname = 'Win 2016' Then 'KB5010359'
        When tsysOS.OScode Like '10.0.15063' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.16299' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.17134' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not
          Like '%LTSC%' Then 'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like
          '%LTSC%' Then 'KB5010351'
        When tsysOS.OSname = 'Win 2019' Then 'KB5010351'
        When tsysOS.OScode Like '10.0.18362' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.18363' Then 'KB5010345'
        When tsysOS.OScode Like '10.0.19041' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.19042' Then 'KB5010342'
        When tsysOS.OScode Like '10.0.19043' Then 'KB5010342'
        When tsysOS.OScode Like '10.0.19044' Then 'KB5010342'
        When tsysOS.OSname = 'Win 2022' Then 'KB5010354'
        When tsysOS.OScode Like '10.0.22000%' Then 'KB5010386'
      End
  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,
  Case tblAssets.AssetID
    When SubQuery1.AssetID 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 Top 1000000 tblQuickFixEngineering.AssetID
    From tblQuickFixEngineering
      Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
          tblQuickFixEngineering.QFEID
    Where tblQuickFixEngineeringUni.HotFixID In ('KB5010384', 'KB5010403',
      'KB5010404', 'KB5010422', 'KB5010392', 'KB5010412', 'KB5010419',
      'KB5010395', 'KB5010358', 'KB5010359', 'KB5010351', 'KB5010345',
      'KB5010342', 'KB5010354', 'KB5010386')) As SubQuery1 On
      tblAssets.AssetID = SubQuery1.AssetID
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
      And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join (Select Distinct Top 1000000 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 Top 1000000 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 <> 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
  tsysOS.OSname Not Like '%2003%' And (Not tsysOS.OSname Like 'Win 7%'
    Or Not tblAssets.SP = 0) And tblAssetCustom.State = 1 And
  tsysAssetTypes.AssetTypename Like 'Windows%' And
  Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
  GetDate())) > 90
Order By tblAssets.Domain,
  tblAssets.AssetName