Notification

Icon
Error

Windows 10 1703 EOL Report - Not sure how to edit the below report to filter out any Windows 10 1607 LTSB devices

Posted: Tuesday, October 29, 2019 1:03:54 PM(UTC)
Liamsy11

Liamsy11

Member Original PosterPosts: 7
0
Like
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
#1Esben.D Member Administration Posts: 1,982  
posted: 10/29/2019 1:53:03 PM(UTC)
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:

Code:
tblOperatingsystem.Caption Like '%LTSB%'


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

Code:
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:

Code:
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.

Active Discussions

Lansweeper "add cc user" doesn't show the correct listings
by  JLPingree   Go to last post Go to first unread
Last post: Yesterday at 8:12:44 PM(UTC)
Lansweeper Default User Date Format
by  RickW99456  
Go to last post Go to first unread
Last post: Yesterday at 5:00:51 PM(UTC)
Lansweeper Duplicate assets (Servers)
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 3:20:28 PM(UTC)
Lansweeper Lansweeper load the disk subsystem
by  Alexey Gorbachev  
Go to last post Go to first unread
Last post: Yesterday at 3:13:17 PM(UTC)
Lansweeper Lost Configuration tab (Admin rights)
by  kspap   Go to last post Go to first unread
Last post: Yesterday at 10:30:12 AM(UTC)
Lansweeper Deployment with different user rights
by  Jupiter_IT  
Go to last post Go to first unread
Last post: Yesterday at 9:39:20 AM(UTC)
Lansweeper cisco fuji device not linking with connected devices
by  char   Go to last post Go to first unread
Last post: 7/5/2020 9:12:07 AM(UTC)
Lansweeper Microsoft CVE-2020-1425
by  Richard_B  
Go to last post Go to first unread
Last post: 7/3/2020 4:29:41 PM(UTC)