Report OS Build breakdown by Build

[Andy_Sismey]

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

[brandon_jones]

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]

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]

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]

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]

Hendrik.VE thank you very much this is exactly what I'm trying to achieve, life saver, thank you !

[Hendrik_VE · Accepted solution]

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]

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