How To: UEBA with Actor Baselines

User and Entity Behavior Analytics (UEBA) compares activity to what is normal for that specific user or service account, not a one-size-fits-all rule. A 2am login can be routine for a night-shift SRE and highly anomalous for an accountant. Per-entity baselines know the difference.

RunReveal approaches UEBA as something you build on your unified logs with inspectable SQL: automatic hourly actor rollups, z-score style anomaly queries, composable detections, and AI Chat investigations that show their work.

For the architecture narrative behind this model, see Rethinking UEBA for the Modern Cloud.

What you get out of the box

As events land in logs, ClickHouse incrementally rolls them into ueba_actor_hourly via the ueba_actor_hourly_mv materialized view:

• One row key per workspace, actor email (principal), sourceType, and hour (hourBucket)
• Aggregates: event counts plus unique IPs, countries, ASNs, event names, and resources
• Retention: 550-day TTL on hourBucket (aligned with default log retention)
• Filter: only events where actor['email'] != '' are included (high-volume email-less sources are skipped)

You do not create or manage the materialized view. You query the table and build detections on top of it.

Prerequisites

1. Identity-rich sources with normalized actor['email'], for example Okta, Google Workspace, CloudTrail (when actor email is present), GitHub, and similar
2. Optional but useful for geo signals: enrichment that populates srcASCountryCode and srcASNumber
3. Enough history to baseline (plan on at least 7 days of activity per principal before trusting z-score detections; 30 days is a common window)

Schema: ueba_actor_hourly

hourOfWeek is not stored. Derive it when needed:

toUInt16((toDayOfWeek(hourBucket) - 1) * 24 + toHour(hourBucket)) AS hourOfWeek

Sample the table

In Explore, confirm baselines are filling:

SELECT
  principal,
  sourceType,
  hourBucket,
  sum(events) AS events,
  groupUniqArrayArray(ips) AS ips,
  groupUniqArrayArray(countries) AS countries,
  groupUniqArrayArray(asns) AS asns,
  groupUniqArrayArray(eventNames) AS eventNames,
  groupUniqArrayArray(resources) AS resources
FROM ueba_actor_hourly
WHERE hourBucket >= now() - INTERVAL 7 DAY
GROUP BY principal, sourceType, hourBucket
ORDER BY hourBucket DESC
LIMIT 100

If this returns no rows, check that your sources populate actor['email'] on events in logs.

Signals vs alerts

UEBA works best when you keep signals (observations worth recording) separate from alerts (things that should page someone):

In RunReveal, both appear as detection results. Prefer starting UEBA volume detections as signals, then promote noisy-but-true patterns to alerts after tuning. See Detections, Signals, and Alerts.

Step-by-step: anomalous hourly volume

WITH actor_hours AS (
  SELECT
    principal,
    sourceType,
    hourBucket,
    sum(events) AS events,
    groupUniqArrayArray(ips) AS ips,
    groupUniqArrayArray(countries) AS countries,
    groupUniqArrayArray(asns) AS asns,
    groupUniqArrayArray(eventNames) AS eventNames
  FROM ueba_actor_hourly
  WHERE hourBucket >= {from:DateTime} AND hourBucket < {to:DateTime}
  GROUP BY principal, sourceType, hourBucket
),
actor_baseline AS (
  SELECT
    principal,
    sourceType,
    avg(events) AS avgEvents,
    stddevPop(events) AS stdEvents
  FROM actor_hours
  GROUP BY principal, sourceType
)
SELECT
  u.principal,
  u.sourceType,
  u.hourBucket,
  toUInt16((toDayOfWeek(u.hourBucket) - 1) * 24 + toHour(u.hourBucket)) AS hourOfWeek,
  u.events,
  round(b.avgEvents, 2) AS avgEvents,
  round((u.events - b.avgEvents) / nullIf(b.stdEvents, 0), 2) AS zScore,
  length(arrayDistinct(u.ips)) AS uniqueIPs,
  arrayDistinct(u.countries) AS countries,
  arrayDistinct(u.asns) AS asns,
  arraySlice(arrayDistinct(u.eventNames), 1, 10) AS sampleEventNames
FROM actor_hours AS u
INNER JOIN actor_baseline AS b
  ON u.principal = b.principal AND u.sourceType = b.sourceType
WHERE (u.events - b.avgEvents) / nullIf(b.stdEvents, 0) > 3
ORDER BY zScore DESC

WITH daily AS (
  SELECT
    principal,
    sourceType,
    toDate(hourBucket) AS d,
    sum(events) AS ev
  FROM ueba_actor_hourly
  WHERE hourBucket >= {from:DateTime} AND hourBucket < {to:DateTime}
  GROUP BY principal, sourceType, d
),
baseline AS (
  SELECT
    principal,
    sourceType,
    avg(ev) AS meanEv,
    stddevSamp(ev) AS sdEv,
    count() AS daysSeen
  FROM daily
  WHERE d < toDate({to:DateTime}) - 1
  GROUP BY principal, sourceType
),
latest AS (
  SELECT
    principal,
    sourceType,
    sum(ev) AS dayEv
  FROM daily
  WHERE d = toDate({to:DateTime}) - 1
  GROUP BY principal, sourceType
)
SELECT
  l.principal,
  l.sourceType,
  l.dayEv,
  round(b.meanEv, 1) AS meanEv,
  round(b.sdEv, 1) AS sdEv,
  b.daysSeen,
  round((l.dayEv - b.meanEv) / nullIf(b.sdEv, 0), 2) AS zscore
FROM latest AS l
INNER JOIN baseline AS b USING (principal, sourceType)
WHERE b.daysSeen >= 7
  AND b.sdEv > 0
  AND l.dayEv > 20
  AND (l.dayEv - b.meanEv) / nullIf(b.sdEv, 0) >= 3
ORDER BY zscore DESC

More UEBA patterns to try

Once volume baselines work, compose additional SQL detections on the same table (and join back to logs when you need raw context):

Keep each building block modest. Correlate in a higher-level detection or investigation when combinations matter (unusual login + token creation + privileged API calls).

Best practices

1. Baseline per principal, not against the whole org. Service accounts and CI bots will dominate population stats
2. Prefer signals first, then alerts after you have history and exclusions
3. Document thresholds in detection notes so the next analyst can audit why >= 3 and dayEv > 20 were chosen
4. Ensure actor['email'] quality on critical sources; empty emails never enter ueba_actor_hourly
5. Give the MV time after connecting new sources. There is no automatic historical backfill of the baseline table from before the MV existed
6. Investigate with receipts: use AI Chat and Explore side by side; do not trust a score you cannot re-query

Related

• Blog: Rethinking UEBA for the Modern Cloud
• Writing detections
• Detections, Signals, and Alerts
• Native AI Chat
• Agents: schedule recurring hunts (for example Weekly Threat Hunt)