https://github.com/osquery/osquery logo
#general
Title
# general
i

Ian

05/17/2023, 10:31 AM
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.
Copy code
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;
v

V

05/17/2023, 1:06 PM
This statement works as expected
Copy code
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;
s

seph

05/17/2023, 1:07 PM
Note: I’m not using os_version build numbers because they are not reliable.
Hrm. Can you say more?
One thing I wonder, is whether or not having the
CASE
refer to the previous column defines works. SQL is super inconsistent about it. For example, if you create a column like that, you can’t reference it in a where or groupby. So sometimes I bounce things through a CTE to handle it
v

V

05/17/2023, 1:11 PM
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
E.g. Just using the build column in the below statement provides the correct output.
Copy code
SELECT 
    CAST(os_version.build AS integer) AS build, 
    CAST(SPLIT(kernel_info.version, '.', 3) AS integer) AS patch,
    CASE 
        WHEN build >= 19045 THEN 'Yes' 
        WHEN build >= 22621 THEN 'Yes' 
        ELSE 'No' 
    END AS is_compliant 
FROM kernel_info, os_version;
Just using the patch column in the below statement does not provide the correct output.
Copy code
SELECT 
    CAST(os_version.build AS integer) AS build, 
    CAST(SPLIT(kernel_info.version, '.', 3) AS integer) AS patch,
    CASE 
        WHEN patch >= 2965 THEN 'Yes' 
        ELSE 'No' 
    END AS is_compliant 
FROM kernel_info, os_version;
s

seph

05/17/2023, 1:20 PM
There’s some chance
build
isn’t getting the cast, but the underlying column. Which is probably fine here anyhow
i

Ian

05/17/2023, 1:59 PM
@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.
os_version does not give me the patch levels detail I want but kernel_info.version does 🤷
v

V

05/17/2023, 2:08 PM
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
s

seph

05/17/2023, 2:09 PM
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?
i

Ian

05/17/2023, 2:12 PM
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.
4 Views