Windows 10 1703 EOL Report

[Liamsy11]

I need to be able to filter out all of the Windows 10 1607 LTSB devices from the below report. These devices are not actually out of support due to them being LTSB. I know its probably a really simple thing to do but I have absolutely no idea when it comes to SQL.

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,
Case
When tblAssets.Version < '1703' Then 'EOL'
When tblAssets.Version = '1703' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When tblAssets.Version = '1703' And (tblOperatingsystem.Caption Like
'%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then 'EOL in ' + EOL.Days +
' days'
When EOL.Days Is Null Then 'Rescan asset'
End As Status,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When EOL.Days > 0 And EOL.Days <= 90 Then '#ffd152'
When EOL.Days <= 0 Then '#ffadad'
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
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblAssets.Version < '1703' Then '-1'
When tblAssets.Version = '1703' And (tblOperatingsystem.Caption Like
'%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then
Cast(DateDiff(DAY, GetDate(), '2019-10-08') As nvarchar)
When tblAssets.Version = '1703' And (tblOperatingsystem.Caption Like
'%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then Cast(DateDiff(DAY,
GetDate(), '2018-10-09') As nvarchar)
End As Days
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblState.Statename = 'Active' And tsysOS.OSname = 'Win 10'
And tblAssets.Version <= '1703') As EOL On
EOL.AssetID = tblAssets.AssetID
Where tblAssets.Version <= '1703' And tblState.Statename = 'Active' And
tsysOS.OSname = 'Win 10'
Order By tblAssets.Domain,
tblAssets.AssetName

[Esben_D]

I'm actually working on something more robust for all W10 versions that includes LTSB versions. It should be up in the report library soon. So you can keep an eye out for that.

The gist of it is that if you want to show LTSB version differently, then use:

tblOperatingsystem.Caption Like '%LTSB%'

For example, to exclude LTSB lower than 1703 from being marked EOL:

tblAssets.Version,
Case
When tblAssets.Version < '1703' AND tblOperatingsystem.Caption NOT Like '%LTSB%' Then 'EOL'

Then you still need to set something up for what they should be marked as:

When tblAssets.Version < '1703' AND tblOperatingsystem.Caption Like '%LTSB%' Then 'Not EOL'

These are the very basic ways to adjust the report. To also show the days remaining and color highlights you would have to do some more modifications.