SQL Server End of Life Audit

SQL Server EOL Audit

Microsoft outlines end of life dates for all of their products to make customers aware of when they will cease support for specific products. SQL Server is no exception to this. Each version of SQL server usually has both an end of support date and an end of extended support date. At the end of mainstream support, Microsoft will no longer provide non-security hotfixes unless you have an extended support agreement. All warranty claims end and they will no longer accept feature and change requests so we advise you to audit your SQL servers to see which ones are going EOL soon.

This report provides an overview of all the SQL Server installations in your network along with an indication of whether they have surpassed their extended support date. After the extended support date has passed, your SQL Servers might no longer be secure as they will not receive security updates. Therefore it is highly recommended that if you have a SQL server version that is end of life, you update it as soon as possible.

SQL 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,
  tsysOS.OSname As OS,
  tblSqlServers.displayVersion As Edition,
  tblSqlServers.skuName As License,
  tblSqlServers.spLevel As SQLServicePack,
  tblSqlServers.version,
  Case when tblSqlServers.version LIKE '15%' then '2030-01-08'
  when tblSqlServers.version LIKE '14%' then '2027-10-12'
  when tblSqlServers.version LIKE '13%' and tblSqlServers.spLevel = '0' then 'EOL Reached'
    when tblSqlServers.version LIKE '13%' and tblSqlServers.spLevel = '1' then 'EOL Reached'
  when tblSqlServers.version LIKE '13%' and tblSqlServers.spLevel = '2' then '2026-07-14'
  when tblSqlServers.version LIKE '12%' and tblSqlServers.spLevel = '0' then 'EOL Reached'
    when tblSqlServers.version LIKE '12%' and tblSqlServers.spLevel = '2' then '2020-01-14'
	  when tblSqlServers.version LIKE '12%' and tblSqlServers.spLevel = '3' then '2024-07-09'
    when tblSqlServers.version LIKE '11%' and tblSqlServers.spLevel = '4' then '2022-07-12'
	  when tblSqlServers.version LIKE '11%' and tblSqlServers.spLevel <> '4' then 'EOL Reached'
When Cast(SubString(tblSqlServers.version, 0, CharIndex('.',
      tblSqlServers.version)) As INT) < 11 Then 'EOL Reached'
  end as [EOL Status],
  tblSqlServers.lastChanged as SQLLastChanged,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case when tblSqlServers.version LIKE '15%' and getdate() < '2030-01-08' then '#d4f4be'
  when tblSqlServers.version LIKE '14%' and getdate() < '2027-10-12' then '#d4f4be'
  when tblSqlServers.version LIKE '13%' and tblSqlServers.spLevel = '0' then '#ffadad'
    when tblSqlServers.version LIKE '13%' and tblSqlServers.spLevel = '1' then '#ffadad'
  when tblSqlServers.version LIKE '13%' and tblSqlServers.spLevel = '2' and getdate() < '2026-07-14' then '#d4f4be'
  when tblSqlServers.version LIKE '12%' and tblSqlServers.spLevel = '0' then '#ffadad'
    when tblSqlServers.version LIKE '12%' and tblSqlServers.spLevel = '2' and getdate() < '2020-01-14' then '#d4f4be'
	  when tblSqlServers.version LIKE '12%' and tblSqlServers.spLevel = '3' and getdate() < '2024-07-09' then '#d4f4be'
    when tblSqlServers.version LIKE '11%' and tblSqlServers.spLevel = '4' and getdate() < '2022-07-12' then '#d4f4be'
	  when tblSqlServers.version LIKE '11%' and tblSqlServers.spLevel <> '4' then '#ffadad'
When Cast(SubString(tblSqlServers.version, 0, CharIndex('.',
      tblSqlServers.version)) As INT) < 11 Then '#ffadad'
  end as backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  Edition