Lookup Tables

What it's for

Create, import, and maintain reference lists in Management → Lookup Tables, and use them in Logs and Detection Rules via the Lists button.

Before you start

• Admin permissions on /management/lookup-tables.
• For CSV import: the first row is the column headers.
• Create tables before referencing them in SQL or rules — if the table is missing or empty, the query fails.

Expected result

Tips

• For large lists (IPs, hashes, domains) use Quick List or CSV instead of row-by-row entry.
• In detection rules, prefer Reference over Value list — update the list without editing every rule.
• Pick the right column type: numeric and date comparisons depend on it.

Access the panel

• MANAGEMENT → LOOKUP TABLES (route /management/lookup-tables).
• Screen subtitle: Reusable reference sets for SQL queries and detection rules.

The screen — three areas

• Left — Tables: table list with Filter tables… search and a + button to create. Each row shows name and {rows} rows · {cols} cols.
• Center — detail: selecting a table shows its name (inline-editable), counters, tags, data grid, and Export, Add Row, and delete-table actions.
• Right — Create Table (slide-in panel): opens with + and has three tabs: Manual, Quick List, and CSV.

Create · Manual

• Table Name — unique name (e.g. blocked_ips); used in {{placeholders}}. If it already exists, Affinity auto-appends a date suffix.
• Description — what the table contains.
• Columns — name + type (string, number, boolean, date). Starts with key and value; Add column for more.
• Tags — comma-separated labels (e.g. threat-intel, malware).
• Confirm with Create Table. The table starts empty; add rows with Add Row or inline cell editing.

Create · Quick List

• Ideal for simple lists: IPs, domains, hashes, etc.
• List Name — table name (e.g. blocked_ips).
• Column Name — column name (default value).
• Values — paste plain-text values; delimiter auto-detected (newline, comma, or semicolon).
• Preview: {N} values detected with the first values shown.
• Confirm with Create List ({N} rows).

Create · CSV

• Drag or select a .csv file; the first row defines columns.
• Preview: {N} rows · {M} columns detected with a mini-table of the first rows.
• Table Name and Description (optional).
• Confirm with Import CSV.

Manage data

• Click any cell to edit inline · Enter to save · Esc to cancel — direct grid editing.
• Add Row — adds an empty row (null values).
• Hover a row → trash icon to delete it.
• Click the table name → rename inline.
• Export — downloads CSV; delete the table with the header trash icon (asks for confirmation).

Use in Logs — Lists button

• In LOGS → Query Editor, the Lists button (book icon) opens a popover with your lookup tables.
• Search with Search lookup tables…, pick a table, and choose an insert mode:
• Reference ✦ recommended — inserts ({{table_name}}) or ({{table_name.column}}). Recommended: values resolve at query time and stay in sync when you update the list.
• Value list — inserts literals 'a','b','c' (static snapshot in SQL).
• IN list — inserts IN ('a','b','c') (ad-hoc Logs filter).
• WITH CTE — inserts WITH lookup_name AS (SELECT * FROM (VALUES …)) to use the table as a subquery.
• The editor highlights {{…}} in amber and shows a hover popup with resolved values.

Placeholder syntax

• Single-column table trusted_ips: WHERE "src_endpoint_ip" NOT IN ({{trusted_ips}})
• Multi-column table vip_users, column email: WHERE "actor_user_uid" IN ({{vip_users.email}})
• At query time the engine resolves placeholders to SQL literals.
• Placeholders work in Logs (Run Query) and the alerts engine (Detection Rules).

Use in Detection Rules — Lists button

• In the Add Detection Rule wizard, step 1 (Detection Logic), the toolbar has the same Lists button.
• Use Reference ✦ recommended mode so the rule stays in sync when you change the list — don't hardcode IPs in SQL.
• Example: SELECT time, activity_name, src_endpoint_ip FROM cloudtrail WHERE src_endpoint_ip NOT IN ({{blocked_ips}})
• The detection engine resolves {{lookup_table}} before executing the query. Saved queries must be SELECT … FROM … WHERE … (no ORDER BY, LIMIT, etc.).

See also