---
title: "Analytics tables reference"
slug: "analytics-tables-reference"
updated: 2026-10-27T16:31:17Z
published: 2026-10-27T16:31:17Z
canonical: "docs.hunters.ai/analytics-tables-reference"
---

> ## 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.

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

| Table | One row per | Use it for |
| --- | --- | --- |
| `alerts` | Lead that reached your analysts | Everything about SOC workload: alert counts, triage, response times |
| `leads` | Detection, including leads that were suppressed or scored too low to alert | Detection volume and detection details |

`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.

              `COUNT(*) FROM leads` is not your alert count

              

`leads` includes suppressed leads that no analyst ever saw. To count what reached your SOC, count `alerts`.

## The main tables

| Table | What's inside | Join to leads on | Rows per lead |
| --- | --- | --- | --- |
| `leads` | `uuid` (lead ID), `id` (**detector name**, not a row ID), `start_time`, `created_at`, `end_time`, `specific_source_type`, `attrs` (the detection's attributes, JSON) | — | 1 |
| `alerts` | `uuid`, `start_time`, `display_name`, `score`, `risk`, `confidence`, `severity`, `queue_status` | `alerts.uuid = leads.uuid` | 0 or 1 |
| `leads_score` | `score` (0–100), `risk` (1–4), `confidence` (1–10), `severity` (1–10) as numbers | `lead_uuid` | 1 |
| `leads_classifications` | Analyst triage: `queue_status` (`open`, `wip`, `done`) and `maliciousness_status` (`benign`, `malicious`, `unknown`) | `lead_uuid` | 0 or 1 |
| `assignees` | Current assignee | `assignee_to = leads.uuid` and `assignee_scope = 'lead'` | 0 or 1 |
| `users` | `name`, `email` | `users.id::varchar = assignees.user_sub` | — |
| `leads_clusters` | The cluster a lead belongs to (`threat_uuid`) | `lead_uuid` | 0 or 1 |
| `analytic_v2` | Detector catalog: `display_name`, `native_detector`, `is_custom_detector` | `analytic_v2.name = leads.id` | 1 |
| `event_logs` | Audit trail of status changes, assignments, verdicts and comments (`type`, `parameters`, `created_at`) | `object_id = leads.uuid` and `scope = 'leads'` | **Many** |
| `ai_investigation` | Pathfinder runs: `state`, `verdict` (`BENIGN`, `MALICIOUS`, `INCONCLUSIVE`), `explanation` | `lead_uuid` | **Many** |
| `mega_entities` | Entities involved in a lead (hosts, users, processes): `type`, `role`, `display_name`, `attributes` | `lead_uuid` | **Many** |

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