Windows Server End of Life Audit
Operating System SecurityDiscover the Server End of Life Status of Your Environment
Microsoft regularly releases new versions of Windows Server. Unlike previous Windows Server versions, the new versions are released more frequently than they used to be. Releasing more versions frequently does have the benefit of being able to update and add features to Windows server, improving security, and ensuring that your network is adequately protected.
As is clear by now, tracking all of this can be a nightmare. Especially if you don’t even have a complete inventory to begin with. Luckily, the report below will be able to help here. After you’ve scanned your environment, the color-coded report will indicate which machines are EOL, which ones are nearing their EOL date (EOL date is within 12 months or after mainstream support), and which ones are still supported for some time. You can find more information on our Windows Server blog.
Windows Server End of Life Query
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblOperatingsystem.Caption As OS,
tblAssets.Version,
Case
When (tblOperatingsystem.Caption Like '%2000%' Or
tblOperatingsystem.Caption Like '%2003%' Or
tblOperatingsystem.Caption Like '%2008%') Then 'EOL'
When tblAssets.Version In ('1709', '1803', '1903') Then 'EOL'
When GetDate() >= subquery1.[EOL Date] And tblOperatingsystem.Caption Like
'%2012%' Then 'EOL'
When GetDate() >= subquery1.[EOL Date] And (tblAssets.Version = '1607' Or
tblOperatingsystem.Caption Like '%2016%') Then 'EOL'
When GetDate() >= subquery1.[EOL Date] And (tblAssets.Version = '1809' Or
tblOperatingsystem.Caption Like '%2019%') Then 'EOL'
When GetDate() >= subquery1.[EOL Date] And tblAssets.Version = '1909' Then
'EOL'
When GetDate() >= subquery1.[EOL Date] And tblAssets.Version = '2004' Then
'EOL'
When GetDate() >= subquery1.[EOL Date] And tblAssets.Version = '2009' Then
'EOL'
When GetDate() >= subquery1.[EOL Date] And tblAssets.Version = '20H2' Then
'EOL'
When GetDate() >= subquery1.[EOL Date] And tblAssets.Version = '21H2' Then
'EOL'
When GetDate() < subquery1.[EOL Date] And tblOperatingsystem.Caption Like
'%2012%' Then Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As
nvarchar) + ' days remaining'
When GetDate() < subquery1.[EOL Date] And (tblAssets.Version = '1607' Or
tblOperatingsystem.Caption Like '%2016%') Then Cast(DateDiff(DAY,
GetDate(), subquery1.[EOL Date]) As nvarchar) + ' days remaining'
When GetDate() < subquery1.[EOL Date] And (tblAssets.Version = '1809' Or
tblOperatingsystem.Caption Like '%2019%') Then Cast(DateDiff(DAY,
GetDate(), subquery1.[EOL Date]) As nvarchar) + ' days remaining'
When GetDate() < subquery1.[EOL Date] And tblAssets.Version = '1909' Then
Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As nvarchar) +
' days remaining'
When GetDate() < subquery1.[EOL Date] And tblAssets.Version = '2004' Then
Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As nvarchar) +
' days remaining'
When GetDate() < subquery1.[EOL Date] And tblAssets.Version = '2009' Then
Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As nvarchar) +
' days remaining'
When GetDate() < subquery1.[EOL Date] And tblAssets.Version = '20H2' Then
Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As nvarchar) +
' days remaining'
When GetDate() < subquery1.[EOL Date] And tblAssets.Version = '21H2' Then
Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As nvarchar) +
' days remaining'
Else 'Supported'
End As Status,
subquery1.[EOL Date],
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When GetDate() >= subquery1.[EOL Date] Then '#ffadad'
When GetDate() >= subquery1.[Mainstream Support End Date] Then '#ffd152'
When GetDate() >= DateAdd(month, -12, subquery1.[EOL Date]) Then '#ffd152'
Else '#d4f4be'
End As backgroundcolor
From tblAssets
Left Join (Select tblOperatingsystem.AssetID,
Case
When tblOperatingsystem.Caption Like '%2000%' Then '2010-07-13'
When tblOperatingsystem.Caption Like '%2003%' Then '2015-07-14'
When tblOperatingsystem.Caption Like '%2008%' Then '2020-01-14'
When tblOperatingsystem.Caption Like '%2012%' Then '2023-10-10'
When tblassets.Version = '1607' Or
tblOperatingsystem.Caption Like '%2016%' Then '2027-01-12'
When tblassets.Version = '1809' Or
tblOperatingsystem.Caption Like '%2019%' Then '2029-01-09'
When tblassets.Version = '1903' Then '2020-12-08'
When tblassets.Version = '1909' Then '2021-05-11'
When tblassets.Version = '2004' Then '2021-12-14'
When tblassets.Version = '2009' Then '2022-05-10'
When tblassets.Version = '21H2' Then '2031-10-14'
End As [EOL Date],
Case
When tblassets.Version = '1607' Then '2022-01-11'
When tblassets.Version = '1809' Then '2024-01-09'
When tblassets.Version = '21H2' Then '2026-10-13'
End As [Mainstream Support End Date]
From tblOperatingsystem
Inner Join tblassets On tblassets.AssetID = tblOperatingsystem.AssetID) As
subquery1 On subquery1.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
Where tblState.Statename = 'Active' And tblComputersystem.Domainrole > 1
Order By tblAssets.Domain,
tblAssets.AssetName