Analytics tables reference

The Analytics data type holds what Hunters produces from your events: leads, alerts, scores, triage state, assignments, Pathfinder investigations and the audit trail. This page describes the main tables and how to join them. The Resources panel lists every table and its columns.

Analytics SQL is PostgreSQL. Write table names without a schema prefix, for example leads, not investigation.leads.

Start from alerts or leads

alerts is a view over leads, and alerts.uuid equals leads.uuid. alerts already carries readable labels (risk, confidence, severity, queue_status and the detector's display_name), so you rarely need to decode the scores yourself.

The main tables

Other tables cover detector-to-MITRE mapping (analytics_techniques, technique, techniques_tactics, tactic), products and data types (data_type, product), stories (persistent_stories_analysis) and ignore rules (ignore_rule_stats).

Joining safely

A join to a table with many rows per lead repeats each lead once per matching row, and every count, sum and average after it is inflated. Nothing warns you.

• One row per lead (leads_score, leads_classifications, leads_clusters, alerts, assignees, analytic_v2): LEFT JOIN directly.
• event_logs: aggregate per lead first, with GROUP BY or a subquery.
• ai_investigation: keep the latest run per lead with DISTINCT ON (lead_uuid) ... ORDER BY lead_uuid, created_at DESC, and always filter is_discarded = false.
• mega_entities: filter by role, then keep one row per lead with DISTINCT ON (lead_uuid).

Gotchas

• Always add a time filter, such as start_time >= current_timestamp - interval '30 days'. Queries the platform estimates to be too expensive are refused before they run.
• event_logs: filter scope = 'leads' with equality, and add jsonb_typeof(parameters) = 'object' before you read parameters. Status changes are type = 'TYPE_SET_STATUS' with parameters ->> 'newStatus'; assignments are type = 'TYPE_ASSIGN'.
• maliciousness_status can be an empty string as well as NULL. Use COALESCE(NULLIF(maliciousness_status, ''), 'Not set'), or the chart gets a blank category.
• Some timestamps have no time zone: event_logs.created_at, leads_score.updated_at and leads_clusters.created_at. To compute a duration, subtract epochs: EXTRACT(EPOCH FROM a) - EXTRACT(EPOCH FROM b).
• No literal %, including in LIKE patterns. Use position('text' in column) > 0 instead of LIKE '%text%'.
• One wide query can serve many widgets. Return one row per alert with every column you want to slice by, then give each widget its own grouping. On one dashboard, widgets with identical SQL share one cached result.

Example queries

Each example returns one row per alert or per lead, so you can build several widgets from it. Nominate start_time (or created_at) as the widget's time column.

Alerts with detector, risk and status. Use it for alerts per detector, risk distribution and the open queue.

SELECT
  alerts.uuid,
  alerts.start_time,
  alerts.display_name AS detector,
  alerts.risk,
  alerts.queue_status
FROM alerts
WHERE alerts.start_time >= current_timestamp - interval '30 days'

Triage: verdict and assignee per alert.

SELECT
  alerts.uuid,
  alerts.start_time,
  alerts.display_name AS detector,
  alerts.risk,
  alerts.queue_status,
  COALESCE(NULLIF(leads_classifications.maliciousness_status, ''), 'Not set') AS analyst_verdict,
  COALESCE(users.name, 'Unassigned') AS assignee
FROM alerts
LEFT JOIN leads_classifications ON leads_classifications.lead_uuid = alerts.uuid
LEFT JOIN assignees ON assignees.assignee_to = alerts.uuid
                   AND assignees.assignee_scope = 'lead'
LEFT JOIN users ON users.id::varchar = assignees.user_sub
WHERE alerts.start_time >= current_timestamp - interval '30 days'

Time to acknowledge (MTTA). An alert counts as acknowledged at its first assignment, or when it is first set to wip or done. Alerts never acknowledged return NULL. Average hours_to_acknowledge in a Gauge or Bar widget.

SELECT
  alerts.uuid,
  alerts.start_time,
  alerts.display_name AS detector,
  alerts.risk,
  (EXTRACT(EPOCH FROM MIN(event_logs.created_at) FILTER (
       WHERE event_logs.type = 'TYPE_ASSIGN'
          OR (event_logs.type = 'TYPE_SET_STATUS'
              AND event_logs.parameters ->> 'newStatus' IN ('wip', 'done'))))
   - EXTRACT(EPOCH FROM leads.created_at)) / 3600 AS hours_to_acknowledge
FROM alerts
JOIN leads ON leads.uuid = alerts.uuid
LEFT JOIN event_logs ON event_logs.object_id = alerts.uuid
                    AND event_logs.scope = 'leads'
                    AND jsonb_typeof(event_logs.parameters) = 'object'
WHERE alerts.start_time >= current_timestamp - interval '30 days'
GROUP BY alerts.uuid, alerts.start_time, alerts.display_name, alerts.risk, leads.created_at

Pathfinder verdicts: the latest completed investigation per lead.

SELECT DISTINCT ON (lead_uuid)
  lead_uuid,
  created_at,
  verdict
FROM ai_investigation
WHERE is_discarded = false
  AND state = 'COMPLETED'
  AND created_at >= current_timestamp - interval '30 days'
ORDER BY lead_uuid, created_at DESC