Hello. Looking for advice on how to fix this osquery segment for checking Windows patch levels. My column called is_compliant is not working as expected. Should be showing as yes for the highlighted areas. Thanks in advance for help where I've tripped on the logic here. Note: I'm not using os_version build numbers because they are not reliable.

SELECT 
    CAST(os_version.build AS integer) AS build, 
    CAST(SPLIT(kernel_info.version, '.', 3) AS integer) AS patch,
    CASE 
        WHEN build >= 19045 AND patch >= 2965 THEN 'Yes' 
        WHEN build >= 22621 AND patch >= 1702 THEN 'Yes' 
        ELSE 'No' 
    END AS is_compliant 
FROM kernel_info, os_version;

This statement works as expected

SELECT 
    CAST(os_version.build AS integer) AS build, 
    CAST(SPLIT(kernel_info.version, '.', 3) AS integer) AS patch,
    CASE 
        WHEN build >= 19045 AND CAST(SPLIT(kernel_info.version, '.', 3) AS integer) >= 2965 THEN 'Yes' 
        WHEN build >= 22621 AND CAST(SPLIT(kernel_info.version, '.', 3) AS integer) >= 1702 THEN 'Yes' 
        ELSE 'No' 
    END AS is_compliant 
FROM kernel_info, os_version;

Note: I’m not using os_version build numbers because they are not reliable.

Hrm. Can you say more?

Somehow in this situation, column

build

can be used correctly in the

CASE

statement, though the column patch cannot be. I cannot explain the difference between the two though. CTE is also a good option in this case

There’s some chance

build

isn’t getting the cast, but the underlying column. Which is probably fine here anyhow

@seph So the context is that os_version.build are returning different build numbers so I decided to matchmake the two sources. In my fleet I get kernel_info.version showing 19041 whereas os_version.build shows 19045. Not exactly sure what is going on. A colleague hypothesised that the kernel_info.version build is produced from the original build distributed with the PC whereas os_version.build is the actual current running build of windows upgraded from the original distro.

kernel_info.version

is derived from the properties of file

C:\Windows\System32\ntoskrnl.exe

os_version.build

is derived from the WMI table Win32_OperatingSystem https://learn.microsoft.com/en-us/windows/win32/cimwin32prov/win32-operatingsystem

Thank you Vivek, I was just going to look that up! I’m not sure what to do with the info, windows might just do that. Does the discrepancy persist across reboots?

Yes it persists. I'm using wikipedia nicely formatted table as the source of the patch levels to update my query/policy, it provides the full version including patch in the public patch release table. _os_version_ falls short.