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 TRY_CONVERT(date, LatestEvent.EventTime) = TRY_CONVERT(date, SubQuery1.InstalledOn)
        THEN 'Reboot might be required' 
    WHEN TRY_CONVERT(date, LatestEvent.EventTime) < TRY_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,
TRY_CONVERT(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
Case
When TRY_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,
'#000000' As foregroundcolor
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%' 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 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 8.1' THEN 'EOL'
    WHEN tsysOS.OSname = 'Win 2012' AND (SubQuery3.AssetID IS NULL OR SubQuerySSU.HasSSU2012 != 1) THEN 'KB5063906 + KB5056456'
    WHEN tsysOS.OSname = 'Win 2012 R2' AND (SubQuery3.AssetID IS NULL OR SubQuerySSU.HasSSU2012R2 != 1) THEN 'KB5063950 + KB5058529'
    WHEN tsysOS.OScode LIKE '10.0.10240%' AND SubQuery2.Buildnumber < 21100 THEN 'KB5063889'
    WHEN tsysOS.OScode LIKE '10.0.10586%' THEN 'EOL'
    WHEN (tsysOS.OScode LIKE '10.0.14393%' OR tsysOS.OSname = 'Win 2016') AND SubQuery2.Buildnumber < 8330 THEN 'KB5063871'
    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 < 7671 THEN 'KB5063877'
    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 < 6216 THEN 'KB5063709'
    WHEN tsysOS.OScode LIKE '10.0.19045%' AND SubQuery2.Buildnumber < 6216 THEN 'KB5063709'
    WHEN tsysOS.OScode LIKE '10.0.19045%' AND SubQuery2.Buildnumber >= 6216 THEN ''
    WHEN tsysOS.OSname = 'Win 2019' AND SubQuery2.Buildnumber < 7671 THEN 'KB5063877'
    WHEN tsysOS.OScode LIKE '10.0.20348%' AND SubQuery2.Buildnumber < 3989 THEN 'KB5063880 (or KB5063812 for Azure)'
    WHEN tsysOS.OScode LIKE '10.0.22000%' THEN 'EOL'
    WHEN (tsysOS.OScode LIKE '10.0.22621%' OR tsysOS.OScode LIKE '10.0.22631%') AND SubQuery2.Buildnumber < 5768 THEN 'KB5063875'
    WHEN tsysOS.OScode LIKE '10.0.25398%' AND SubQuery2.Buildnumber < 1791 THEN 'KB5063899'
    WHEN tsysOS.OSname = 'Win 2025' AND SubQuery2.Buildnumber < 4851 THEN 'KB5063878 (or KB5064010 for Azure)'
    WHEN tsysOS.OScode LIKE '10.0.26100%' AND SubQuery2.Buildnumber < 4851 THEN 'KB5063878 (or KB5064010 for LTSC 2024)'
    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 TRY_CONVERT(bigint, tblAssets.BuildNumber)
            ELSE NULL
        END as Buildnumber 
        from tblAssets where Assettype = -1
    ) As SubQuery2 On tblAssets.AssetID = SubQuery2.AssetID
    Left Join (
        Select tblQuickFixEngineering.AssetID
        From tblQuickFixEngineering
        Inner Join tblQuickFixEngineeringUni 
            On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
        Where tblQuickFixEngineeringUni.HotFixID In ('KB5063906','KB5063950')
    ) As SubQuery3 On tblAssets.AssetID = SubQuery3.AssetID
    Left Join (
        Select tblQuickFixEngineering.AssetID,
            Max(CASE WHEN tblQuickFixEngineeringUni.HotFixID = 'KB5056456' THEN 1 ELSE 0 END) as HasSSU2012,
            Max(CASE WHEN tblQuickFixEngineeringUni.HotFixID = 'KB5058529' THEN 1 ELSE 0 END) as HasSSU2012R2
        From tblQuickFixEngineering
        Inner Join tblQuickFixEngineeringUni 
            On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
        Group By tblQuickFixEngineering.AssetID
    ) 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 
    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(Try_Cast(Right(tblQuickFixEngineeringUni.HotFixID, 7) As bigint))
ELSE NULL
END as PatchIDMax,
MAX(Try_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