@KLC for instance we recently had a customer ask for the state of the Windows Powershell 2.0 which has had official support deprecated and is vulnerable to unloggable malicious code injection. If you wanted to get at that with a single query you could run something like this to check it's state via the

kolide_wmi

table:

WITH 
optional_features AS (
    SELECT  
        parent,
        key, 
        value, 
        class
    FROM kolide_wmi WHERE class = 'Win32_OptionalFeature'
    AND properties = 'name,installstate,caption'),
identify_parent AS (
    SELECT  
        DISTINCT(parent) AS parent 
    FROM optional_features 
    WHERE value LIKE '%Powershell%'),
reduce_to_powershell AS (
    SELECT * FROM optional_features, identify_parent USING (parent)),
eav_pivot AS (
    SELECT
        MAX(CASE WHEN key = 'name' THEN value END) AS name,
        MAX(CASE WHEN key = 'installstate' THEN value END) AS install_state,
        MAX(CASE WHEN key = 'caption' THEN value END) AS caption
    FROM reduce_to_powershell
    GROUP BY parent),
win32_powershell_v2 AS (
    SELECT
        name, caption,
        CASE 
        WHEN install_state = '1' THEN 'enabled'
        WHEN install_state = '2' THEN 'disabled'
        WHEN install_state = '3' THEN 'absent'
        WHEN install_state = '4' THEN 'unknown'
        END AS install_state
    FROM eav_pivot)
SELECT * FROM win32_powershell_v2;

I'm so happy you shared this with me because I didn't know osquery supported CTEs. So excited now

@KLC I ❤️ CTE's and use them in almost all of my bigger queries to help compartmentalize and work stepwise.