TMA1 Observability Query

SELECT session_id,
       MIN(ts) AS started,
       MAX(ts) AS last_event,
       SUM(CASE WHEN event_type = 'PreToolUse' THEN 1 ELSE 0 END) AS tool_calls,
       SUM(CASE WHEN event_type = 'PostToolUseFailure' THEN 1 ELSE 0 END) AS tool_failures
FROM tma1_hook_events
WHERE agent_source = 'copilot_cli'
  AND ts > NOW() - INTERVAL '1 day'
GROUP BY session_id
ORDER BY last_event DESC
LIMIT 20

SELECT model,
       SUM(COALESCE(output_tokens, 0)) AS output_tokens,
       COUNT(*) AS messages
FROM tma1_messages
WHERE session_id LIKE 'cp:%'
  AND model != ''
  AND ts > NOW() - INTERVAL '1 day'
GROUP BY model
ORDER BY output_tokens DESC

SELECT tool_name,
       COUNT(*) AS calls
FROM tma1_hook_events
WHERE agent_source = 'copilot_cli'
  AND event_type = 'PreToolUse'
  AND tool_name != ''
  AND ts > NOW() - INTERVAL '1 day'
GROUP BY tool_name
ORDER BY calls DESC
LIMIT 15

SELECT ts, agent_type, metadata
FROM tma1_hook_events
WHERE agent_source = 'copilot_cli'
  AND event_type = 'SubagentStop'
ORDER BY ts DESC
LIMIT 20

SELECT ts, message_type, "role", model, content
FROM tma1_messages
WHERE session_id = 'cp:<sessionId>'
ORDER BY ts ASC

SELECT timestamp, trace_id,
  CASE
    WHEN json_get_string(parse_json(body), 'message.role') IS NOT NULL
      THEN json_get_string(parse_json(body), 'message.role')
    WHEN json_get_string(parse_json(body), 'id') IS NOT NULL THEN 'tool'
    WHEN json_get_string(parse_json(body), 'content') IS NOT NULL THEN 'user'
    ELSE 'unknown'
  END AS role,
  COALESCE(
    json_get_string(parse_json(body), 'message.content'),
    json_get_string(parse_json(body), 'content')
  ) AS content
FROM opentelemetry_logs
WHERE matches_term(body, 'your_keyword')
ORDER BY timestamp DESC LIMIT 50

SELECT timestamp,
  CASE
    WHEN json_get_string(parse_json(body), 'message.role') IS NOT NULL
      THEN json_get_string(parse_json(body), 'message.role')
    WHEN json_get_string(parse_json(body), 'id') IS NOT NULL THEN 'tool'
    WHEN json_get_string(parse_json(body), 'content') IS NOT NULL THEN 'user'
    ELSE 'unknown'
  END AS role,
  COALESCE(
    json_get_string(parse_json(body), 'message.content'),
    json_get_string(parse_json(body), 'content')
  ) AS content
FROM opentelemetry_logs
WHERE trace_id = '<trace_id>'
ORDER BY timestamp LIMIT 100

SELECT timestamp, trace_id,
  json_get_string(parse_json(body), 'content') AS content
FROM opentelemetry_logs
WHERE trace_id != ''
  AND json_get_string(parse_json(body), 'content') IS NOT NULL
  AND json_get_string(parse_json(body), 'message.role') IS NULL
  AND json_get_string(parse_json(body), 'id') IS NULL
  AND (
    body LIKE '%ignore%previous%instructions%'
    OR body LIKE '%ignore%above%'
    OR body LIKE '%disregard%previous%'
    OR body LIKE '%reveal%system%prompt%'
    OR body LIKE '%jailbreak%'
    OR body LIKE '%DAN%mode%'
    OR body LIKE '%bypass%safety%'
    OR body LIKE '%[system]%'
  )
  AND timestamp > NOW() - INTERVAL '24 hours'
ORDER BY timestamp DESC LIMIT 50

SELECT span_name,
       "span_attributes.gen_ai.request.model" AS model,
       "span_attributes.gen_ai.usage.input_tokens" AS input_tok,
       "span_attributes.gen_ai.usage.output_tokens" AS output_tok,
       duration_nano / 1000000 AS duration_ms,
       timestamp
