Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
SubQuery4.[Patch status],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Version,
SubQuery4.Buildnumber,
SubQuery1.PatchIDMax as [Highest KB Patch installed],
SubQuery1.InstalledOn as [Installed On],
SubQuery4.[Install one of these updates],
CASE 
    WHEN SubQuery4.[Patch status] = '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],
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblState.Statename As State,
tblAssets.Lastseen,
tblAssets.Lasttried,
QuickFixLastScanned.QuickFixLastScanned,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
Case
When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) > 3 Then
'Windows update information may not be up to date. We recommend rescanning this machine.'
Else ''
End As Comment,
Case when SubQuery4.[Patch status] = 'Up to date' then '#d4f4be'
when SubQuery4.[Patch status] = '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 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 >= 20796 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 >= 7428 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 >= 6414 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 >= 5011 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.19045%' And SubQuery2.Buildnumber >= 5011 Then 'Up to date'
	WHEN tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 6414 Then 'Up to date'
	WHEN tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 2762 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.22000%' And SubQuery2.Buildnumber >= 3260 Then 'Up to date'
    WHEN (tsysOS.OScode LIKE '10.0.22621%' OR tsysOS.OScode LIKE '10.0.22631%') And SubQuery2.Buildnumber >= 4317 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.25398%' And SubQuery2.Buildnumber >= 1189 Then 'Up to date'
	WHEN tsysOS.OScode LIKE '10.0.26100%' And SubQuery2.Buildnumber >= 2033 Then 'Up to date'
Else 'Out of date'
End As [Patch status],
CASE
    WHEN tsysOS.OSname = 'Win 2008' THEN 'EOL'
    WHEN tsysOS.OSname IN ('Win 7', 'Win 7 RC', 'Win 2008 R2') THEN 'EOL'
    WHEN tsysOS.OSname = 'Win 8' THEN 'EOL'
    WHEN tsysOS.OSname = 'Win 2012' AND SubQuery3.AssetID <> tblassets.AssetID THEN 'KB5044342'
    WHEN tsysOS.OSname = 'Win 8.1' THEN 'EOL'
    WHEN tsysOS.OSname = 'Win 2012 R2' AND SubQuery3.AssetID <> tblassets.AssetID THEN 'KB5044343'
    WHEN tsysOS.OScode LIKE '10.0.10240%' AND SubQuery2.Buildnumber < 20796 THEN 'KB5044286'
    WHEN tsysOS.OScode LIKE '10.0.10586%' THEN 'EOL'
    WHEN (tsysOS.OScode LIKE '10.0.14393%' OR tsysOS.OSname = 'Win 2016') AND SubQuery2.Buildnumber < 7428 THEN 'KB5044293'
    WHEN tsysOS.OScode LIKE '10.0.15063%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.16299%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.17134%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.17763' AND tblOperatingsystem.Caption NOT LIKE '%LTSC%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.17763' AND tblOperatingsystem.Caption LIKE '%LTSC%' AND SubQuery2.Buildnumber < 6414 THEN 'KB5044277'
    WHEN tsysOS.OScode LIKE '10.0.18362%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.18363%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.19041%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.19042%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.19043%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.19044%' AND SubQuery2.Buildnumber < 5011 THEN 'KB5044273'
    WHEN tsysOS.OScode LIKE '10.0.19045%' AND SubQuery2.Buildnumber < 5011 THEN 'KB5044273'
    WHEN tsysOS.OScode LIKE '10.0.19045%' AND SubQuery2.Buildnumber >= 5011 THEN ''
	WHEN tsysOS.OSname = 'Win 2019' AND SubQuery2.Buildnumber < 6414 THEN 'KB5044277'
    WHEN tsysOS.OSname = 'Win 2022' AND SubQuery2.Buildnumber < 2762 THEN 'KB5044281'
    WHEN tsysOS.OScode LIKE '10.0.22000%' AND SubQuery2.Buildnumber < 3260 THEN 'KB5044280'
    WHEN (tsysOS.OScode LIKE '10.0.22621%' OR tsysOS.OScode LIKE '10.0.22631%') AND SubQuery2.Buildnumber < 4317 THEN 'KB5044285'
    WHEN tsysOS.OScode LIKE '10.0.25398%' AND SubQuery2.Buildnumber < 1189 THEN 'KB5044288'
    WHEN tsysOS.OScode LIKE '10.0.26100%' AND SubQuery2.Buildnumber < 2033 THEN 'KB5044284'
    ELSE ''
END AS [Install one of these updates]
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 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 ('KB5044342','KB5044343')) As SubQuery3 On
tblAssets.AssetID = SubQuery3.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 
    AssetId,
    EventTime
FROM (
    SELECT 
        AssetId,
        EventTime,
        ROW_NUMBER() OVER (PARTITION BY AssetId ORDER BY EventTime DESC) AS rn
    FROM 
        tblUptime
    WHERE 
        EventType = 1
        AND ISDATE(EventTime) = 1
) AS rankedEvents
WHERE 
    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(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(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.Domain,
tblAssets.AssetName