Skip to main content

Grafana Visualization

Telemetry Harbor integrates with Grafana to provide powerful data visualization capabilities for your telemetry data.

Database Schema Overview​

Telemetry Harbor uses TimescaleDB (PostgreSQL extension) to store time-series data. The main table structure is:

cargo_data Table​

CREATE TABLE cargo_data (
    time TIMESTAMPTZ NOT NULL,
    ship_id TEXT NOT NULL,
    cargo_id TEXT NOT NULL,
    value DOUBLE PRECISION NOT NULL,
);

Column Descriptions:

  • time: Timestamp of the ship reading (ISO 8601 format)
  • ship_id: Unique identifier for the device
  • cargo_id: Unique identifier for the sensor or event
  • value: The recorded cargo value

Setting Up Your First Graph​

  1. Log In: Access Grafana using your Telemetry Harbor credentials (Email/Provided Password)
  2. Change the default password for extra security
  3. Data Source Setup: Verify that the TimescaleDB data source is properly connected
  4. Create a New Dashboard:
    • Navigate to "Dashboards" > "New Dashboard"
    • Click "Add new panel"
    • In the query editor, select the TimescaleDB data source

Setting Up Grafana Variables​

Before writing queries, set up variables for dynamic dashboards:

1. Ship ID Variable​

  • Name: ship_id
  • Type: Query
  • Query:
SELECT DISTINCT ship_id FROM cargo_data ORDER BY ship_id
  • Multi-value: Enable
  • Include All option: Enable

2. Cargo ID Variable​

  • Name: cargo_id
  • Type: Query
  • Query:
SELECT DISTINCT cargo_id FROM cargo_data ORDER BY cargo_id
  • Multi-value: Enable
  • Include All option: Enable

3. Interval Handling (for TimescaleDB)​

Grafana's $__interval or custom interval variables return values like 1d or 5m, which are not valid in TimescaleDB. Timescale requires intervals in the format interval '1 day'.

To solve this, we calculate the bucket size in SQL based on the selected time range:

WITH time_range AS (
  SELECT $__timeFrom() AS start_time, $__timeTo() AS end_time
),
bucket_choice AS (
  SELECT CASE
    WHEN end_time - start_time <= interval '1 hour' THEN interval '10 seconds'
    WHEN end_time - start_time <= interval '6 hours' THEN interval '1 minute'
    WHEN end_time - start_time <= interval '1 day' THEN interval '5 minutes'
    WHEN end_time - start_time <= interval '7 days' THEN interval '30 minutes'
    WHEN end_time - start_time <= interval '30 days' THEN interval '1 hour'
    WHEN end_time - start_time <= interval '90 days' THEN interval '6 hours'
    ELSE interval '1 day'
  END AS bucket
  FROM time_range
)

4. Time Range Variable​

Grafana automatically provides $__timeFilter() function for time filtering.

Sample Queries​

Basic Time Series Query​

Display sensor readings for specific ships and cargo types:

SELECT 
    time,
    value,
    ship_id,
    cargo_id
FROM cargo_data
WHERE $__timeFilter(time)
    AND cargo_id IN ($cargo_id:sqlstring)
    AND ship_id IN ($ship_id:sqlstring)
ORDER BY time ASC;

Aggregated Data with Dynamic Time Buckets​

Group data by time intervals using the interval variable:

SELECT 
    time_bucket('$interval', time) AS time,
    ship_id,
    cargo_id,
    AVG(value) as avg_value,
    MAX(value) as max_value,
    MIN(value) as min_value,
    COUNT(*) as data_points
FROM cargo_data
WHERE $__timeFilter(time)
    AND cargo_id IN ($cargo_id:sqlstring)
    AND ship_id IN ($ship_id:sqlstring)
GROUP BY time_bucket('$interval', time), ship_id, cargo_id
ORDER BY time ASC;

GPS Tracking Query​

For GPS data (latitude/longitude pairs):

SELECT 
    time_bucket('$interval', time) AS time,
    ship_id,
    MAX(CASE WHEN cargo_id = 'latitude' THEN value END) as latitude,
    MAX(CASE WHEN cargo_id = 'longitude' THEN value END) as longitude
FROM cargo_data
WHERE $__timeFilter(time)
    AND ship_id IN ($ship_id:sqlstring)
    AND cargo_id IN ('latitude', 'longitude')
GROUP BY time_bucket('$interval', time), ship_id
ORDER BY time ASC;

Latest Values Query​

Get the most recent reading for each sensor:

SELECT DISTINCT ON (ship_id, cargo_id)
    time,
    ship_id,
    cargo_id,
    value
FROM cargo_data
WHERE $__timeFilter(time)
    AND cargo_id IN ($cargo_id:sqlstring)
    AND ship_id IN ($ship_id:sqlstring)
ORDER BY ship_id, cargo_id, time DESC;

Rate of Change Query​

Calculate the rate of change using the interval variable:

SELECT 
    time_bucket('$interval', time) AS time,
    ship_id,
    cargo_id,
    AVG(value) as current_avg,
    LAG(AVG(value)) OVER (PARTITION BY ship_id, cargo_id ORDER BY time_bucket('$interval', time)) as previous_avg,
    AVG(value) - LAG(AVG(value)) OVER (PARTITION BY ship_id, cargo_id ORDER BY time_bucket('$interval', time)) as change
FROM cargo_data
WHERE $__timeFilter(time)
    AND cargo_id IN ($cargo_id:sqlstring)
    AND ship_id IN ($ship_id:sqlstring)
GROUP BY time_bucket('$interval', time), ship_id, cargo_id
ORDER BY time ASC;

Visualization Types​

Time Series Panel​

Best for showing sensor trends over time:

  • Use the aggregated query with interval variable
  • Configure legend to show {{ship_id}} - {{cargo_id}}
  • Set appropriate Y-axis units

Stat Panel​

Perfect for showing current values:

  • Use the latest values query
  • Configure value mappings for status indicators
  • Set thresholds for color coding

Geomap Panel​

For GPS tracking visualization:

  • Use the GPS tracking query
  • Configure latitude/longitude fields
  • Add trajectory lines for ship movement

Table Panel​

Useful for detailed data inspection:

  • Use any query with multiple columns
  • Configure column sorting and filtering
  • Add conditional formatting

Advanced Features​

Alerting​

Set up alerts based on sensor thresholds:

SELECT 
    time,
    ship_id,
    cargo_id,
    value
FROM cargo_data
WHERE $__timeFilter(time)
    AND cargo_id = 'temperature'
    AND value > 80  -- Alert threshold
ORDER BY time DESC
LIMIT 1;

Annotations​

Add events to your graphs:

SELECT 
    time as time,
    'Alert: High Temperature' as text,
    ship_id as tags
FROM cargo_data
WHERE $__timeFilter(time)
    AND cargo_id = 'temperature'
    AND value > 80;

Moving Averages​

Calculate moving averages using window functions:

SELECT 
    time,
    ship_id,
    cargo_id,
    value,
    AVG(value) OVER (
        PARTITION BY ship_id, cargo_id 
        ORDER BY time 
        ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
    ) as moving_avg_10
FROM cargo_data
WHERE $__timeFilter(time)
    AND cargo_id IN ($cargo_id:sqlstring)
    AND ship_id IN ($ship_id:sqlstring)
ORDER BY time ASC;

By following these guidelines and using the provided queries with the interval variable, you can create comprehensive, performant dashboards that provide valuable insights into your maritime telemetry data.