Useful queries

We collected some useful queries to help you start your raw data exploration.
Below, you'll find example queries for some common data sources.

AWS CloudTrail

AWS instance creation events

SELECT *
FROM RAW.AWS_CLOUDTRAIL
WHERE EVENT_TIME BETWEEN '2020-11-23' AND '2020-11-25'
AND event_name = 'RunInstances'
AND RESPONSE_ELEMENTS ilike '%i-%'
LIMIT 10;

AWS web console logins of a user over a period of time

SELECT *
FROM RAW.AWS_CLOUDTRAIL
WHERE EVENT_TIME > dateadd(day, -14, current_timestamp()) -- last 14 days
AND event_name = 'ConsoleLogin'
AND USER_IDENTITY_ARN ILIKE '%username-here%' -- enter username
ORDER BY event_time DESC
LIMIT 5;

Creation of users in AWS

SELECT *
FROM RAW.AWS_CLOUDTRAIL
WHERE EVENT_TIME BETWEEN '2020-11-23' AND '2020-11-25'
AND EVENT_NAME = 'CreateUser'
AND RESPONSE_ELEMENTS ILIKE '%username-here%' -- enter username
ORDER BY event_time DESC
LIMIT 5;

AWS web console logins for a specific User-Agent

SELECT *
FROM RAW.AWS_CLOUDTRAIL
WHERE EVENT_TIME BETWEEN '2020-11-01' AND '2020-11-30'
AND EVENT_NAME = 'ConsoleLogin'
AND USER_AGENT ILIKE '%Mozilla%' -- enter user agent
ORDER BY event_time DESC
LIMIT 5;

Azure

Windows users logging-in from multiple endpoints/countries/IP addresses

select IDENTITY,
       array_agg(distinct PROPERTIES:deviceDetail:displayName) devices_rough,
       array_size(devices_rough) device_cnt,
       array_agg(distinct PROPERTIES:deviceDetail:deviceId) device_ids,
       array_size(device_ids) device_ids_cnt,
       array_agg(distinct split_part(PROPERTIES:deviceDetail:browser, ' ', 1)) browsers_product,
       array_size(browsers_product) browsers_cnt,
       array_agg(DISTINCT PROPERTIES:location:countryOrRegion) countries,
       array_size(countries) countries_cnt,
       array_agg(distinct PROPERTIES:appDisplayName) apps,
       array_size(apps) apps_count,
       (device_cnt + device_ids_cnt + browsers_cnt + (countries_cnt * 5) + apps_count) score
    from raw.AZURE_SIGNIN
    group by IDENTITY
    order by score desc

CrowdStrike

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;

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;

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;

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;

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;