List SQL Servers and Their End-Of-Life Dates
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. You can read more about this use case in the SQL Server EOL blog post.
SQL Server End of Life Query
Select Distinct Top 1000000 tblAssets.AssetID, tblAssets.AssetName, tblAssets.Domain, tsysAssetTypes.AssetTypename As AssetType, tblAssets.Username, tblAssets.Userdomain, tsysAssetTypes.AssetTypeIcon10 As icon, tblAssets.IPAddress, tsysIPLocations.IPLocation, tblAssetCustom.Manufacturer, tblAssetCustom.Model, tsysOS.OSname As OS, tblAssets.Version as OSVersion, tblAssets.SP, subquery1.Edition, subquery1.License, subquery1.SQLServicePack, subquery1.version, subquery1.EOLDate, Case When IsDate(subquery1.EOLDate) = 0 then '' when IsDate(subquery1.EOLDate) = 1 And GetDate() < subquery1.EOLDate Then Cast(DateDiff(DAY, GetDate(), subquery1.EOLDate) As NVARCHAR) + ' days remaining' End As [Days Remaining], tblAssets.Lastseen, tblAssets.Lasttried, Case When subquery1.EOLDate = 'EOL' Then '#ffadad' When IsDate(subquery1.EOLDate) = 1 And GetDate() > subquery1.EOLDate Then '#ffadad' When IsDate(subquery1.EOLDate) = 1 And GetDate() >= DateAdd(month, -1, subquery1.EOLDate) Then '#ffd152' Else '#d4f4be' End As backgroundcolor From tblAssets 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 Left Join tsysOS On tsysOS.OScode = tblAssets.OScode inner Join (Select tblSqlServers.displayVersion As Edition, tblSqlServers.skuName As License, tblSqlServers.spLevel As SQLServicePack, tblSqlServers.version, Case When Cast(ParseName(tblSqlServers.version, 4) As int) < 11 Then 'EOL' 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' when tblSqlServers.version LIKE '13%' and tblSqlServers.spLevel = '1' then 'EOL' when tblSqlServers.version LIKE '13%' and tblSqlServers.spLevel = '2' then '2022-10-11' when tblSqlServers.version LIKE '13%' and tblSqlServers.spLevel = '3' then '2026-07-14' when tblSqlServers.version LIKE '12%' and tblSqlServers.spLevel = '0' then 'EOL' when tblSqlServers.version LIKE '12%' and tblSqlServers.spLevel = '1' then '2017-10-10' 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' End As EOLDate, tblSqlServers.AssetID From tblSqlServers) As subquery1 On subquery1.AssetID = tblAssets.AssetID Where tblState.Statename = 'Active'