FROM opentelemetry_traces
WHERE "span_attributes.gen_ai.system" IS NOT NULL
ORDER BY timestamp DESC
LIMIT 20

-- Joins against tma1_model_pricing (seeded by tma1-server on first start).
-- To see/edit pricing: SELECT * FROM tma1_model_pricing ORDER BY priority;
SELECT t.model,
       ROUND(SUM(
         CAST(t.input_tok AS DOUBLE) * p.input_price / 1e6 +
         CAST(t.output_tok AS DOUBLE) * p.output_price / 1e6
       ), 4) AS cost_usd
FROM (
  SELECT "span_attributes.gen_ai.request.model" AS model,
         "span_attributes.gen_ai.usage.input_tokens" AS input_tok,
         "span_attributes.gen_ai.usage.output_tokens" AS output_tok
  FROM opentelemetry_traces
  WHERE "span_attributes.gen_ai.system" IS NOT NULL
    AND timestamp >= DATE_TRUNC('day', NOW())
) t
JOIN tma1_model_pricing p
  ON t.model LIKE CONCAT('%', p.model_pattern, '%')
GROUP BY t.model
ORDER BY cost_usd DESC

SELECT "span_attributes.gen_ai.request.model" AS model,
       SUM(CAST("span_attributes.gen_ai.usage.input_tokens" AS DOUBLE)) AS input_tok,
       SUM(CAST("span_attributes.gen_ai.usage.output_tokens" AS DOUBLE)) AS output_tok,
       COUNT(*) AS requests
FROM opentelemetry_traces
WHERE "span_attributes.gen_ai.system" IS NOT NULL
  AND timestamp >= DATE_TRUNC('day', NOW())
GROUP BY model
ORDER BY input_tok DESC

SELECT "span_attributes.gen_ai.request.model" AS model,
       COUNT(*) AS requests,
       SUM(CASE WHEN span_status_code = 'STATUS_CODE_ERROR' THEN 1 ELSE 0 END) AS errors
FROM opentelemetry_traces
WHERE "span_attributes.gen_ai.system" IS NOT NULL
  AND timestamp > NOW() - INTERVAL '24 hours'
GROUP BY model
ORDER BY errors DESC

SELECT "span_attributes.gen_ai.request.model" AS model,
       ROUND(APPROX_PERCENTILE_CONT(duration_nano, 0.50) / 1000000.0, 0) AS p50_ms,
       ROUND(APPROX_PERCENTILE_CONT(duration_nano, 0.95) / 1000000.0, 0) AS p95_ms,
       COUNT(*) AS requests
FROM opentelemetry_traces
WHERE "span_attributes.gen_ai.system" IS NOT NULL
  AND timestamp > NOW() - INTERVAL '24 hours'
GROUP BY model
ORDER BY p95_ms DESC

-- List recent sessions with tool counts
SELECT session_id, conversation_id, agent_source, MIN(ts) AS start_ts, MAX(ts) AS end_ts,
  SUM(CASE WHEN event_type = 'PreToolUse' THEN 1 ELSE 0 END) AS tool_calls,
  SUM(CASE WHEN event_type = 'SubagentStart' THEN 1 ELSE 0 END) AS subagents
FROM tma1_hook_events WHERE ts > NOW() - INTERVAL '24 hours'
GROUP BY session_id, conversation_id, agent_source ORDER BY MIN(ts) DESC

-- Search conversation content
SELECT session_id, conversation_id, ts, message_type, content FROM tma1_messages
WHERE matches_term(content, 'search keyword')
  AND ts > NOW() - INTERVAL '7 days'
ORDER BY ts DESC LIMIT 20

-- Session token usage (from JSONL transcripts)
SELECT session_id, SUM(input_tokens) AS input_tok, SUM(output_tokens) AS output_tok,
  SUM(cache_read_tokens) AS cache_read, SUM(cache_creation_tokens) AS cache_write
FROM tma1_messages WHERE message_type = 'assistant' AND ts > NOW() - INTERVAL '24 hours'
GROUP BY session_id ORDER BY input_tok DESC

-- Session tool breakdown
SELECT tool_name, COUNT(*) AS calls FROM tma1_hook_events
WHERE session_id = '<session_id>' AND event_type = 'PreToolUse'
GROUP BY tool_name ORDER BY calls DESC