How to aggregate session events in Real-Time Analytics: step by step

This tutorial shows how to aggregate session events in Real-Time Analytics to obtain per-session metrics (duration, event count, last attributes). Aggregating by session is useful for behavioral analysis, measuring retention and detecting abnormal experiences in real time.

Prerequisites

• Account and cluster with Real-Time Analytics support (for example, Azure Data Explorer / Kusto).
• Event data with a session identifier (sessionId), timestamp and event type.
• Basic knowledge of KQL (Kusto Query Language) and data ingestion.

Step 1: understand the goal and the schema

Before writing the query it is important to know which columns exist: sessionId, timestamp (datetime), eventType and, optionally, userId and properties. The goal is to aggregate by sessionId and produce metrics: start, end, duration, event_count, last_event_type.

Step 2: filter and normalize events

Filter the relevant time range and normalize the timestamp. This reduces processing cost and avoids including old sessions or events outside the window.

Events
| where Timestamp between (ago(1h) .. now())
| where isnotempty(sessionId)

Step 3: aggregate by session for basic metrics

Use summarize to get start, end and count. start=min(Timestamp) and end=max(Timestamp) provide the session period; duration is the difference between them.

Events
| where Timestamp between (ago(1h) .. now())
| where isnotempty(sessionId)
| summarize
    session_start = min(Timestamp),
    session_end = max(Timestamp),
    event_count = count()
  by sessionId, userId
| extend duration_sec = todouble(session_end - session_start) / 1s

Step 4: get the last event and other attributes per session

To know the last event type or the last value of a property use arg_max which selects the row with the highest timestamp per session.

let sessions_basic = Events
| where Timestamp between (ago(1h) .. now())
| where isnotempty(sessionId)
| summarize
    session_start = min(Timestamp),
    session_end = max(Timestamp),
    event_count = count()
  by sessionId, userId;

let sessions_last = Events
| where Timestamp between (ago(1h) .. now())
| where isnotempty(sessionId)
| summarize arg_max(Timestamp, *) by sessionId;

sessions_basic
| join kind=inner (sessions_last) on sessionId
| project sessionId, userId, session_start, session_end, duration_sec, event_count, last_event_type = eventType, last_properties = properties

Step 5: handle inactive sessions and false positives

Not all sessionId values represent valid sessions. Define a minimum threshold for duration or event count to filter noise (e.g., ignore sessions with ≤1 event or duration <5s).

// Filter very short sessions or those with few events
...
| where event_count > 1 and duration_sec > 5

Step 6: produce aggregated metrics in real time

With the sessions processed, compute aggregated metrics by time window (e.g., per minute) for Real-Time Analytics dashboards.

let sessions = (
  // final session query from the previous step
  Events
  | where Timestamp between (ago(1h) .. now())
  | where isnotempty(sessionId)
  | summarize session_start = min(Timestamp), session_end = max(Timestamp), event_count = count() by sessionId
  | extend duration_sec = todouble(session_end - session_start) / 1s
  | where event_count > 1 and duration_sec > 5
);

sessions
| summarize
    avg_duration = avg(duration_sec),
    sessions_count = count(),
    pct_long = 100.0 * sumif(1, duration_sec > 300) / count()
  by bin(session_start, 1m)
| order by session_start desc

Verify the result

To confirm that everything went well: 1) compare the number of original events with the sum of event_count per sessionId (they should match); 2) inspect example sessions (limit 10) to check start/end; 3) validate the aggregated metrics on a real-time dashboard and look for outliers (very long or very short durations).

Conclusion

You can now aggregate events by sessionId in Real-Time Analytics and generate per-session and time-window metrics. Next steps: enrich with segmentation by userId, deviceType or country and export sessions to a downstream ETL/streaming. Tip: if you notice high latency, reduce the query window and pre-aggregate events at ingestion.