Windows 10 End of Life Audit

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption As OS,
tblAssets.Version,
subquery1.EOLDate,
Case
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.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
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 tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.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
Left Join (Select tblassets.AssetID,
Case
When tblassets.Version In ('1511','1703','1709','1803','1903','1909',
'2004','20H1','21H1') Then 'EOL'
When tblassets.Version = '1507' And tblOperatingsystem.Caption
Like '%LTSB%' Then '2025-10-14'
When tblassets.Version = '1507' And tblOperatingsystem.Caption
not Like '%LTSB%' Then 'EOL'
When tblassets.Version = '1607' And tblOperatingsystem.Caption
Like '%LTSB%' Then '2026-10-13'
When tblassets.Version = '1607' And tblOperatingsystem.Caption
not Like '%LTSB%' Then 'EOL'
When tblassets.Version = '1809' And tblOperatingsystem.Caption
Like '%LTSC%' Then '2029-01-09'
When tblassets.Version = '1809' And tblOperatingsystem.Caption
not Like '%LTSC%' Then 'EOL'
When tblassets.Version = '21H2' And tblOperatingsystem.Caption
Like '%LTSC%' Then '2027-01-12'
When tblassets.Version = '20H2' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%' Or
tblOperatingsystem.Caption Like '%Business%') Then 'EOL'
When tblassets.Version = '20H2' And (tblOperatingsystem.Caption Like
'%Education%' Or
tblOperatingsystem.Caption Like '%Ent__prise%') Then '2023-05-09'
When tblassets.Version = '21H2' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%' Or
tblOperatingsystem.Caption Like '%Business%') Then '2023-06-13'
When tblassets.Version = '21H2' And (tblOperatingsystem.Caption Like
'%Education%' Or
tblOperatingsystem.Caption Like '%Ent__prise%') Then '2024-06-11'
When tblassets.Version = '22H2' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%' Or
tblOperatingsystem.Caption Like '%Business%') Then '2025-10-14'
When tblassets.Version = '22H2' And (tblOperatingsystem.Caption Like
'%Education%' Or
tblOperatingsystem.Caption Like '%Ent__prise%') Then '2025-10-14'
End As EOLDate
From tblassets
Inner Join tblOperatingsystem On
tblassets.AssetID = tblOperatingsystem.AssetID) As subquery1
On subquery1.AssetID = tblAssets.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%' And tblState.Statename =
'Active'