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'
