Windows EOL report, workstation and Server

[jmql]

Hi,

I'm having trouble writing a report that would automatically report EOL for a windows operating system based on the OS name, and build number.

What I'd like to do, is report on all windows assets in the environment, and show EOL operating systems in red with "EOL" as a separate column. I'd also like to show machines that are within 6 months of EOL in yellow, with the number of days listed in a separate column.

I'm using info from MS to try to organize this, but I'm getting lost as I try to build all the case and if statements to get this to work.

Example might be Windows 10, V 1809. Home/Pro/Ed versions expire 2020-11-10, Enterprise expires 2021-05-11.
- I'd like it to be Green today,
- Home/Pro/Education version yellow when it's within 6 months of 2020-11-10. and turn red after that date. It should show EOL or 'Not EOL', based on that date as well.
- Enterprise version would be yellow within 6 months of 2021-05-11, and red after that date, with EOL or 'Not EOL'.


Windows publishes workstation info here :
https://support.microsoft.com/en-us/help/13853/windows-lifecycle-fact-sheet

Server info is kind of all over the place, but I found a decent list here :
https://support.microsoft.com/en-us/lifecycle/search/1163


Not looking for someone to write everything into it, but maybe add a couple and let me see how the code works, and I can build the rest. I'm aware this is a document I'd have to maintain, and I'm okay with that.

If there is a better answer, please let me know.

Thanks!!

[jmql]

This looks AWESOME, and exactly like what I'm trying to accomplish.

Trying to load it into the LSWP server, and it keeps timing out, though.
The joys of WFH over a VPN.

I'll get there somehow.

TY much for this. You rock.

[Hendrik_VE · Accepted solution]

