Ian
05/17/2023, 10:31 AMSELECT
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
05/17/2023, 1:06 PMSELECT
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;
seph
05/17/2023, 1:07 PMNote: I’m not using os_version build numbers because they are not reliable.Hrm. Can you say more?
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 itV
05/17/2023, 1:11 PMbuild
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 caseSELECT
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.
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;
seph
05/17/2023, 1:20 PMbuild
isn’t getting the cast, but the underlying column. Which is probably fine here anyhowIan
05/17/2023, 1:59 PMV
05/17/2023, 2:08 PMkernel_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-operatingsystemseph
05/17/2023, 2:09 PMIan
05/17/2023, 2:12 PM