outbound_connection_initiated_by_script_interpreters.sql

-- The query looks for a newly created script executed with WScript/CScript initiating an outbound network connection
WITH net_conn AS (
    SELECT DISTINCT EVENT_TIME                       AS CONN_EVENT_TIME,
                    EVENT_ID                         AS CONN_EVENT_ID,
                    AGENT_ID                         AS CONN_AGENT_ID,
                    INITIATING_PROCESS_NAME          AS CONN_INITIATING_PROCESS_NAME,
                    INITIATING_PROCESS_UID           AS CONN_INITIATING_PROCESS_UID,
                    INITIATING_PROCESS_COMMANDLINE   AS CONN_INITIATING_PROCESS_COMMANDLINE,
                    REMOTE_IP,
                    DOMAIN,
                    REMOTE_PORT,
                    PROTOCOL,
                    IS_INBOUND
    FROM INVESTIGATION.EDR_NETWORK_EVENTS
    WHERE IS_INBOUND = FALSE
      AND EVENT_TIME > CURRENT_TIMESTAMP - INTERVAL '60d'
      and (DOMAIN LIKE '%.%' OR DOMAIN IS NULL)
      --Filter out internal IP ranges
      AND (NOT (REMOTE_IP LIKE '10.%'
        OR REMOTE_IP LIKE '192.168.%'
        OR REMOTE_IP LIKE '127.%'
        OR REMOTE_IP REGEXP '^172\\.(1[6-9]|2[0-9]|3[0-1])\\..*' -- 172.16.0.0–172.31.255.255
        OR REMOTE_IP LIKE '169.254.%'
        OR REMOTE_IP LIKE '0.%'
        OR REMOTE_IP REGEXP
           '^100\\.(6[4-9]|[7-9][0-9]|1[0-1][0-9]|12[0-7])\\..*' -- 100.64.0.0–100.127.255.255
        OR REMOTE_IP LIKE '192.0.0.%'
        OR REMOTE_IP LIKE '192.0.2.%'
        OR REMOTE_IP LIKE '192.88.99.%'
        OR REMOTE_IP REGEXP '^198.1[8-9]\\..*'
        OR REMOTE_IP LIKE '198.51.100.%'
        OR REMOTE_IP LIKE '203.0.113.%'
        OR REMOTE_IP LIKE '233.252.0.%'
        OR REMOTE_IP REGEXP '^(22[4-9]|23[0-9])\\..*'
        OR REMOTE_IP REGEXP '^(24[0-9]|25[0-5])\\..*'
        OR REMOTE_IP like 'fc00%'
        OR REMOTE_IP like 'fe80%'
        OR REMOTE_IP like 'ff00%'
        OR REMOTE_IP = '::1') OR (REMOTE_IP IS NULL AND NOT DOMAIN IS NULL))
),
     wscript_cscript AS (
         SELECT DISTINCT EVENT_TIME                       AS WSCRIPT_CSCRIPT_EVENT_TIME,
                         EVENT_ID                         AS WSCRIPT_CSCRIPT_EVENT_ID,
                         AGENT_ID                         AS WSCRIPT_CSCRIPT_AGENT_ID,
                         TARGET_PROCESS_PATH              AS WSCRIPT_CSCRIPT_TARGET_PROCESS_PATH,
                         TARGET_PROCESS_COMMANDLINE       AS WSCRIPT_CSCRIPT_TARGET_PROCESS_COMMANDLINE,
                         IFF(LOWER(TARGET_PROCESS_COMMANDLINE) LIKE '%temp\\rar$%', TRUE,
                             FALSE)                          WSCRIPT_CSCRIPT_EXTRACTED_WITH_WINRAR,
                         TARGET_PROCESS_UID               AS WSCRIPT_CSCRIPT_TARGET_PROCESS_UID,
                         TARGET_PROCESS_OS_PID            AS WSCRIPT_CSCRIPT_TARGET_PROCESS_OS_PID,
                         TARGET_PROCESS_WINDOWS_INTEGRITY AS WSCRIPT_CSCRIPT_TARGET_PROCESS_WINDOWS_INTEGRITY,
                         TARGET_PROCESS_HASH_SHA256       AS WSCRIPT_CSCRIPT_TARGET_PROCESS_HASH_SHA256,
                         TARGET_PROCESS_WINDOWS_USER_SID  AS WSCRIPT_CSCRIPT_TARGET_PROCESS_WINDOWS_USER_SID,
                         INITIATING_PROCESS_NAME          AS WSCRIPT_CSCRIPT_INITIATING_PROCESS_NAME,
                         INITIATING_PROCESS_UID           AS WSCRIPT_CSCRIPT_INITIATING_PROCESS_UID,
                         INITIATING_PROCESS_COMMANDLINE   AS WSCRIPT_CSCRIPT_INITIATING_PROCESS_COMMANDLINE,
                        PARENT_PROCESS_NAME               AS WSCRIPT_CSCRIPT_PARENT_PROCESS_NAME,
                        PARENT_PROCESS_COMMANDLINE        AS WSCRIPT_CSCRIPT_PARENT_PROCESS_COMMANDLINE
         FROM INVESTIGATION.EDR_PROCESS_CREATION_EVENTS
         WHERE LOWER(TARGET_PROCESS_NAME) IN ('wscript.exe', 'cscript.exe')
           AND EVENT_TIME > CURRENT_TIMESTAMP - INTERVAL '60d'
     ),
     script_written AS (
         SELECT DISTINCT EVENT_TIME                                                                   AS SCRIPT_WRITTEN_EVENT_TIME,
                         EVENT_ID                                                                     AS SCRIPT_WRITTEN_EVENT_ID,
                         AGENT_ID                                                                     AS SCRIPT_WRITTEN_AGENT_ID,
                         TARGET_FILE_NAME                                                             AS SCRIPT_WRITTEN_TARGET_FILE_NAME,
                         TARGET_FILE_PATH                                                             AS SCRIPT_WRITTEN_TARGET_FILE_PATH,
                         TARGET_FILE_HASH_SHA256                                                      AS SCRIPT_WRITTEN_TARGET_FILE_HASH_SHA256,
                         TARGET_FILE_EXTENSION                                                        AS SCRIPT_WRITTEN_TARGET_FILE_EXTENSION,
                         TARGET_FILE_TYPE                                                             AS SCRIPT_WRITTEN_TARGET_FILE_TYPE,
                         TARGET_FILE_ACTION                                                           AS SCRIPT_WRITTEN_TARGET_FILE_ACTION,
                         INITIATING_PROCESS_COMMANDLINE                                               AS SCRIPT_WRITTEN_INITIATING_PROCESS_COMMANDLINE,
                         INITIATING_PROCESS_NAME                                                      AS SCRIPT_WRITTEN_INITIATING_PROCESS_NAME,
                         INITIATING_PROCESS_UID                                                       AS SCRIPT_WRITTEN_INITIATING_PROCESS_UID,
                         COUNT(DISTINCT AGENT_ID) OVER (PARTITION BY SCRIPT_WRITTEN_TARGET_FILE_NAME) AS SCRIPT_WRITTEN_AGENT_COUNT
         FROM INVESTIGATION.EDR_FILE_EVENTS
         WHERE EVENT_TIME > CURRENT_TIMESTAMP - INTERVAL '60d'
           AND LOWER(TARGET_FILE_NAME) LIKE ANY ('%.tmp', '%.wsf', '%.jse', '%.js', '%.vba', '%.vbe', '%.vbs')
        QUALIFY SCRIPT_WRITTEN_AGENT_COUNT <= 10)
SELECT *
FROM net_conn
         INNER JOIN wscript_cscript
                    ON net_conn.CONN_INITIATING_PROCESS_UID = wscript_cscript.WSCRIPT_CSCRIPT_TARGET_PROCESS_UID
                        AND net_conn.CONN_AGENT_ID = wscript_cscript.WSCRIPT_CSCRIPT_AGENT_ID
                        AND
                       net_conn.CONN_EVENT_TIME BETWEEN wscript_cscript.WSCRIPT_CSCRIPT_EVENT_TIME AND wscript_cscript.WSCRIPT_CSCRIPT_EVENT_TIME + INTERVAL '5m'
         INNER JOIN script_written
                    ON net_conn.CONN_AGENT_ID = script_written.SCRIPT_WRITTEN_AGENT_ID
                        AND
                       net_conn.CONN_EVENT_TIME BETWEEN script_written.SCRIPT_WRITTEN_EVENT_TIME AND script_written.SCRIPT_WRITTEN_EVENT_TIME + INTERVAL '5m'
                        AND wscript_cscript.WSCRIPT_CSCRIPT_TARGET_PROCESS_COMMANDLINE LIKE
                            '%' || script_written.SCRIPT_WRITTEN_TARGET_FILE_NAME || '%';