---
title: "Useful queries"
slug: "useful-queries"
updated: 2023-06-04T11:33:25Z
published: 2023-06-04T11:33:25Z
canonical: "docs.hunters.ai/useful-queries"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.hunters.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# 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;
```