• f

    fritz

    2 years ago
    @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;
  • k

    KLC

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

    fritz

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