Internet Explorer 11 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,
tblOperatingsystem.Caption As OS,
tblAssets.Version,
tblAssets.SP,
subquery1.[IE11 EOL],
subquery1.[IE Mode EOL],
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
tblAssets.Lastseen,
tblAssets.Lasttried
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
Inner Join tblOperatingsystem On tblOperatingsystem.assetid =
tblAssets.assetid
Left Join (Select tblOperatingsystem.assetid,
Replace(Replace(ParseName(tsysOS.OScode, 1), 's', ''), 'R2',
'') As oscode2,
Case
When tsysOS.OSname = 'Win 8.1' Then 'Unaffected'
When tsysOS.OSname = 'Win 7' And tblassets.sp = 1 And
tblOperatingsystem.Caption Like '%Professional%' Then
'Unaffected with ESU'
When tsysOS.OSname = 'Win 7' And tblassets.sp = 1 And
tblOperatingsystem.Caption Like '%Enterprise%' Then
'Unaffected with ESU'
When tblComputersystem.Domainrole > 1 Then 'Unaffected'
When tsysOS.OSname = 'Win 10' And tblOperatingsystem.Caption Like
'%LTSC%' Then 'Unaffected'
When tsysOS.OSname = 'Win 10' And
Replace(Replace(ParseName(tsysOS.OScode, 1), 's', ''), 'R2',
'') >= 19042 Then '2022-06-15'
End As [IE11 EOL],
Case
When tblassets.Version = '20H2' And tblComputersystem.Domainrole > 1
Then '2022-05-10'
When tblassets.Version = '20H2' Then '2023-05-09'
When tblassets.Version = '2004' Then '2021-12-14'
When tblassets.Version = '1809' And tblOperatingsystem.Caption
Like '%LTSC%' Then '2029-01-09'
When tblassets.Version = '1809' And tblOperatingsystem.Caption
Like '%IoT%' Then '2029-01-09'
When tblassets.Version = '1809' And tblComputersystem.Domainrole > 1
Then '2029-01-09'
When tsysOS.OSname = 'Win 8.1' Then '2023-01-10'
When tsysOS.OSname = 'Win 7' And tblassets.sp = 1 And
tblOperatingsystem.Caption Like '%Professional%' Then '2023-01-15'
When tsysOS.OSname = 'Win 7' And tblassets.sp = 1 And
tblOperatingsystem.Caption Like '%Enterprise%' Then '2023-01-15'
When tblassets.Version = '21H1' Then '2022-12-13'
When tblassets.Version = '21H2' And tblOperatingsystem.Caption
Like '%LTSC%' Then '2027-01-12'
When tblassets.Version = '21H2' And tsysOS.OSname Not Like '%11%' Then
'2023-06-13'
End As [IE Mode EOL]
From tblOperatingsystem
Inner Join tblassets On tblOperatingsystem.assetid = tblassets.assetid
Inner Join tblComputersystem On tblComputersystem.AssetID =
tblassets.AssetID
Left Join tsysOS On tsysOS.OScode = tblassets.OScode
Where tsysOS.OScode Is Not Null) As subquery1 On subquery1.AssetID =
tblAssets.AssetID
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
Where tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName