Select Distinct Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon, tblAssets.AssetID, tblAssets.AssetName, Case When tblComputersystem.Domainrole > 1 Then 'Server' Else 'Workstation' End As [Workstation/Server], Case When SubQuery4.[Patch Nov] = 'Up to date' Then '' When Convert(date,LatestEvent.EventTime) = Convert(date,SubQuery1.InstalledOn) Then 'Reboot might be required' When Convert(date,LatestEvent.EventTime) < Convert(date,SubQuery1.InstalledOn) Then 'Reboot is required' Else '' End As [Reboot Status], SubQuery4.[Patch Nov], SubQuery4.[Patch Oct], SubQuery4.[Patch Sept], SubQuery4.[Patch Aug], SubQuery4.[Patch Jul], SubQuery4.[Patch Jun], SubQuery4.[Patch May], SubQuery4.[Patch Apr], SubQuery4.[Patch Mar], SubQuery4.[Patch Feb], SubQuery4.[Patch Jan], Case When SubQuery4.[Patch Nov] = 'Up to date' Then '#d4f4be' When SubQuery4.[Patch Nov] = 'EOL, update to a higher Windows version' Then '#ffadad' Else '#ffadad' End As backgroundcolor From tblAssets Left Join (Select tblassets.assetid, SubQuery2.Buildnumber, Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012 = 1 Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012R2 = 1 Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 8594 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 8027 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 6575 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 6575 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 8027 Then 'Up to date' When tsysOS.OScode Like '10.0.20348%' And SubQuery2.Buildnumber >= 4405 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 6199 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1965 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 7092 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 7092 Then 'Up to date' When tsysOS.OScode Like '10.0.26200%' And SubQuery2.Buildnumber >= 7092 Then 'Up to date' Else 'Out of date' End As [Patch Nov], Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 20890 Then 'Up to date' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 7699 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 6766 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 5371 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 5371 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 6766 Then 'Up to date' When tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 3091 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 4751 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1369 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 2894 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 2894 Then 'Up to date' Else 'Out of date' End As [Patch Jan], Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 20915 Then 'Up to date' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 7785 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 6893 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 5487 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 5487 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 6893 Then 'Up to date' When tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 3148 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 4890 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1425 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 3194 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 3194 Then 'Up to date' Else 'Out of date' End As [Patch Feb], Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 20947 Then 'Up to date' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 7876 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 7009 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 5608 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 5608 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 7009 Then 'Up to date' When tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 3328 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 5039 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1486 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 3476 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 3476 Then 'Up to date' Else 'Out of date' End As [Patch Mar], Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 20979 Then 'Up to date' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 7969 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 7131 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 5737 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 5737 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 7131 Then 'Up to date' When tsysOS.OScode Like '10.0.20348%' And SubQuery2.Buildnumber >= 3453 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 5189 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1551 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 3775 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 3775 Then 'Up to date' Else 'Out of date' End As [Patch Apr], Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 21014 Then 'Up to date' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 8066 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 7309 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 5854 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 5854 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 7309 Then 'Up to date' When tsysOS.OScode Like '10.0.20348%' And SubQuery2.Buildnumber >= 3630 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 5335 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1611 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 4061 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 4061 Then 'Up to date' Else 'Out of date' End As [Patch May], Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012 = 1 Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012R2 = 1 Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 21034 Then 'Up to date' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 8148 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 7434 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 5965 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 5965 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 7434 Then 'Up to date' When tsysOS.OScode Like '10.0.20348%' And SubQuery2.Buildnumber >= 3745 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 5472 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1665 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 4343 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 4343 Then 'Up to date' Else 'Out of date' End As [Patch Jun], Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012 = 1 Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012R2 = 1 Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 21034 Then 'Up to date' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 8148 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 7548 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 6093 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 6093 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 7548 Then 'Up to date' When tsysOS.OScode Like '10.0.20348%' And SubQuery2.Buildnumber >= 3932 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 5624 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1732 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 4652 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 4652 Then 'Up to date' Else 'Out of date' End As [Patch Jul], Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012 = 1 Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012R2 = 1 Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 21100 Then 'Up to date' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 8330 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 7671 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 6216 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 6216 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 7671 Then 'Up to date' When tsysOS.OScode Like '10.0.20348%' And SubQuery2.Buildnumber >= 3989 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 5768 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1791 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 4851 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 4851 Then 'Up to date' Else 'Out of date' End As [Patch Aug], Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012 = 1 Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012R2 = 1 Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 21128 Then 'Up to date' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 8422 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 7792 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 6332 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 6332 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 7792 Then 'Up to date' When tsysOS.OScode Like '10.0.20348%' And SubQuery2.Buildnumber >= 4166 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 5909 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1849 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 6569 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 6569 Then 'Up to date' Else 'Out of date' End As [Patch Sept], Case When tsysOS.OSname = 'Win 2008' Then 'EOL, update to a higher Windows version' When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012 = 1 Then 'Up to date' When tsysOS.OSname = 'Win 8.1' Then 'EOL, update to a higher Windows version' When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblassets.AssetID And SubQuerySSU.HasSSU2012R2 = 1 Then 'Up to date' When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 21161 Then 'Up to date' When tsysOS.OScode Like '10.0.10586%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.14393%' Or tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 8519 Then 'Up to date' When tsysOS.OScode Like '10.0.15063%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.16299%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17134%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not Like '%LTSC%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like '%LTSC%' And SubQuery2.Buildnumber >= 7919 Then 'Up to date' When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.18363%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19041%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19042%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19043%' Then 'EOL, update to a higher Windows version' When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 6456 Then 'Up to date' When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 6456 Then 'Up to date' When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 7919 Then 'Up to date' When tsysOS.OScode Like '10.0.20348%' And SubQuery2.Buildnumber >= 4294 Then 'Up to date' When tsysOS.OScode Like '10.0.22000%' Then 'EOL, update to a higher Windows version' When (tsysOS.OScode Like '10.0.22621%' Or tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 6060 Then 'Up to date' When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1913 Then 'Up to date' When tsysOS.OSname Like 'Win 2025' And SubQuery2.Buildnumber >= 6899 Then 'Up to date' When tsysOS.OScode Like '10.0.26100%' And SubQuery2.Buildnumber >= 6899 Then 'Up to date' When tsysOS.OScode Like '10.0.26200%' And SubQuery2.Buildnumber >= 6899 Then 'Up to date' Else 'Out of date' End As [Patch Oct] From tblassets Left Join tsysOS On tsysOS.OScode = tblassets.OScode Inner Join tblOperatingsystem On tblOperatingsystem.AssetID = tblassets.AssetID Left Join (Select tblAssets.AssetID, Case When IsNumeric(tblAssets.BuildNumber) = 1 Then Convert(bigint,tblAssets.BuildNumber) Else Null End As Buildnumber From tblAssets Where tblAssets.Assettype = -1) As SubQuery2 On tblassets.AssetID = SubQuery2.AssetID Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID In ('KB5050004', 'KB5050048')) As SubQuery3 On tblassets.AssetID = SubQuery3.AssetID Left Join (Select Distinct tblAssets.AssetID, Case When Exists(Select 1 From tblQuickFixEngineering qfe Inner Join tblQuickFixEngineeringUni qfeu On qfeu.QFEID = qfe.QFEID Where qfe.AssetID = tblAssets.AssetID And qfeu.HotFixID Like '%SSU%' And qfeu.HotFixID Like '%2012%' And qfeu.HotFixID Not Like '%R2%') Then 1 Else 0 End As HasSSU2012, Case When Exists(Select 1 From tblQuickFixEngineering qfe Inner Join tblQuickFixEngineeringUni qfeu On qfeu.QFEID = qfe.QFEID Where qfe.AssetID = tblAssets.AssetID And qfeu.HotFixID Like '%SSU%' And qfeu.HotFixID Like '%2012%R2%') Then 1 Else 0 End As HasSSU2012R2 From tblAssets) As SubQuerySSU On tblassets.AssetID = SubQuerySSU.AssetID) As SubQuery4 On tblAssets.AssetID = SubQuery4.AssetID Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID Left Join (Select rankedEvents.AssetId, rankedEvents.EventTime From (Select tblUptime.AssetId, tblUptime.EventTime, Row_Number() Over (Partition By tblUptime.AssetId Order By tblUptime.EventTime Desc) As rn From tblUptime Where tblUptime.EventType = 1 And IsDate(tblUptime.EventTime) = 1) As rankedEvents Where rankedEvents.rn = 1) As LatestEvent On tblAssets.AssetID = LatestEvent.AssetId Left Join tblQuickFixEngineering On tblAssets.AssetID = tblQuickFixEngineering.AssetID Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID, Case When IsNumeric(Max(Right(tblQuickFixEngineeringUni.HotFixID, 7))) = 1 Then Max(Cast(Right(tblQuickFixEngineeringUni.HotFixID, 7) As bigint)) Else Null End As PatchIDMax, Max(Cast(tblQuickFixEngineering.installedon As date)) As InstalledOn From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where Right(tblQuickFixEngineeringUni.HotFixID, 7) Not Like '%[^0-9]%' And IsDate(tblQuickFixEngineering.installedon) = 1 Group By tblQuickFixEngineering.AssetID) As SubQuery1 On tblAssets.AssetID = SubQuery1.AssetID Inner Join tblState On tblState.State = tblAssetCustom.State Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID Left Join tsysOS On tsysOS.OScode = tblAssets.OScode Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP Left Join (Select Distinct Top 1000000 TsysLastscan.AssetID As ID, TsysLastscan.Lasttime As QuickFixLastScanned From TsysWaittime Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On tblAssets.AssetID = QuickFixLastScanned.ID Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID, Max(tblErrors.Teller) As ErrorID From tblErrors Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID = ScanningError.ID Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype = tblErrors.ErrorType Where tsysOS.OSname <> 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And tsysOS.OSname Not Like '%2003%' And tblAssetCustom.State = 1 And tsysAssetTypes.AssetTypename Like 'Windows%' Order By tblAssets.AssetName