Use Cases (example queries)
RoleAnalystRead10 min
Ready-to-copy examples. Swap the table name and columns for your integration; all of them use **Presto SQL**.
What it's for
Presto SQL query templates for common investigation tasks. Change the table (cloudtrail, google_admin, etc.) and the single-quoted values.
Before you start
- Always bound the time range (editor selector or
WHERE "time" BETWEEN ...). - Double-quote tables and columns; single-quote string values.
Tips
- When a query becomes recurrently useful, turn it into a detection rule (shield icon) to alert automatically.
- To investigate an incident with many indicators, use Incident Response: paste the logs, extract IOCs, and the query is generated for you.
Explore a source
- See everything (bounded):
SELECT * FROM "cloudtrail" LIMIT 100 - Key columns only:
SELECT "time", "activity_name", "actor_user_name", "src_endpoint_ip" FROM "cloudtrail" ORDER BY "time" DESC LIMIT 100 - Distinct values:
SELECT DISTINCT "workgroup", "version_id" FROM "cloudtrail"
Access and authentication
- Console logins (AWS):
SELECT "time", "actor_user_name", "src_endpoint_ip" FROM "cloudtrail" WHERE "activity_name" = 'ConsoleLogin' ORDER BY "time" DESC LIMIT 100 - Activity for one user:
SELECT "time", "activity_name", "src_endpoint_ip" FROM "cloudtrail" WHERE "actor_user_name" = 'alice' ORDER BY "time" DESC LIMIT 200
Counts and trends (aggregations)
- Top IPs by activity:
SELECT "src_endpoint_ip", COUNT(*) AS events FROM "cloudtrail" GROUP BY "src_endpoint_ip" ORDER BY events DESC LIMIT 20 - Events by activity type:
SELECT "activity_name", COUNT(*) AS total FROM "cloudtrail" GROUP BY "activity_name" ORDER BY total DESC LIMIT 50
Indicator (IOC) search
- Suspicious IPs (list):
SELECT * FROM "cloudtrail" WHERE "src_endpoint_ip" IN ('1.2.3.4', '5.6.7.8') LIMIT 100 - Pattern in parameters:
SELECT * FROM "cloudtrail" WHERE CAST("requestParameters" AS TEXT) LIKE '%secret-bucket%' LIMIT 100 - Event name contains text:
SELECT * FROM "cloudtrail" WHERE CAST("eventName" AS TEXT) LIKE '%Delete%' LIMIT 100