---
title: Cookbook
description: Practical ROSQL query recipes for common robotics observability tasks
sidebar_position: 5
keywords: [rosql, examples, queries, cookbook, ros2, robotics, telemetry]
---

# Cookbook

Practical query recipes for common ROS2 observability tasks. Copy, adapt, and run against your telemetry data.

## Basic queries

### Find all error spans

```sql
FROM traces WHERE status = 'ERROR'
```

### Error logs at a specific severity

```sql
FROM logs WHERE severity = 'ERROR'
```

### Slowest actions (last hour)

```sql
SELECT span_name, duration
FROM traces
WHERE action_name = '/navigate_to_pose' AND duration > 500 ms
SINCE 1 hour ago
ORDER BY duration DESC
LIMIT 20
```

### Filter by ROS2 node

```sql
FROM traces WHERE node = '/bt_navigator'
```

### Robot-scoped query

```sql
FOR ROBOT 'robot_sim_001' FROM logs WHERE severity = 'ERROR'
```

### Scope by version

```sql
FOR VERSION 'v1.2.3' FROM traces WHERE status = 'ERROR'
```

### Scope by environment

```sql
FOR ENVIRONMENT 'production' FROM traces WHERE status = 'ERROR' SINCE 1 hour ago
```

### Scope by session

```sql
FOR SESSION 'sess_abc123' FROM logs WHERE severity = 'ERROR'
```

### Compose multiple FOR clauses

```sql
FOR ROBOT 'robot_42' FOR VERSION 'v1.0.0' FOR ENVIRONMENT 'prod' FROM traces WHERE status = 'ERROR'
```

### Read topic messages

```sql
FROM topics WHERE topic_name = '/battery_state' AND fields['percentage'] < 20
```

### Topic alias

```sql
-- Shorthand for: FROM topics WHERE topic_name = '/odom'
FROM odom LIMIT 5
```

---

## Cross-signal correlation

### Navigation failures during low battery

```sql
SELECT trace_id, span_name_col, service_name, duration, status_code, span_attributes
FROM traces
WHERE status = 'ERROR' AND action_name = '/navigate_to_pose'
DURING(
  FROM topics WHERE topic_name = '/battery_state'
  AND fields['percentage'] < 15
)
SINCE 6 hours ago
```

### Errors during high CPU load

```sql
FROM traces WHERE status = 'ERROR'
DURING(
  FROM metrics WHERE metric_name = 'system.cpu.total_usage_pct'
  AND metric_value > 90
)
SINCE yesterday
```

---

## Causality and message tracing

### Trace full message causality chain

```sql
TRACE 'a3f1c9d2e8b04f7a'
```

Returns all spans in the causal chain by walking `parent_span_id → span_id` recursively via a CTE.

:::note Removed in v0.4.1
`MESSAGE JOURNEY FOR TRACE 'id'` has been removed. Use `TRACE 'id'` instead.
:::

### Find all message paths for a topic

```sql
MESSAGE FLOW FROM TOPIC '/cmd_vel'
```

Shows which nodes published and subscribed to `/cmd_vel`.

### Filter message flow to a specific destination node

```sql
MESSAGE FLOW FROM TOPIC '/cmd_vel' TO NODE '/motor_driver'
```

### Filter message flow to a specific destination topic

```sql
MESSAGE FLOW FROM TOPIC '/sensor_data' TO TOPIC '/processed_data'
```

:::note Removed in v0.4.1
`MESSAGE PATHS FOR TOPIC '/topic'` and `MESSAGE PATH FROM TOPIC '/src' TO NODE '/dst'` have been removed. Use `MESSAGE FLOW FROM TOPIC ...` instead.
:::

### All spans for a specific trace

```sql
TRACE 'trace-002'
```

---

## Composable Health Dashboard

Five focused queries that together cover what `HEALTH()` will do when implemented. Run them individually or wire them into a dashboard.

### Error rate by robot

```sql
SELECT COUNT(*) FROM traces WHERE status = 'ERROR' FACET robot_id SINCE 30 minutes ago
```

### Log severity breakdown

