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: 4
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,956  
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 HP Warranty scan - broken for some products
by  M Redfern   Go to last post Go to first unread
Last post: Today at 12:30:44 PM(UTC)
Lansweeper Worked time
by  Imrane DESSAI  
Go to last post Go to first unread
Last post: Today at 7:14:40 AM(UTC)
Lansweeper Adding Owner/User information to Assets:All column report
by  ssmarr5   Go to last post Go to first unread
Last post: Today at 12:15:01 AM(UTC)
Lansweeper Send users email about low disk space
by  DontByteMe  
Go to last post Go to first unread
Last post: Yesterday at 10:02:43 PM(UTC)
Lansweeper Can reports be directed to a file server
by  RKCar   Go to last post Go to first unread
Last post: Yesterday at 9:40:18 PM(UTC)
Lansweeper Monitor Model- Generic PnP Monitor
by  Roger D.  
Go to last post Go to first unread
Last post: Yesterday at 7:22:01 PM(UTC)
Lansweeper Are Deleted Dashboard Tabs Retrievable?
by  Rob-CD   Go to last post Go to first unread
Last post: Yesterday at 4:04:45 PM(UTC)
Lansweeper Assets Not Seen in 90 Days not Automatically becoming Inactive
by  Rob-CD  
Go to last post Go to first unread
Last post: Yesterday at 4:00:00 PM(UTC)