Crowdstrike Queries

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

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;

CODE

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;

CODE

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;

CODE

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;

CODE

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;

CODE