→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎03-15-2021 10:11 AM
'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,
Solved! Go to Solution.
‎03-15-2021 05:16 PM
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
‎03-19-2021 06:53 PM
‎03-19-2021 05:38 PM
‎03-19-2021 03:33 PM
‎03-19-2021 11:18 AM
‎03-15-2021 05:28 PM
‎03-15-2021 05:16 PM
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
‎03-15-2021 04:34 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now