```sql
SELECT COUNT(*) FROM logs WHERE severity IN ('ERROR', 'WARN') FACET severity SINCE 30 minutes ago
```

### Action success rates

```sql
SELECT ACTION_SUCCESS_RATE('/navigate_to_pose') FROM traces SINCE 30 minutes ago
```

### Topic publish rates

```sql
SELECT TOPIC_RATE('/cmd_vel') FROM metrics SINCE 30 minutes ago
```

### P95 action latency

```sql
SELECT APPROX_PERCENTILE(duration, 95) FROM traces WHERE action_name = '/navigate_to_pose' SINCE 30 minutes ago
```

---

## Robot health and anomaly detection

### Robot health assessment

:::caution Not implemented in v0.4
`HEALTH()` is not yet compiled. Use the [Composable Health Dashboard](#composable-health-dashboard) recipes above.
:::

```sql
HEALTH() FOR ROBOT 'robot_sim_001'
```

Fan-out across traces, logs, and metrics to produce an overall health assessment.

### Detect statistical anomalies in span duration _(v0.4.3+)_

Uses a two-phase z-score CTE to compare current values against a historical or fleet-wide baseline.

```sql
-- Anomalous robots compared to last week
ANOMALY(duration) COMPARED TO last week FACET robot_id SINCE 7 days ago

-- Anomalies compared to fleet-wide baseline
ANOMALY(duration) COMPARED TO fleet SINCE 1 hour ago FACET robot_id
```

Returns `robot_id`, `avg_current`, `avg_baseline`, `z_score`, `is_anomalous`, `direction`.

### Finding anomalous robots after deployment _(v0.4.3+)_

Compare post-deployment behaviour against the previous week's baseline to catch regressions:

```sql
ANOMALY(duration) FROM traces COMPARED TO last week FACET robot_id SINCE 24 hours ago
```

Pair with `FOR VERSION` to scope to a specific release:

```sql
FOR VERSION 'v1.3.0' ANOMALY(duration) COMPARED TO last week FACET robot_id SINCE 12 hours ago
```

### Path deviation analysis _(v0.4.3+)_

Check if a robot deviated from its planned path during a specific trace:

```sql
PATH DEVIATION FOR TRACE 'trace-002'
```

Or over a time window for a robot:

```sql
PATH DEVIATION FOR ROBOT 'robot_sim_001' SINCE 1 hour ago
```

Select a specific plan (0 = first occurrence, -1 = latest, default = -1):

```sql
PATH DEVIATION PLAN 0 FOR ROBOT 'robot_sim_001' SINCE 6 hours ago
```

Returns lateral deviation in metres per waypoint plus summary stats.

### Joint trajectory tracking _(v0.4.3+)_

Compare planned vs. actual joint positions to detect mechanical issues or controller errors:

```sql
JOINT DEVIATION FOR TRACE 'trace-002'
```

Or across a time window:

```sql
JOINT DEVIATION FOR ROBOT 'arm_01' SINCE 1 hour ago
```

Returns per-joint `position_error_rad` and summary statistics.

### Browse robot joint map _(v0.4.3+)_

Inspect the URDF-derived joint map registered for a robot model:

```sql
SHOW JOINTS FOR ROBOT 'robot_sim_001'
```

Returns `joint_name`, `joint_index`, `joint_type`, `lower_limit`, `upper_limit`.

---

## MCAP recordings

### Find the recording that covers a time window

:::caution Not implemented in v0.4
`SHOW RECORDING` is not yet compiled. Use `FROM recordings LIMIT 5` to browse recording metadata directly.
:::

```sql
SHOW RECORDING
```

Returns MCAP recording metadata (`s3_key`, time range, topics) that covers the most recent event window.

---

## Pipeline syntax

Pipeline syntax uses `|` to chain stages, making complex queries more readable:

### Find slow error spans, grouped by robot

```sql
FROM traces
| WHERE duration > 500 ms
| WHERE status = 'ERROR'
| FACET robot_id
```

### Navigation failures vs. last week

```sql
FROM traces
| WHERE action_name = '/navigate_to_pose'
| WHERE status = 'ERROR'
| FACET robot_id
| COMPARE TO last week
```

### Compare error rate to a previous version

```sql
FROM traces WHERE status = 'ERROR' SINCE 1 hour ago | COMPARE TO VERSION 'v1.0.0'
```

### Compare two versions head-to-head

```sql
FROM traces WHERE action_name = '/navigate_to_pose' COMPARE VERSION 'v1.0' TO VERSION 'v2.0'
```

### Deployment history for a robot

```sql
SHOW DEPLOYMENTS FOR ROBOT 'robot_42' SINCE 7 days ago
```

### Top spans by average duration

```sql
SHOW SPAN SUMMARY FOR ROBOT 'robot_42' SINCE 1 hour ago
```

### Plan spans for a specific trace

```sql
SHOW PLANS FOR TRACE 'a3f1c9d2e8b04f7a'
```

### Log analysis with ordering

```sql
FROM logs
| WHERE severity IN ('ERROR', 'WARN')
| ORDER BY severity DESC
| LIMIT 20
```

---

## Time-series and aggregations

### Average span duration per robot

```sql
SELECT AVG(duration) FROM traces FACET robot_id
```

### Error count by severity

```sql
SELECT COUNT(*) FROM logs WHERE severity = 'ERROR' FACET robot_id
```

### Percentile latency for navigation

```sql
SELECT PERCENTILE(duration, 95) FROM traces WHERE action_name = '/navigate_to_pose'
```

### Multiple aggregations

```sql
SELECT AVG(duration) AS avg_dur, MAX(duration) AS max_dur, COUNT(*) AS total FROM traces
```

### Rolling average (MOVING_AVG)

Smooth out spikes in action duration over a 5-row window:

```sql
SELECT MOVING_AVG(duration, 5) FROM traces WHERE action_name = '/navigate_to_pose'
```

### Rate of change (DERIVATIVE)

Detect rising metric values — useful for spotting resource exhaustion trends:

```sql
SELECT DERIVATIVE(metric_value) FROM metrics WHERE metric_name = 'system.cpu.total_usage_pct'
```

### Approximate distinct count

Count unique action names without a full distinct scan (DuckDB uses HLL; PostgreSQL falls back to exact):

```sql
SELECT APPROX_COUNT_DISTINCT(span_name) FROM traces SINCE 1 hour ago
```

---

## Geospatial filtering _(v0.4.3+)_

### Filter by GPS proximity (Haversine)

Find odom messages where the robot was within 500 m of a GPS coordinate:

```sql
FROM odom WHERE position WITHIN 500 m OF (37.7749, -122.4194) SINCE 1 hour ago
```

Uses inline Haversine SQL (great-circle distance) extracting `position.latitude` / `position.longitude` from the `fields` JSONB column.

### Filter by local frame proximity (Euclidean)

Find odom messages within 2 m of a local coordinate using Euclidean distance:

```sql
FROM odom WHERE position WITHIN 2 m OF POSITION (1.5, 3.0) SINCE 1 hour ago
```

Extracts `pose.pose.position.x` / `pose.pose.position.y` from `fields`.

### Monitoring joint positions during failures

Use array-indexed field access to read individual joint positions from `/joint_states` messages:

```sql
FROM joint_states
WHERE fields['position[0]'] > 1.5
FOR ROBOT 'arm_01'
SINCE 1 hour ago
```

`fields['position[0]']` compiles to `"fields"->'position'->>0` on PostgreSQL/DuckDB.

---

## Pagination

### Page through results with LIMIT and OFFSET

```sql
FROM logs ORDER BY timestamp DESC LIMIT 20 OFFSET 0
FROM logs ORDER BY timestamp DESC LIMIT 20 OFFSET 20
FROM logs ORDER BY timestamp DESC LIMIT 20 OFFSET 40
```

Pipeline form:

```sql
FROM logs | ORDER BY timestamp DESC | LIMIT 20 | OFFSET 40
```

---

## See also

- [Command Reference →](./command-reference) — full syntax for every ROSQL statement
- [Schema Reference →](./schema-reference) — table structure ROSQL expects
- [Examples page →](/examples) — killer demos with detailed explanations
