VMware vCenter Server EOL Audit

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblVmwareProductInfo.Version,
tblVmwareProductInfo.Build,
subquery1.EOLDate As 'End of General Support',
Case
When GetDate() < subquery1.EOLDate Then Cast(DateDiff(DAY, GetDate(),
subquery1.EOLDate) As NVARCHAR) + ' days remaining'
When GetDate() > subquery1.EOLDate Then 'EOL'
End As [EOL Days Remaining],
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When GetDate() > subquery1.EOLDate Then '#ffadad'
When GetDate() >= DateAdd(month, -1, subquery1.EOLDate) Then '#ffd152'
When GetDate() < subquery1.EOLDate Then '#d4f4be'
Else ''
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
Left Join tblVmwareVcenters On tblAssets.AssetID = tblVmwareVcenters.AssetID
Left Join tblVmwareProductInfo On tblVmwareVcenters.VcenterID =
tblVmwareProductInfo.VCenterID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select
Case
When tblVmwareProductInfo.Version Like '8.%' Then '2027-10-11'
When tblVmwareProductInfo.Version Like '7.%' Then '2025-10-02'
When tblVmwareProductInfo.Version Like '6.7%' Then '2022-10-15'
When tblVmwareProductInfo.Version Like '6.5%' Then '2022-10-15'
When tblVmwareProductInfo.Version Like '6.0%' Then '2020-03-12'
When tblVmwareProductInfo.Version Like '5.5%' Then '2018-09-19'
End As EOLDate,
tblassets.AssetID
From tblassets
Left Join tblVmwareVcenters On tblAssets.AssetID = tblVmwareVcenters.AssetID
Left Join tblVmwareProductInfo On tblVmwareVcenters.VcenterID =
tblVmwareProductInfo.VCenterID)
As subquery1 On
subquery1.AssetID = tblAssets.AssetID
Where tsysAssetTypes.Assettypename = 'VMware vCenter server'
Order by version