SQL Queries (Presto)
RoleAnalystRead12 min
Route: /logs → Query Editor
Affinity runs your queries with **Presto SQL** (standard SQL): if you know SQL, you already know how to query your logs.
What it's for
Write and run SQL queries over your security events. Learn the Presto syntax, the quoting rules, and the editor's time controls.
Before you start
- At least one integration with ingested data must exist.
- Access permission to
/logs. - Each integration exposes a table (e.g.
cloudtrail,google_admin,azure_sql_audit).
Steps
- 1
Go to LOGS → Query Editor.
- 2
Write your query in the tab (
Query 1). Minimal example:SELECT * FROM "cloudtrail"; - 3
Pick the time range: 1h / 3h / 12h / 1d / 1w / Custom.
- 4
(Optional) Change the timezone with the UTC / UTC-3 selector.
- 5
Click Run Query (to cancel a long query: Stop Query → shows Stopping...).
- 6
The editor switches to Table View automatically to show results.
Expected result
- The table shows the result rows and the
{shown} / {total} resultscounter. - Query Execution Statistics section with times and data scanned.
Common errors
| Symptom | What to do |
|---|---|
| `No Logs To Display` / `Run a query or adjust filters to see results.` | Run the query or expand the time range |
| `No results found` | Adjust filters; **Clear search to see all logs** |
| Column / reserved-word error | Wrap the identifier in double quotes: `"..."` |
| Slow or expensive query | Reduce the time range, select only the columns you need, and add `LIMIT` |
Tips
- The table name matches the integration. If you don't remember the columns, open the Query Builder and look at the source's column list.
- For incident investigations, you can send IOCs from Incident Response and the query is generated automatically.
Syntax rules (Presto)
- Double-quote identifiers: table and column names go in
"..."(e.g.SELECT "activity_name" FROM "cloudtrail"). Required when the name is a reserved word. - Single-quote string literals:
WHERE "activity_name" = 'ConsoleLogin'. To escape a single quote, double it:'O''Reilly'. - `LIKE` wildcard: use
%(any sequence) —WHERE "http_request_url_path" LIKE '%/admin%'. - `IN` lists:
WHERE "src_endpoint_ip" IN ('1.2.3.4', '5.6.7.8'). - Filter by time:
WHERE "time" BETWEEN ... AND ...(or use the editor's range selector). - Always bound with `LIMIT` while exploring (e.g.
LIMIT 100) for fast, cheap responses.
Query structure
SELECT <columns> FROM "<table>" WHERE <conditions> GROUP BY <columns> ORDER BY <column> [ASC|DESC] LIMIT <n>- Available aggregations:
COUNT,SUM,AVG,MIN,MAX. - Filter operators:
=,!=,>,<,>=,<=,LIKE,IN,NOT IN,IS NULL,IS NOT NULL.
Editor tools
- Run Query / Stop Query, Clear Query, Format SQL.
- SQL Intellisense Help (table/column autocomplete), Lists, and Query History (clock icon).
- Lists — insert lookup table references (
{{table_name}}) created in Management → Lookup Tables (see Lookup Tables). - Show/Hide Execution Statistics and Share Query (share query + results via link).
- Query Builder — if you'd rather not hand-write SQL (see Visual Query Builder).
Lookup tables in queries (Lists)
- Lookup tables are managed at
/management/lookup-tables, not in Logs. - In the editor, Lists opens a popover to search tables and insert them into SQL.
- Recommended mode Reference ✦: inserts
({{blocked_ips}})— values resolve when the query runs. - You can also use Value list, IN list, or WITH CTE for ad-hoc investigation.
- Example:
WHERE "src_endpoint_ip" NOT IN ({{trusted_ips}})