I think this is exactly what you're looking for, might need a bit of tweaking to fit your environment.
I only added the OS's that are relevant for my shop.

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'
    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 '%LTSB' Then 'EOL'
    When tblOperatingsystem.Version = '10.0.10586' Then 'EOL'
    When tblOperatingsystem.Version = '10.0.14393' And
      tblOperatingsystem.Caption Not Like '%LTSB' And
      tblOperatingsystem.Caption Not Like '%2016%' Then 'EOL'
    When GetDate() >= '2018-10-09' And tblOperatingsystem.Version = '10.0.15063'
      And (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
    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 '%LTSB' 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-04-14' And tblOperatingsystem.Version = '10.0.16299'
      And (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' 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 '%LTSB' Then 'EOL'
    When GetDate() >= '2020-05-12' 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 '%LTSB' Then 'EOL'
    When GetDate() >= '2020-12-08' And tblOperatingsystem.Version = '10.0.18362'
    Then 'EOL'
    When GetDate() >= '2020-01-14' And (tblOperatingsystem.Caption Like '%2008%'
      Or tblOperatingsystem.Caption Like '%Windows 7%') 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 '%LTSB' Then 'EOL'
    When GetDate() >= '2026-10-13' And tblOperatingsystem.Version = '10.0.14393'
      And tblOperatingsystem.Caption Like '%LTSB' Then 'EOL'
    When GetDate() >= '2029-01-09' And tblOperatingsystem.Version = '10.0.17763'
      And tblOperatingsystem.Caption Like '%LTSB' 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 '%LTSB' 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 '%LTSB' 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 '%LTSB' 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 '%LTSB' Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2019-10-08') As nvarchar) +
      ' days'
    When GetDate() > '2019-01-01' And GetDate() < '2020-04-14' And
      tblOperatingsystem.Version = '10.0.16299' And
      (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-04-14') 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 '%LTSB' Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-11-10') As nvarchar) +
      ' days'
    When GetDate() > '2019-01-01' And GetDate() < '2020-05-12' And
      tblOperatingsystem.Version = '10.0.17763' And
      (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-05-12') 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 '%LTSB' 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() < '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() < '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 '%LTSB%' 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 '%LTSB%' 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-04-14'
    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-05-12'
    When tblOperatingsystem.Version = '10.0.17763' And
      (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') 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.10240' And
      (tblOperatingsystem.Caption Like '%LTSB%' Or
      tblOperatingsystem.Caption Like '%LTSC%') Then '2025-10-14'
    When tblOperatingsystem.Version = '10.0.14393' And
      (tblOperatingsystem.Caption Like '%LTSB%' Or
      tblOperatingsystem.Caption Like '%LTSC%') Then '2026-10-13'
    When tblOperatingsystem.Version = '10.0.17763' And
      (tblOperatingsystem.Caption Like '%LTSB%' Or
      tblOperatingsystem.Caption Like '%LTSC%') Then '2029-01-09'
    When tblOperatingsystem.Caption Like '%2008%' Or
      tblOperatingsystem.Caption Like '%Windows 7%' Then '2020-01-14'
    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 '%LTSB' Then '#ffadad'
    When tblOperatingsystem.Version Like '%10240%' And
      tblOperatingsystem.Caption Like '%LTSB' Then '#d4f4be'
    When tblOperatingsystem.Version Like '%10586%' Then '#ffadad'
    When tblOperatingsystem.Version = '10.0.14393' And
      tblOperatingsystem.Caption Not Like '%LTSB' And
      tblOperatingsystem.Caption Not Like '%2016%' Then '#ffadad'
    When tblOperatingsystem.Version Like '%14393%' And
      tblOperatingsystem.Caption Like '%LTSB' Then '#d4f4be'
    When tblOperatingsystem.Version Like '%17763%' And
      tblOperatingsystem.Caption Like '%LTSB' 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 '%LTSB' 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-04-14' And tblOperatingsystem.Version = '10.0.16299'
      And (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' 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 '%LTSB' Then '#ffadad'
    When GetDate() >= '2020-05-12' 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 '%LTSB' Then '#ffadad'
    When GetDate() >= '2020-12-08' And tblOperatingsystem.Version = '10.0.18362'
    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() >= '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 '%LTSB' Then '#ffadad'
    When GetDate() >= '2026-10-13' And tblOperatingsystem.Version = '10.0.14393'
      And tblOperatingsystem.Caption Like '%LTSB' Then '#ffadad'
    When GetDate() >= '2029-01-09' And tblOperatingsystem.Version = '10.0.17763'
      And tblOperatingsystem.Caption Like '%LTSB' 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

[Noman]

I just found this awesome report, made a crude update based on Wikipedia https://en.wikipedia.org/wiki/Windows_10_version_history and https://michaelspice.net/windows/end-of-life-microsoft-windows-and-office/

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 '20H1'
    When '10.0.19042' Then '20H2'
    When '10.0.19043' Then '21H1'
    When '10.0.19044' Then '21H2'
    When '10.0.22000' Then '21H2'
    Else ''
  End As 'Win 10/11 Release',
  Case
    When tblOperatingsystem.Caption Like '%2000%' Then 'EOL'
    When tblOperatingsystem.Version = '10.0.10240' And
      tblOperatingsystem.Caption Not Like '%LTSB' Then 'EOL'
    When tblOperatingsystem.Version = '10.0.10586' Then 'EOL'
    When tblOperatingsystem.Version = '10.0.14393' And
      tblOperatingsystem.Caption Not Like '%LTSB' And
      tblOperatingsystem.Caption Not Like '%2016%' Then 'EOL'
    When GetDate() >= '2018-10-09' And tblOperatingsystem.Version = '10.0.15063'
      And (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
    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 '%LTSB' 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-04-14' And tblOperatingsystem.Version = '10.0.16299'
      And (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' 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 '%LTSB' Then 'EOL'
    When GetDate() >= '2020-05-12' 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 '%LTSB' Then 'EOL'
    When GetDate() >= '2020-12-08' And tblOperatingsystem.Version = '10.0.18362'
    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 '%LTSB' Then 'EOL'
	When GetDate() >= '2022-05-10' And tblOperatingsystem.Version = '10.0.18363'
      And (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
	When GetDate() >= '2021-12-14' And tblOperatingsystem.Version = '10.0.19041'
      And (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' Then 'EOL'
	When GetDate() >= '2021-12-14' And tblOperatingsystem.Version = '10.0.19041'
      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 '%LTSB' 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() >= '2022-12-13' And tblOperatingsystem.Version = '10.0.19043'
      And (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' Then 'EOL'
	When GetDate() >= '2022-12-13' And tblOperatingsystem.Version = '10.0.19043'
      And (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
	When GetDate() >= '2024-06-11' And tblOperatingsystem.Version = '10.0.19044'
      And (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' Then 'EOL'
	When GetDate() >= '2023-06-13' And tblOperatingsystem.Version = '10.0.19044'
      And (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
	When GetDate() >= '2024-10-08' And tblOperatingsystem.Version = '10.0.22000'
      And (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' Then 'EOL'
	When GetDate() >= '2023-10-10' And tblOperatingsystem.Version = '10.0.22000'
      And (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
    When GetDate() >= '2020-01-14' And (tblOperatingsystem.Caption Like '%2008%'
      Or tblOperatingsystem.Caption Like '%Windows 7%') 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 '%LTSB' Then 'EOL'
    When GetDate() >= '2026-10-13' And tblOperatingsystem.Version = '10.0.14393'
      And tblOperatingsystem.Caption Like '%LTSB' Then 'EOL'
    When GetDate() >= '2029-01-09' And tblOperatingsystem.Version = '10.0.17763'
      And tblOperatingsystem.Caption Like '%LTSB' 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 '%LTSB' 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 '%LTSB' 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 '%LTSB' 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 '%LTSB' Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2019-10-08') As nvarchar) +
      ' days'
    When GetDate() > '2019-01-01' And GetDate() < '2020-04-14' And
      tblOperatingsystem.Version = '10.0.16299' And
      (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-04-14') 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 '%LTSB' Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-11-10') As nvarchar) +
      ' days'
    When GetDate() > '2019-01-01' And GetDate() < '2020-05-12' And
      tblOperatingsystem.Version = '10.0.17763' And
      (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2020-05-12') 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 '%LTSB' 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%') 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 '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2021-12-14') 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%') 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-10-09' And
      tblOperatingsystem.Version = '10.0.19042' And
      (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2023-10-09') As nvarchar) +
      ' days'
	When GetDate() > '2019-01-01' And GetDate() < '2022-12-13' And
      tblOperatingsystem.Version = '10.0.19043' And
      (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2022-12-13') As nvarchar) +
      ' days'
    When GetDate() > '2019-01-01' And GetDate() < '2022-12-13' And
      tblOperatingsystem.Version = '10.0.19043' And
      (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2022-12-13') As nvarchar) +
      ' days'
	When GetDate() > '2019-01-01' And GetDate() < '2023-06-13' And
      tblOperatingsystem.Version = '10.0.19044' And
      (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2023-06-13') As nvarchar) +
      ' days'
    When GetDate() > '2019-01-01' And GetDate() < '2024-06-11' And
      tblOperatingsystem.Version = '10.0.19044' And
      (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2024-06-11') As nvarchar) +
      ' days'
	When GetDate() > '2019-01-01' And GetDate() < '2023-10-10' And
      tblOperatingsystem.Version = '10.0.22000' And
      (tblOperatingsystem.Caption Like '%Home%' Or
      tblOperatingsystem.Caption Like '%Pro%') Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2023-10-10') As nvarchar) +
      ' days'
    When GetDate() > '2019-01-01' And GetDate() < '2024-10-08' And
      tblOperatingsystem.Version = '10.0.22000' And
      (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' Then
      'EOL in ' + Cast(DateDiff(DAY, GetDate(), '2024-10-08') 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() < '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() < '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-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 '%LTSB%' 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 '%LTSB%' 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-04-14'
    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-05-12'
    When tblOperatingsystem.Version = '10.0.17763' And
      (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') 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.10240' And
      (tblOperatingsystem.Caption Like '%LTSB%' Or
      tblOperatingsystem.Caption Like '%LTSC%') Then '2025-10-14'
    When tblOperatingsystem.Version = '10.0.14393' And
      (tblOperatingsystem.Caption Like '%LTSB%' Or
      tblOperatingsystem.Caption Like '%LTSC%') Then '2026-10-13'
    When tblOperatingsystem.Version = '10.0.17763' And
      (tblOperatingsystem.Caption Like '%LTSB%' Or
      tblOperatingsystem.Caption Like '%LTSC%') Then '2029-01-09'
    When tblOperatingsystem.Caption Like '%2008%' Or
      tblOperatingsystem.Caption Like '%Windows 7%' Then '2020-01-14'
    When tblOperatingsystem.Caption Like '%Server 2016%' Then '2027-01-12'
    When tblOperatingsystem.Caption Like '%Server 2012%' Then '2023-10-10'
    When tblOperatingsystem.Caption Like '%Server 2019%' Then '2029-01-09'
    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 '%LTSB' Then '#ffadad'
    When tblOperatingsystem.Version Like '%10240%' And
      tblOperatingsystem.Caption Like '%LTSB' Then '#d4f4be'
    When tblOperatingsystem.Version Like '%10586%' Then '#ffadad'
    When tblOperatingsystem.Version = '10.0.14393' And
      tblOperatingsystem.Caption Not Like '%LTSB' And
      tblOperatingsystem.Caption Not Like '%2016%' Then '#ffadad'
    When tblOperatingsystem.Version Like '%14393%' And
      tblOperatingsystem.Caption Like '%LTSB' Then '#d4f4be'
    When tblOperatingsystem.Version Like '%17763%' And
      tblOperatingsystem.Caption Like '%LTSB' 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 '%LTSB' 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-04-14' And tblOperatingsystem.Version = '10.0.16299'
      And (tblOperatingsystem.Caption Like '%Enterprise%' Or
      tblOperatingsystem.Caption Like '%Education%') And
      tblOperatingsystem.Caption Not Like '%LTSB' 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 '%LTSB' Then '#ffadad'
    When GetDate() >= '2020-05-12' 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 '%LTSB' Then '#ffadad'
    When GetDate() >= '2020-12-08' And tblOperatingsystem.Version = '10.0.18362'
    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() >= '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 '%LTSB' Then '#ffadad'
    When GetDate() >= '2026-10-13' And tblOperatingsystem.Version = '10.0.14393'
      And tblOperatingsystem.Caption Like '%LTSB' Then '#ffadad'
    When GetDate() >= '2029-01-09' And tblOperatingsystem.Version = '10.0.17763'
      And tblOperatingsystem.Caption Like '%LTSB' 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