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 Report creation based on FilePathFull for Installation
by  Pang  
Go to last post Go to first unread
Last post: Yesterday at 2:50:23 PM(UTC)
Lansweeper Software Version Compatibility Report (Solved)
by  RC62N   Go to last post Go to first unread
Last post: 7/13/2020 5:04:10 PM(UTC)
Lansweeper Reports Email Question
by  muffintopman  
Go to last post Go to first unread
Last post: 7/13/2020 3:23:23 PM(UTC)
Lansweeper Ripple20
by  Hendrik.VE  
Go to last post Go to first unread
Last post: 7/8/2020 8:09:35 AM(UTC)
Lansweeper All USB devices connected
by  earmor   Go to last post Go to first unread
Last post: 7/7/2020 1:35:37 PM(UTC)
Lansweeper Software audit with installation date
by  RC62N  
Go to last post Go to first unread
Last post: 7/3/2020 3:39:41 PM(UTC)