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.Software,
subquery1.Version,
subquery1.Publisher,
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.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
Left Join (Select tblSoftwareUni.softwareName As Software,
tblsoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
Case
When tblSoftwareUni.softwareName Like '%Acrobat%DC%' Then 'Supported'
When tblsoftwareUni.softwareName Like '%Acrobat%Classic%'Then '2029-07-15'
When tblsoftwareUni.softwareName Like '%Acrobat%2024%'Then '2029-07-15'
When tblsoftwareUni.softwareName Like '%Acrobat%2020%'Then '2025-11-30'
When tblsoftwareUni.softwareName Like '%Acrobat%2017%'Then '2022-06-06'
When tblsoftwareUni.softwareName Like '%Acrobat%2015%'Then '2020-07-07'
When tblsoftwareUni.softwareName Like '%Acrobat%'
and Cast(ParseName(tblsoftware.softwareVersion, 3) As int) > 20
And (tblsoftwareUni.softwareName not Like '%2015%'
or tblsoftwareUni.softwareName not Like '%2017%'
or tblsoftwareUni.softwareName not Like '%2020%'
or tblsoftwareUni.softwareName not Like '%2024%'
or tblsoftwareUni.softwareName not Like '%Classic%') Then 'Supported'
End As EOLDate,
tblsoftware.AssetID
From tblsoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblsoftware.softID
Inner Join tblAssets On tblAssets.AssetID = tblsoftware.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
tblSoftwareUni.softwareName Like '%Acrobat%'
And tblSoftwareUni.SoftwarePublisher Like '%Adobe%')
As subquery1 On subquery1.AssetID = tblAssets.AssetID
Where (subquery1.Software Like '%Acrobat%'
And subquery1.Publisher Like '%Adobe%'
and tblState.Statename = 'Active')