Setting the file. One moment.
Skill 102 · Querying PostHog Data
Subchapter 103.8
references/example-llm-traces-list.mdMarkdown4 KBView on GitHub
List multiple LLM traces with aggregated latency, token usage, costs, and error counts. This is a two-phase query for performance: first find matching trace IDs, then fetch full trace data. Time ranges are always required. Results can be large — dump to a file if needed.
This query intentionally omits large content fields (, , , ). Use the to retrieve those for a specific trace. This content lives only on (not ), retained 30 days by default — read it anchored on . See for the column mapping and access patterns.
$ai_input$ai_output_choices$ai_input_state$ai_output_stateposthog.ai_eventseventstrace_idUse this subquery to find trace IDs matching your criteria. Add property filters here for efficiency.
SELECT
properties.$ai_trace_id AS trace_id,
min(timestamp) AS first_ts,
max(timestamp) AS last_ts
FROM events
WHERE
event IN ('$ai_span', '$ai_generation', '$ai_embedding', '$ai_metric', '$ai_feedback', '$ai_trace')
AND isNotNull(properties.$ai_trace_id)
AND properties.$ai_trace_id != ''
AND timestamp >= now() - INTERVAL 1 HOUR
AND timestamp <= now()
-- Add property filters here, e.g.:
-- AND properties.$ai_model = 'gpt-4o'
-- AND properties.$ai_is_error = 'true'
GROUP BY trace_id
ORDER BY min(timestamp) DESC
LIMIT 20Use the trace IDs from phase 1 to fetch aggregated metrics. Replace the IN (...) clause with the IDs found above.
SELECT
properties.$ai_trace_id AS id,
any(properties.$ai_session_id) AS ai_session_id,
min(timestamp) AS first_timestamp,
ifNull(
nullIf(argMinIf(distinct_id, timestamp, event = '$ai_trace'), ''),
argMin(distinct_id, timestamp)
) AS first_distinct_id,
round(
coalesce(
-- The root $ai_trace event reports the wall-clock latency of the whole trace,
-- so the events it contains are already inside that number. Adding them again
-- counts the same time twice.
nullIf(maxIf(toFloat(properties.$ai_latency),
event = '$ai_trace' AND toFloat(properties.$ai_latency) > 0), 0),
CASE
WHEN countIf(toFloat(properties.$ai_latency) > 0 AND event != '$ai_generation') = 0
AND countIf(toFloat(properties.$ai_latency) > 0 AND event = '$ai_generation') > 0
THEN sumIf(toFloat(properties.$ai_latency),
event = '$ai_generation' AND toFloat(properties.$ai_latency) > 0)
ELSE sumIf(toFloat(properties.$ai_latency),
properties.$ai_parent_id IS NULL
OR toString(properties.$ai_parent_id) = toString(properties.$ai_trace_id))
END
), 2
) AS total_latency,
sumIf(toFloat(properties.$ai_input_tokens),
event IN ('$ai_generation', '$ai_embedding')) AS input_tokens,
sumIf(toFloat(properties.$ai_output_tokens),
event IN ('$ai_generation', '$ai_embedding')) AS output_tokens,
round(sumIf(toFloat(properties.$ai_input_cost_usd),
event IN ('$ai_generation', '$ai_embedding')), 10) AS input_cost,
round(sumIf(toFloat(properties.$ai_output_cost_usd),
event IN ('$ai_generation', '$ai_embedding')), 10) AS output_cost,
round(sumIf(toFloat(properties.$ai_total_cost_usd),
event IN ('$ai_generation', '$ai_embedding')), 10) AS total_cost,
ifNull(
argMinIf(
ifNull(properties.$ai_span_name, properties.$ai_trace_name),
timestamp, event = '$ai_trace'
),
argMin(
ifNull(properties.$ai_span_name, properties.$ai_trace_name),
timestamp
)
) AS trace_name,
countIf(
isNotNull(properties.$ai_error) OR properties.$ai_is_error = 'true'
) AS error_count
FROM events
WHERE
event IN ('$ai_span', '$ai_generation', '$ai_embedding', '$ai_metric', '$ai_feedback', '$ai_trace')
AND timestamp >= now() - INTERVAL 1 HOUR
AND timestamp <= now()
AND properties.$ai_trace_id IN ('trace-id-1', 'trace-id-2')
GROUP BY properties.$ai_trace_id
ORDER BY first_timestamp DESC