Red Hat Enterprise Linux End of Life Audit

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblLinuxSystem.OSRelease,
subquery2.[Maximum Supported Date],
subquery2.[Release Support (6 months)],
subquery2.[Maintenance Support],
subquery2.[Extended Update Support],
subquery2.[Update Services for SAP Solutions],
Case
When GetDate() <= subquery2.[Maximum Supported Date] Then
Cast(DateDiff(DAY, GetDate(), subquery2.[Maximum Supported Date]) As
nvarchar) + ' days remaining'
End As [Days Remaining],
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When GetDate() >= subquery2.[Release Support (6 months)] Then '#ffdc7a'
When GetDate() >= subquery2.[Maintenance Support] Then '#ffdc7a'
When GetDate() >= subquery2.[Extended Update Support] Then '#ffdc7a'
When GetDate() >= subquery2.[Update Services for SAP Solutions] Then
'#ffdc7a'
When GetDate() >= subquery2.[Maximum Supported Date] Then '#ffadad'
Else '#d4f4be'
End As backgroundcolor
From tblAssets
Left Join (Select Case
When subquery1.[Update Services for SAP Solutions] Is Not Null
Then subquery1.[Update Services for SAP Solutions]
When subquery1.[Extended Update Support] Is Not Null Then
subquery1.[Extended Update Support]
When subquery1.[Maintenance Support] Is Not Null Then
subquery1.[Maintenance Support]
When subquery1.[Release Support] Is Not Null Then Cast(DateAdd(month, 6,
subquery1.[Release Support]) As DATE)
End As [Maximum Supported Date],
subquery1.AssetID,
subquery1.[Update Services for SAP Solutions],
subquery1.[Release Support],
subquery1.[Maintenance Support],
subquery1.[Extended Update Support],
Cast(DateAdd(month, 6, subquery1.[Release Support]) As DATE) As
[Release Support (6 months)]
From (Select tblLinuxSystem.AssetID,
Case
When tblLinuxSystem.OSRelease Like '%7.1%' Then '2017-03-31'
When tblLinuxSystem.OSRelease Like '%7.2%' Then '2017-11-30'
When tblLinuxSystem.OSRelease Like '%7.3%' Then '2018-11-30'
When tblLinuxSystem.OSRelease Like '%7.4%' Then '2019-08-31'
When tblLinuxSystem.OSRelease Like '%7.5%' Then '2020-04-30'
When tblLinuxSystem.OSRelease Like '%7.6%' Then '2021-05-31'
When tblLinuxSystem.OSRelease Like '%7.7%' Then '2021-08-31'
When tblLinuxSystem.OSRelease Like '%7.9%' Then '2028-06-30'
When tblLinuxSystem.OSRelease Like '%8.1%' Then '2021-11-30'
When tblLinuxSystem.OSRelease Like '%8.2%' Then '2022-04-30'
When tblLinuxSystem.OSRelease Like '%8.4%' Then '2023-05-31'
When tblLinuxSystem.OSRelease Like '%8.6%' Then '2024-05-31'
When tblLinuxSystem.OSRelease Like '%8.8%' Then '2025-05-31'
When tblLinuxSystem.OSRelease Like '%9.0%' Then '2024-05-31'
When tblLinuxSystem.OSRelease Like '%9.2%' Then '2025-05-31'
When tblLinuxSystem.OSRelease Like '%8.10%' Then '2032-05-31'
When tblLinuxSystem.OSRelease Like '%9.10%' Then '2035-05-31'
End As [Extended Update Support],
Case
When tblLinuxSystem.OSRelease Like '%7.8%' Then '2020-09-30'
When tblLinuxSystem.OSRelease Like '%7.9%' Then '2024-06-30'
When tblLinuxSystem.OSRelease Like '%8.10%' Then '2029-05-31'
When tblLinuxSystem.OSRelease Like '%9.10%' Then '2032-05-31'
End As [Maintenance Support],
Case
When tblLinuxSystem.OSRelease Like '%7.2%' Then '2019-11-30'
When tblLinuxSystem.OSRelease Like '%7.3%' Then '2020-11-30'
When tblLinuxSystem.OSRelease Like '%7.4%' Then '2021-08-31'
When tblLinuxSystem.OSRelease Like '%7.6%' Then '2022-10-31'
When tblLinuxSystem.OSRelease Like '%7.7%' Then '2023-08-30'
When tblLinuxSystem.OSRelease Like '%8.0%' Then '2020-12-31'
When tblLinuxSystem.OSRelease Like '%8.1%' Then '2023-11-30'
When tblLinuxSystem.OSRelease Like '%8.2%' Then '2024-04-30'
When tblLinuxSystem.OSRelease Like '%8.4%' Then '2025-05-30'
When tblLinuxSystem.OSRelease Like '%8.6%' Then '2026-05-31'
When tblLinuxSystem.OSRelease Like '%8.8%' Then '2027-05-31'
When tblLinuxSystem.OSRelease Like '%9.0%' Then '2026-05-31'
When tblLinuxSystem.OSRelease Like '%9.2%' Then '2027-05-31'
End As [Update Services for SAP Solutions],
Case
When tblLinuxSystem.OSRelease Like '%8.0%' Then '2019-05-07'
When tblLinuxSystem.OSRelease Like '%8.1%' Then '2019-11-06'
When tblLinuxSystem.OSRelease Like '%8.2%' Then '2020-04-21'
When tblLinuxSystem.OSRelease Like '%8.3%' Then '2021-05-31'
When tblLinuxSystem.OSRelease Like '%8.4%' Then '2021-05-18'
When tblLinuxSystem.OSRelease Like '%8.5%' Then '2022-05-31'
When tblLinuxSystem.OSRelease Like '%8.7%' Then '2023-05-31'
When tblLinuxSystem.OSRelease Like '%8.9%' Then '2024-05-14'
When tblLinuxSystem.OSRelease Like '%8.10%' Then '2024-05-31'
When tblLinuxSystem.OSRelease Like '%9.1%' Then '2023-05-15'
When tblLinuxSystem.OSRelease Like '%9.3%' Then '2024-05-08'
When tblLinuxSystem.OSRelease Like '%9.10%' Then '2027-05-31'
End As [Release Support]
From tblLinuxSystem
Inner Join tblassets On tblassets.AssetID = tblLinuxSystem.AssetID) As
subquery1) As subquery2 On subquery2.AssetID = tblAssets.AssetID
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.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
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblLinuxSystem.OSRelease Like '%Red Hat Enterprise%' And
tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName