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