.NET Framework EOL Audit

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Version,
tblAssets.SP,
Case
When TsysLastscan.Lasttime < GetDate() - 1 Then
'Last registry scan more than 24 hours ago! Information may not be up-to-date. Try rescanning this machine.'
End As Comment,
Case
When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
Then 'Yes'
Else 'No'
End As [RegKey Found],
SubQuery1.[.NET Framework Version] As [.NET Framework Version RegKey],
Case
When SubQuery1.[.NET Framework Version] Like '%4.5%' Then '2016-01-12'
When SubQuery1.[.NET Framework Version] Like '%4.6%' Then '2022-04-26'
End As [EOL Date],
subquery2.softwarename As [.Net Framework Software],
subquery2.[EOL Date Software],
Case
When GetDate() < subquery2.[EOL Date Software] Then Cast(DateDiff(DAY,
GetDate(), subquery2.[EOL Date Software]) As NVARCHAR) + ' days remaining'
End As [Days Remaining],
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
TsysLastscan.Lasttime As LastRegistryScan,
SubQuery1.Lastchanged,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When ((SubQuery1.[.NET Framework Version] Like '%4.5' Or
SubQuery1.[.NET Framework Version] Like '%4.5.1') And
GetDate() >= DateAdd(month, -1, '2016-01-12')) Or
((SubQuery1.[.NET Framework Version] Like '%4.5.2' Or
SubQuery1.[.NET Framework Version] Like '%4.6.1' Or
SubQuery1.[.NET Framework Version] Like '%4.6') And GetDate() >=
DateAdd(month, -1, '2022-04-26')) Then '#ffd152'
When ((SubQuery1.[.NET Framework Version] Like '%4.5' Or
SubQuery1.[.NET Framework Version] Like '%4.5.1') And
GetDate() < '2016-01-12') Or
((SubQuery1.[.NET Framework Version] Like '%4.5.2' Or
SubQuery1.[.NET Framework Version] Like '%4.6.1' Or
SubQuery1.[.NET Framework Version] Like '%4.6') And GetDate() <
'2022-04-26') Then '#d4f4be'
When GetDate() > subquery2.[EOL Date Software] Then '#d4f4be'
When GetDate() >= DateAdd(month, -1, subquery2.[EOL Date Software]) Then
'#ffd152'
When (SubQuery1.[.NET Framework Version] Not Like '%4.5%' And
SubQuery1.[.NET Framework Version] Not Like '%4.6' And
SubQuery1.[.NET Framework Version] Not Like '%4.6.1') Then '#d4f4be'
Else '#ffadad'
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 Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select tblassets.assetid,
tblSoftwareUni.softwarename,
Case
When tblSoftwareUni.softwareName Like '%.NET Framework 2.0%' Then
'2011-07-12'
When tblSoftwareUni.softwareName Like '%.NET Framework 3.0%' Then
'2011-07-12'
When tblSoftwareUni.softwareName Like '%.NET Framework 3.5%' Then
'2029-01-09'
When tblSoftwareUni.softwareName Like '%.NET Framework 4 %' Then
'2016-01-12'
When tblSoftwareUni.softwareName Like '%.NET Framework 4.5 %' Then
'2016-01-12'
When tblSoftwareUni.softwareName Like '%.NET Framework 4.5.1%'
Then '2016-01-12'
When tblSoftwareUni.softwareName Like '%.NET Framework 4.5.2%'
Then '2022-04-26'
When tblSoftwareUni.softwareName Like '%.NET Framework 4.6 %' Then
'2022-04-26'
When tblSoftwareUni.softwareName Like '%.NET Framework 4.6.1%'
Then '2022-04-26'
End As [EOL Date Software]
From tblassets
Inner Join tblSoftware On tblassets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%.NET Framework%' And
tblSoftwareUni.softwareName Not Like '%core%') As subquery2 On
subquery2.assetid = tblAssets.assetid
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
Case
When Cast(tblRegistry.Value As bigint) >= 528040 Then
'.NET Framework 4.8'
When Cast(tblRegistry.Value As bigint) >= 461808 Then
'.NET Framework 4.7.2'
When Cast(tblRegistry.Value As bigint) >= 461308 Then
'.NET Framework 4.7.1'
When Cast(tblRegistry.Value As bigint) >= 460798 Then
'.NET Framework 4.7'
When Cast(tblRegistry.Value As bigint) >= 394802 Then
'.NET Framework 4.6.2'
When Cast(tblRegistry.Value As bigint) >= 394254 Then
'.NET Framework 4.6.1'
When Cast(tblRegistry.Value As bigint) >= 393295 Then
'.NET Framework 4.6'
When Cast(tblRegistry.Value As bigint) >= 379893 Then
'.NET Framework 4.5.2'
When Cast(tblRegistry.Value As bigint) >= 378675 Then
'.NET Framework 4.5.1'
When Cast(tblRegistry.Value As bigint) >= 378389 Then
'.NET Framework 4.5'
End As [.NET Framework Version],
tblRegistry.Lastchanged
From tblRegistry
Where
tblRegistry.Regkey Like
'%HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full'
And tblRegistry.Valuename = 'Release') SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName