cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Andy_Sismey
Champion Sweeper III
Hi,

Has anyone written a report with a break down of OS (count) and the build versions , where the build versions are URLS and and can be clicked to give the breakdown of assets ?

I tried using LINKS but the query path changes in SQL and adds '%2b' to the query

 'Build Version :' + tblOperatingsystem.Version As hyperlink_name_Link1,
'http://server/Report/report.aspx?det=Web50GetOSBuild&@OS=[Win+10]+@build=' + tblOperatingsystem.Version + '&title=[Win+10]+computers+with+build' + tblOperatingsystem.Version As hyperlink_Link1,


WORKING
http://server/Report/report.aspx?det=Web50GetOSBuild&@OS=Win+10&@build=10.0.18363.1379&title=Win+10+computers+with+build+10.0.18363.1379

REPORT VERSION
http://server/Report/report.aspx?det=Web50GetOSBuild&@OS=Win%2b10%5d%2b%40build=10.0.18363&title=%5bWin%2b10%5d%2bcomputers%2bwith%2bbuild10.0.18363

Cheers

A
1 ACCEPTED SOLUTION
Hendrik_VE
Champion Sweeper III
I use (and manage) this hell of a report that reports all my assets, the exact build they have, the End of Life date and the days until EOL.
Report is exported to Power BI where I created a nice clickable pie diagram with all builds, or all assets which are EOL (or approach their EOL date).
Beware, this shows only the OS versions/Win10 editions which we have currently installed (but I believe that's most of them).

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom6 As 'System Administrator',
tblAssets.Domain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblOperatingsystem.Caption As OS,
tblOperatingsystem.Version,
Case tblOperatingsystem.Version
When '10.0.10240' Then '1507'
When '10.0.10586' Then '1511'
When '10.0.14393' Then '1607'
When '10.0.15063' Then '1703'
When '10.0.16299' Then '1709'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
When '10.0.18363' Then '1909'
When '10.0.19041' Then '2004'
When '10.0.19042' Then '20H2'
Else ''
End As 'Win 10 Release',
Case
When tblOperatingsystem.Caption Like '%2000%' Then 'EOL'
When tblOperatingsystem.Version = '10.0.10240' And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When tblOperatingsystem.Version = '10.0.10586' Then 'EOL'
When tblOperatingsystem.Version = '10.0.14393' And
tblOperatingsystem.Caption Not Like '%LTS%' And
tblOperatingsystem.Caption Not Like '%2016%' Then 'EOL'
When tblOperatingsystem.Version = '10.0.15063' Then 'EOL'
When GetDate() >= '2019-04-09' And tblOperatingsystem.Version = '10.0.16299'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2020-10-13' And tblOperatingsystem.Version = '10.0.16299'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2019-11-12' And tblOperatingsystem.Version = '10.0.17134'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2020-11-10' And tblOperatingsystem.Version = '10.0.17134'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2020-11-10' And tblOperatingsystem.Version = '10.0.17763'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2021-05-11' And tblOperatingsystem.Version = '10.0.17763'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2020-12-08' And tblOperatingsystem.Version = '10.0.18362'
Then 'EOL'
When GetDate() >= '2021-05-11' And tblOperatingsystem.Version = '10.0.18363'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2022-05-10' And tblOperatingsystem.Version = '10.0.18363'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2021-12-14' And tblOperatingsystem.Version = '10.0.19041'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2022-05-10' And tblOperatingsystem.Version = '10.0.19042'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2023-05-09' And tblOperatingsystem.Version = '10.0.19042'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2020-01-14' And (tblOperatingsystem.Caption Like '%2008%'
Or tblOperatingsystem.Caption Like '%Windows 7%') Then 'EOL'
When GetDate() >= '2029-01-09' And tblOperatingsystem.Caption Like
'%Server 2019%' Then 'EOL'
When GetDate() >= '2027-01-12' And tblOperatingsystem.Caption Like
'%Server 2016%' Then 'EOL'
When GetDate() >= '2023-10-10' And tblOperatingsystem.Caption Like
'%Server 2012%' Then 'EOL'
When GetDate() >= '2023-01-10' And tblOperatingsystem.Caption Like
'%Windows 8%' Then 'EOL'
When GetDate() >= '2025-10-14' And tblOperatingsystem.Version = '10.0.10240'
And tblOperatingsystem.Caption Like '%LTS%' Then 'EOL'
When GetDate() >= '2026-10-13' And tblOperatingsystem.Version = '10.0.14393'
And tblOperatingsystem.Caption Like '%LTS%' Then 'EOL'
When GetDate() >= '2029-01-09' And tblOperatingsystem.Version = '10.0.17763'
And tblOperatingsystem.Caption Like '%LTS%' Then 'EOL'
When GetDate() >= '2020-10-13' And tblOperatingsystem.Version = '6.1.7601'
And tblOperatingsystem.Caption Like '%Embedded%' Then 'EOL'
When tblOperatingsystem.Caption Is Null Then 'Rescan Asset'
Else 'Supported'
End As Status,
Case
When GetDate() > '2019-01-01' And GetDate() < '2025-10-14' And
tblOperatingsystem.Version = '10.0.10240' And
tblOperatingsystem.Caption Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2025-10-14') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2026-10-13' And
tblOperatingsystem.Version = '10.0.14393' And
tblOperatingsystem.Caption Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2026-10-13') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2029-01-09' And
tblOperatingsystem.Version = '10.0.17763' And
tblOperatingsystem.Caption Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2029-01-09') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2019-10-08' And
tblOperatingsystem.Version = '10.0.15063' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2019-10-08') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-10-13' And
tblOperatingsystem.Version = '10.0.16299' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-10-13') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2019-11-12' And
tblOperatingsystem.Version = '10.0.17134' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2019-11-12') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-11-10' And
tblOperatingsystem.Version = '10.0.17134' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-11-10') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-11-10' And
tblOperatingsystem.Version = '10.0.17763' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-11-10') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2021-05-11' And
tblOperatingsystem.Version = '10.0.17763' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2021-05-11') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-12-08' And
tblOperatingsystem.Version = '10.0.18362' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-12-08') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2021-05-11' And
tblOperatingsystem.Version = '10.0.18363' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2021-05-11') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2022-05-10' And
tblOperatingsystem.Version = '10.0.18363' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2022-05-10') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2021-12-14' And
tblOperatingsystem.Version = '10.0.19041' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2021-12-14') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2022-05-10' And
tblOperatingsystem.Version = '10.0.19042' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2022-05-10') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2023-05-09' And
tblOperatingsystem.Version = '10.0.19042' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2023-05-09') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-01-14' And
(tblOperatingsystem.Caption Like '%2008%' Or
tblOperatingsystem.Caption Like '%Windows 7%') Then 'EOL in ' +
Cast(DateDiff(DAY, GetDate(), '2020-01-14') As nvarchar) + ' days'
When GetDate() > '2019-01-01' And GetDate() < '2027-01-12' And
tblOperatingsystem.Caption Like '%Server 2016%' Then 'EOL in ' +
Cast(DateDiff(DAY, GetDate(), '2027-01-12') As nvarchar) + ' days'
When GetDate() > '2019-01-01' And GetDate() < '2029-01-09' And
tblOperatingsystem.Caption Like '%Server 2019%' Then 'EOL in ' +
Cast(DateDiff(DAY, GetDate(), '2029-01-09') As nvarchar) + ' days'
When GetDate() > '2019-01-01' And GetDate() < '2023-10-10' And
tblOperatingsystem.Caption Like '%Server 2012%' Then 'EOL in ' +
Cast(DateDiff(DAY, GetDate(), '2023-10-10') As nvarchar) + ' days'
When GetDate() > '2019-01-01' And GetDate() < '2023-01-10' And
tblOperatingsystem.Caption Like '%Windows 8%' Then 'EOL in ' +
Cast(DateDiff(DAY, GetDate(), '2023-01-10') As nvarchar) + ' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-10-13' And
tblOperatingsystem.Caption Like '%Embedded%' And
tblOperatingsystem.Version = '6.1.7601' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-10-13') As nvarchar) +
' days'
End As 'Days until EOL',
Case
When tblOperatingsystem.Caption Like '%2000%' Then '2010-07-13'
When tblOperatingsystem.Version = '10.0.10240' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '2017-05-09'
When tblOperatingsystem.Version = '10.0.10586' Then '2017-10-10'
When tblOperatingsystem.Version = '10.0.14393' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2018-04-10'
When tblOperatingsystem.Version = '10.0.14393' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '2019-04-09'
When tblOperatingsystem.Version = '10.0.15063' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2018-10-09'
When tblOperatingsystem.Version = '10.0.15063' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2019-10-08'
When tblOperatingsystem.Version = '10.0.16299' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2019-04-09'
When tblOperatingsystem.Version = '10.0.16299' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2020-10-13'
When tblOperatingsystem.Version = '10.0.17134' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2019-11-12'
When tblOperatingsystem.Version = '10.0.17134' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2020-11-10'
When tblOperatingsystem.Version = '10.0.17763' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2020-11-10'
When tblOperatingsystem.Version = '10.0.17763' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '2021-05-11'
When tblOperatingsystem.Version = '10.0.18362' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2020-12-08'
When tblOperatingsystem.Version = '10.0.18362' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2020-12-08'
When tblOperatingsystem.Version = '10.0.18363' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2021-05-11'
When tblOperatingsystem.Version = '10.0.18363' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2022-05-10'
When tblOperatingsystem.Version = '10.0.19041' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '2021-12-14'
When tblOperatingsystem.Version = '10.0.19042' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2022-05-10'
When tblOperatingsystem.Version = '10.0.19042' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '2023-05-09'
When tblOperatingsystem.Version = '10.0.10240' And
(tblOperatingsystem.Caption Like '%LTS%') Then '2025-10-14'
When tblOperatingsystem.Version = '10.0.14393' And
(tblOperatingsystem.Caption Like '%LTS%') Then '2026-10-13'
When tblOperatingsystem.Version = '10.0.17763' And
(tblOperatingsystem.Caption Like '%LTS%') Then '2029-01-09'
When tblOperatingsystem.Caption Like '%2008%' Or
tblOperatingsystem.Caption Like '%Windows 7%' Then '2020-01-14'
When tblOperatingsystem.Caption Like '%Server 2019%' Then '2029-01-09'
When tblOperatingsystem.Caption Like '%Server 2016%' Then '2027-01-12'
When tblOperatingsystem.Caption Like '%Server 2012%' Then '2023-10-10'
When tblOperatingsystem.Caption Like '%Windows 8%' Then '2023-01-10'
When tblOperatingsystem.Caption Like '%Embedded%' And
tblOperatingsystem.Version = '6.1.7601' Then '2020-10-13'
End As [EOL Date],
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblOperatingsystem.Caption Like '%2000%' Then '#ffadad'
When tblOperatingsystem.Version Like '%10240%' And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When tblOperatingsystem.Version Like '%10240%' And
tblOperatingsystem.Caption Like '%LTS%' Then '#d4f4be'
When tblOperatingsystem.Version Like '%10586%' Then '#ffadad'
When tblOperatingsystem.Version = '10.0.14393' And
tblOperatingsystem.Caption Not Like '%LTS%' And
tblOperatingsystem.Caption Not Like '%2016%' Then '#ffadad'
When tblOperatingsystem.Version Like '%14393%' And
tblOperatingsystem.Caption Like '%LTS%' Then '#d4f4be'
When tblOperatingsystem.Version Like '%17763%' And
tblOperatingsystem.Caption Like '%LTS%' Then '#d4f4be'
When GetDate() >= '2018-10-09' And tblOperatingsystem.Version = '10.0.15063'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2019-10-08' And tblOperatingsystem.Version = '10.0.15063'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2019-04-09' And tblOperatingsystem.Version = '10.0.16299'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2020-10-13' And tblOperatingsystem.Version = '10.0.16299'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2019-11-12' And tblOperatingsystem.Version = '10.0.17134'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2020-11-10' And tblOperatingsystem.Version = '10.0.17134'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2020-11-10' And tblOperatingsystem.Version = '10.0.17763'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2021-05-11' And tblOperatingsystem.Version = '10.0.17763'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2021-11-10' And tblOperatingsystem.Version = '10.0.18362'
Then '#ffadad'
When GetDate() >= '2021-05-11' And tblOperatingsystem.Version = '10.0.18363'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2022-05-10' And tblOperatingsystem.Version = '10.0.18363'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2021-12-14' And tblOperatingsystem.Version = '10.0.19041'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2022-05-10' And tblOperatingsystem.Version = '10.0.19042'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2023-05-09' And tblOperatingsystem.Version = '10.0.19042'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2020-01-14' And (tblOperatingsystem.Caption Like '%2008%'
Or tblOperatingsystem.Caption Like '%Windows 7%') Then '#ffadad'
When GetDate() >= '2027-01-12' And tblOperatingsystem.Caption Like
'%Server 2016%' Then '#ffadad'
When GetDate() >= '2029-01-09' And tblOperatingsystem.Caption Like
'%Server 2019%' Then '#ffadad'
When GetDate() >= '2023-10-10' And tblOperatingsystem.Caption Like
'%Server 2012%' Then '#ffadad'
When GetDate() >= '2025-10-14' And tblOperatingsystem.Version = '10.0.10240'
And tblOperatingsystem.Caption Like '%LTS%' Then '#ffadad'
When GetDate() >= '2026-10-13' And tblOperatingsystem.Version = '10.0.14393'
And tblOperatingsystem.Caption Like '%LTS%' Then '#ffadad'
When GetDate() >= '2029-01-09' And tblOperatingsystem.Version = '10.0.17763'
And tblOperatingsystem.Caption Like '%LTS%' Then '#ffadad'
When GetDate() >= '2023-01-10' And tblOperatingsystem.Caption Like
'%Windows 8%' Then '#ffadad'
When GetDate() >= '2020-10-13' And tblOperatingsystem.Version = '6.1.7601'
And tblOperatingsystem.Caption Like '%Embedded%' Then '#ffadad'
When tblOperatingsystem.Caption Is Null Then '#ffadad'
Else '#d4f4be'
End As backgroundcolor,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model
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
Where tblAssets.Assettype Like '-1' And tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName

View solution in original post

7 REPLIES 7
brandon_jones
Champion Sweeper III
Lansweeper also has a built in widget named "Windows 10 Version Overview" that can be placed on a dashboard and shows the same info. You can also click on the version and it will show you which computers have that version installed.
Hendrik_VE
Champion Sweeper III
We update the report monthly and don't have any issue (ok the report builder is crap, but it eventually works). But I must admit we have a distributed environment, separate SQL server (SQL Standard 2016), 16GB RAM so maybe a bit more 'beefy' then a standard environment.

But thanks for the hint regarding the SQL View, RC62N!
RCorbeil
Honored Sweeper II
You may need to use the report builder to create a place-holder -- leave the default query and save it with the name you want the actual report to be -- then modify the view using SQL Server Manager, replacing the default query with this code. I've had to do that with reports that overwhelm the report editor.
fjca
Champion Sweeper II
Has everyone been able to run this report via the Report Builder ?
It runs fine in SQL Server Management Studio, but when I try it on Report Builder, it just sits there for minutes saying Loading until I get bored and close the tab...

Running latest verison...
Andy_Sismey
Champion Sweeper III
Hendrik.VE thank you very much this is exactly what I'm trying to achieve, life saver, thank you !
Hendrik_VE
Champion Sweeper III
I use (and manage) this hell of a report that reports all my assets, the exact build they have, the End of Life date and the days until EOL.
Report is exported to Power BI where I created a nice clickable pie diagram with all builds, or all assets which are EOL (or approach their EOL date).
Beware, this shows only the OS versions/Win10 editions which we have currently installed (but I believe that's most of them).

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom6 As 'System Administrator',
tblAssets.Domain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblOperatingsystem.Caption As OS,
tblOperatingsystem.Version,
Case tblOperatingsystem.Version
When '10.0.10240' Then '1507'
When '10.0.10586' Then '1511'
When '10.0.14393' Then '1607'
When '10.0.15063' Then '1703'
When '10.0.16299' Then '1709'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
When '10.0.18363' Then '1909'
When '10.0.19041' Then '2004'
When '10.0.19042' Then '20H2'
Else ''
End As 'Win 10 Release',
Case
When tblOperatingsystem.Caption Like '%2000%' Then 'EOL'
When tblOperatingsystem.Version = '10.0.10240' And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When tblOperatingsystem.Version = '10.0.10586' Then 'EOL'
When tblOperatingsystem.Version = '10.0.14393' And
tblOperatingsystem.Caption Not Like '%LTS%' And
tblOperatingsystem.Caption Not Like '%2016%' Then 'EOL'
When tblOperatingsystem.Version = '10.0.15063' Then 'EOL'
When GetDate() >= '2019-04-09' And tblOperatingsystem.Version = '10.0.16299'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2020-10-13' And tblOperatingsystem.Version = '10.0.16299'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2019-11-12' And tblOperatingsystem.Version = '10.0.17134'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2020-11-10' And tblOperatingsystem.Version = '10.0.17134'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2020-11-10' And tblOperatingsystem.Version = '10.0.17763'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2021-05-11' And tblOperatingsystem.Version = '10.0.17763'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2020-12-08' And tblOperatingsystem.Version = '10.0.18362'
Then 'EOL'
When GetDate() >= '2021-05-11' And tblOperatingsystem.Version = '10.0.18363'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2022-05-10' And tblOperatingsystem.Version = '10.0.18363'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2021-12-14' And tblOperatingsystem.Version = '10.0.19041'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2022-05-10' And tblOperatingsystem.Version = '10.0.19042'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2023-05-09' And tblOperatingsystem.Version = '10.0.19042'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then 'EOL'
When GetDate() >= '2020-01-14' And (tblOperatingsystem.Caption Like '%2008%'
Or tblOperatingsystem.Caption Like '%Windows 7%') Then 'EOL'
When GetDate() >= '2029-01-09' And tblOperatingsystem.Caption Like
'%Server 2019%' Then 'EOL'
When GetDate() >= '2027-01-12' And tblOperatingsystem.Caption Like
'%Server 2016%' Then 'EOL'
When GetDate() >= '2023-10-10' And tblOperatingsystem.Caption Like
'%Server 2012%' Then 'EOL'
When GetDate() >= '2023-01-10' And tblOperatingsystem.Caption Like
'%Windows 8%' Then 'EOL'
When GetDate() >= '2025-10-14' And tblOperatingsystem.Version = '10.0.10240'
And tblOperatingsystem.Caption Like '%LTS%' Then 'EOL'
When GetDate() >= '2026-10-13' And tblOperatingsystem.Version = '10.0.14393'
And tblOperatingsystem.Caption Like '%LTS%' Then 'EOL'
When GetDate() >= '2029-01-09' And tblOperatingsystem.Version = '10.0.17763'
And tblOperatingsystem.Caption Like '%LTS%' Then 'EOL'
When GetDate() >= '2020-10-13' And tblOperatingsystem.Version = '6.1.7601'
And tblOperatingsystem.Caption Like '%Embedded%' Then 'EOL'
When tblOperatingsystem.Caption Is Null Then 'Rescan Asset'
Else 'Supported'
End As Status,
Case
When GetDate() > '2019-01-01' And GetDate() < '2025-10-14' And
tblOperatingsystem.Version = '10.0.10240' And
tblOperatingsystem.Caption Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2025-10-14') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2026-10-13' And
tblOperatingsystem.Version = '10.0.14393' And
tblOperatingsystem.Caption Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2026-10-13') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2029-01-09' And
tblOperatingsystem.Version = '10.0.17763' And
tblOperatingsystem.Caption Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2029-01-09') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2019-10-08' And
tblOperatingsystem.Version = '10.0.15063' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2019-10-08') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-10-13' And
tblOperatingsystem.Version = '10.0.16299' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-10-13') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2019-11-12' And
tblOperatingsystem.Version = '10.0.17134' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2019-11-12') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-11-10' And
tblOperatingsystem.Version = '10.0.17134' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-11-10') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-11-10' And
tblOperatingsystem.Version = '10.0.17763' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-11-10') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2021-05-11' And
tblOperatingsystem.Version = '10.0.17763' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2021-05-11') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-12-08' And
tblOperatingsystem.Version = '10.0.18362' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-12-08') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2021-05-11' And
tblOperatingsystem.Version = '10.0.18363' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2021-05-11') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2022-05-10' And
tblOperatingsystem.Version = '10.0.18363' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2022-05-10') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2021-12-14' And
tblOperatingsystem.Version = '10.0.19041' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2021-12-14') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2022-05-10' And
tblOperatingsystem.Version = '10.0.19042' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2022-05-10') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2023-05-09' And
tblOperatingsystem.Version = '10.0.19042' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2023-05-09') As nvarchar) +
' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-01-14' And
(tblOperatingsystem.Caption Like '%2008%' Or
tblOperatingsystem.Caption Like '%Windows 7%') Then 'EOL in ' +
Cast(DateDiff(DAY, GetDate(), '2020-01-14') As nvarchar) + ' days'
When GetDate() > '2019-01-01' And GetDate() < '2027-01-12' And
tblOperatingsystem.Caption Like '%Server 2016%' Then 'EOL in ' +
Cast(DateDiff(DAY, GetDate(), '2027-01-12') As nvarchar) + ' days'
When GetDate() > '2019-01-01' And GetDate() < '2029-01-09' And
tblOperatingsystem.Caption Like '%Server 2019%' Then 'EOL in ' +
Cast(DateDiff(DAY, GetDate(), '2029-01-09') As nvarchar) + ' days'
When GetDate() > '2019-01-01' And GetDate() < '2023-10-10' And
tblOperatingsystem.Caption Like '%Server 2012%' Then 'EOL in ' +
Cast(DateDiff(DAY, GetDate(), '2023-10-10') As nvarchar) + ' days'
When GetDate() > '2019-01-01' And GetDate() < '2023-01-10' And
tblOperatingsystem.Caption Like '%Windows 8%' Then 'EOL in ' +
Cast(DateDiff(DAY, GetDate(), '2023-01-10') As nvarchar) + ' days'
When GetDate() > '2019-01-01' And GetDate() < '2020-10-13' And
tblOperatingsystem.Caption Like '%Embedded%' And
tblOperatingsystem.Version = '6.1.7601' Then
'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-10-13') As nvarchar) +
' days'
End As 'Days until EOL',
Case
When tblOperatingsystem.Caption Like '%2000%' Then '2010-07-13'
When tblOperatingsystem.Version = '10.0.10240' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '2017-05-09'
When tblOperatingsystem.Version = '10.0.10586' Then '2017-10-10'
When tblOperatingsystem.Version = '10.0.14393' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2018-04-10'
When tblOperatingsystem.Version = '10.0.14393' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '2019-04-09'
When tblOperatingsystem.Version = '10.0.15063' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2018-10-09'
When tblOperatingsystem.Version = '10.0.15063' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2019-10-08'
When tblOperatingsystem.Version = '10.0.16299' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2019-04-09'
When tblOperatingsystem.Version = '10.0.16299' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2020-10-13'
When tblOperatingsystem.Version = '10.0.17134' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2019-11-12'
When tblOperatingsystem.Version = '10.0.17134' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2020-11-10'
When tblOperatingsystem.Version = '10.0.17763' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2020-11-10'
When tblOperatingsystem.Version = '10.0.17763' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '2021-05-11'
When tblOperatingsystem.Version = '10.0.18362' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2020-12-08'
When tblOperatingsystem.Version = '10.0.18362' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2020-12-08'
When tblOperatingsystem.Version = '10.0.18363' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2021-05-11'
When tblOperatingsystem.Version = '10.0.18363' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2022-05-10'
When tblOperatingsystem.Version = '10.0.19041' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '2021-12-14'
When tblOperatingsystem.Version = '10.0.19042' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '2022-05-10'
When tblOperatingsystem.Version = '10.0.19042' And
(tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '2023-05-09'
When tblOperatingsystem.Version = '10.0.10240' And
(tblOperatingsystem.Caption Like '%LTS%') Then '2025-10-14'
When tblOperatingsystem.Version = '10.0.14393' And
(tblOperatingsystem.Caption Like '%LTS%') Then '2026-10-13'
When tblOperatingsystem.Version = '10.0.17763' And
(tblOperatingsystem.Caption Like '%LTS%') Then '2029-01-09'
When tblOperatingsystem.Caption Like '%2008%' Or
tblOperatingsystem.Caption Like '%Windows 7%' Then '2020-01-14'
When tblOperatingsystem.Caption Like '%Server 2019%' Then '2029-01-09'
When tblOperatingsystem.Caption Like '%Server 2016%' Then '2027-01-12'
When tblOperatingsystem.Caption Like '%Server 2012%' Then '2023-10-10'
When tblOperatingsystem.Caption Like '%Windows 8%' Then '2023-01-10'
When tblOperatingsystem.Caption Like '%Embedded%' And
tblOperatingsystem.Version = '6.1.7601' Then '2020-10-13'
End As [EOL Date],
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblOperatingsystem.Caption Like '%2000%' Then '#ffadad'
When tblOperatingsystem.Version Like '%10240%' And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When tblOperatingsystem.Version Like '%10240%' And
tblOperatingsystem.Caption Like '%LTS%' Then '#d4f4be'
When tblOperatingsystem.Version Like '%10586%' Then '#ffadad'
When tblOperatingsystem.Version = '10.0.14393' And
tblOperatingsystem.Caption Not Like '%LTS%' And
tblOperatingsystem.Caption Not Like '%2016%' Then '#ffadad'
When tblOperatingsystem.Version Like '%14393%' And
tblOperatingsystem.Caption Like '%LTS%' Then '#d4f4be'
When tblOperatingsystem.Version Like '%17763%' And
tblOperatingsystem.Caption Like '%LTS%' Then '#d4f4be'
When GetDate() >= '2018-10-09' And tblOperatingsystem.Version = '10.0.15063'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2019-10-08' And tblOperatingsystem.Version = '10.0.15063'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2019-04-09' And tblOperatingsystem.Version = '10.0.16299'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2020-10-13' And tblOperatingsystem.Version = '10.0.16299'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2019-11-12' And tblOperatingsystem.Version = '10.0.17134'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2020-11-10' And tblOperatingsystem.Version = '10.0.17134'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2020-11-10' And tblOperatingsystem.Version = '10.0.17763'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2021-05-11' And tblOperatingsystem.Version = '10.0.17763'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2021-11-10' And tblOperatingsystem.Version = '10.0.18362'
Then '#ffadad'
When GetDate() >= '2021-05-11' And tblOperatingsystem.Version = '10.0.18363'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2022-05-10' And tblOperatingsystem.Version = '10.0.18363'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2021-12-14' And tblOperatingsystem.Version = '10.0.19041'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2022-05-10' And tblOperatingsystem.Version = '10.0.19042'
And (tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then '#ffadad'
When GetDate() >= '2023-05-09' And tblOperatingsystem.Version = '10.0.19042'
And (tblOperatingsystem.Caption Like '%Enterprise%' Or
tblOperatingsystem.Caption Like '%Education%') And
tblOperatingsystem.Caption Not Like '%LTS%' Then '#ffadad'
When GetDate() >= '2020-01-14' And (tblOperatingsystem.Caption Like '%2008%'
Or tblOperatingsystem.Caption Like '%Windows 7%') Then '#ffadad'
When GetDate() >= '2027-01-12' And tblOperatingsystem.Caption Like
'%Server 2016%' Then '#ffadad'
When GetDate() >= '2029-01-09' And tblOperatingsystem.Caption Like
'%Server 2019%' Then '#ffadad'
When GetDate() >= '2023-10-10' And tblOperatingsystem.Caption Like
'%Server 2012%' Then '#ffadad'
When GetDate() >= '2025-10-14' And tblOperatingsystem.Version = '10.0.10240'
And tblOperatingsystem.Caption Like '%LTS%' Then '#ffadad'
When GetDate() >= '2026-10-13' And tblOperatingsystem.Version = '10.0.14393'
And tblOperatingsystem.Caption Like '%LTS%' Then '#ffadad'
When GetDate() >= '2029-01-09' And tblOperatingsystem.Version = '10.0.17763'
And tblOperatingsystem.Caption Like '%LTS%' Then '#ffadad'
When GetDate() >= '2023-01-10' And tblOperatingsystem.Caption Like
'%Windows 8%' Then '#ffadad'
When GetDate() >= '2020-10-13' And tblOperatingsystem.Version = '6.1.7601'
And tblOperatingsystem.Caption Like '%Embedded%' Then '#ffadad'
When tblOperatingsystem.Caption Is Null Then '#ffadad'
Else '#d4f4be'
End As backgroundcolor,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model
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
Where tblAssets.Assettype Like '-1' And tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName
Andy_Sismey
Champion Sweeper III
Ok so I managed to create it myself, the following report list All Versions of Windows 10 by build and then allows you to select a particular version and display assets :

Select Top 1000000 Count(tblAssets.AssetID) As Count,
tsysOS.OSname,
tsysOS.Image As icon,
tblOperatingsystem.Version + '.' + tblAssets.BuildNumber As Build,
Case tblOperatingsystem.Version
When '10.0.10240' Then '1507'
When '10.0.10586' Then '1511'
When '10.0.14393' Then '1607'
When '10.0.15063' Then '1703'
When '10.0.16299' Then '1709'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
When '10.0.18363' Then '1909'
When '10.0.19041' Then '20H1'
When '10.0.19042' Then '20H2'
When '10.0.20241' Then '20241 - Windows Insiders in the Dev Channel'
When '6.1.7601' Then '7601'
When '6.1.7600' Then '7600'
When '6.3.9600' Then '9600'
Else '?'
End As Version,
tblOperatingsystem.Caption As Caption
From tblAssets
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1
Group By tsysOS.OSname,
tsysOS.Image,
tblOperatingsystem.Caption,
tblAssetCustom.SoftwareVersion,
tblAssets.SP,
tblAssets.OScode,
tblOperatingsystem.Version,
tblAssets.BuildNumber
Order By Build