Skip to content

CrowdStrike

In this page you will find example queries for your CrowdStrike data.

  1. Display all CrowdStrike Falcon devices seen behind a specific IP, and when they were first and last seen from that IP
    SELECT AGENT_ID, MIN(START_TIME) first_seen, MAX(END_TIME) last_seen
    FROM INVESTIGATION.EDR_AIDS_EXTERNAL_IPS
    WHERE EXTERNAL_IP = '<external_ip>'
    GROUP BY AGENT_ID;
    
  2. Search for all devices with that match a specific string in their hostname

    SELECT *
    FROM INVESTIGATION.EDR_AGENT_INFO
    WHERE hostnames::VARCHAR ILIKE '%string-here%' -- hostname that matches a specific string
    GROUP BY name, email;
    

  3. Process creation statistics from a specific agent in the last 7 days

    SELECT IMAGE_FILE_NAME, COMMAND_LINE,
    COUNT(*) occurences, MIN(EVENT_TIME) first_seen, MAX(EVENT_TIME) last_seen
    FROM RAW.CROWDSTRIKE_RAW_EVENTS
    WHERE aid = '<agent_id>' -- agent id here
    AND EVENT_TIME > current_timestamp - INTERVAL '7 day'
    AND EVENT_SIMPLE_NAME IN ('ProcessRollup2', 'SyntheticProcessRollup2')
    AND COMMAND_LINE IS NOT NULL
    GROUP BY 1,2
    ORDER BY occurences DESC;
    

  4. DNS requests from "rare" processes

    SELECT iff(t2.event_platform = 'Win',
               split_part(t2.image_file_name, '\\', -1),
               split_part(t2.image_file_name, '/', -1)) as process_name,
           COUNT(DISTINCT t2.AID) aid_count,
           ARRAY_AGG(DISTINCT t1.DOMAIN_NAME) domain_names,
           ARRAY_AGG(DISTINCT t2.IMAGE_FILE_NAME) process_paths,
           ARRAY_AGG(DISTINCT t2.RAW:SHA256HashData) process_hashes
      FROM RAW.CROWDSTRIKE_RAW_EVENTS t1
     INNER JOIN RAW.CROWDSTRIKE_RAW_EVENTS t2 
        ON t1.RAW:ContextProcessId = t2.RAW:TargetProcessId AND t1.AID = t2.AID
     WHERE t1.EVENT_SIMPLE_NAME = 'DnsRequest'
       AND t2.EVENT_SIMPLE_NAME = 'ProcessRollup2'
       AND t1.EVENT_TIME > current_timestamp - INTERVAL '7 day'
       AND t2.EVENT_TIME > current_timestamp - INTERVAL '8 day'
       AND t2.event_platform = 'Win'
       AND t2.IMAGE_FILE_NAME not ilike '%\\windows\\system32\\%'
       AND t2.IMAGE_FILE_NAME not ilike '%\\program files\\%'
       AND t2.IMAGE_FILE_NAME not ilike '%\\program files (x86)\\%'
     GROUP BY process_name
     ORDER BY aid_count ASC;
    

  5. Suspicious process execution by Office Application

    SELECT RAW:ParentBaseFileName::VARCHAR initiating_process_name,
           iff(event_platform = 'Win',
               split_part(image_file_name, '\\', -1),
               split_part(image_file_name, '/', -1)) as target_process_name,
           COUNT(*) occurences,
           COUNT(DISTINCT AID) aid_count,
           ARRAY_AGG(IMAGE_FILE_NAME) process_paths,
           ARRAY_AGG(COMMAND_LINE) commandlines,
           MIN(EVENT_TIME) first_seen, MAX(EVENT_TIME) last_seen
      FROM RAW.CROWDSTRIKE_RAW_EVENTS
     WHERE EVENT_TIME > current_timestamp - INTERVAL '7 day'
       AND EVENT_SIMPLE_NAME IN ('ProcessRollup2', 'SyntheticProcessRollup2')
       AND COMMAND_LINE IS NOT NULL
       AND initiating_process_name ILIKE ANY ('%winword%', '%excel%', '%powerpnt%')
       AND NOT target_process_name ILIKE ANY ('%werfault.exe', '%winword.exe', '%excel.exe',
       '%splwow64.exe', '%sgtool.exe', '%outlook.exe', '%powerpnt.exe')
     GROUP BY 1,2
     ORDER BY occurences;