Dashboard
Open up Grafana at http://localhost/d/host - note the header has a time range of 3 hours, and a refresh rate of 5 seconds.
Queries
Now let’s look at a query. Click on the CPU header and select ‘Edit’, then look in the ‘Data Source’ box -
SELECT time, value as total
FROM history_float
WHERE
path='$device/CpuTotal'
and $__timeFilter("time")
ORDER BY time
This is a SQL query, fetching data from the local Postgres database, which is running in Docker.
$device
is a variable, which can be specified in the Grafana page settings. In this case, it’s equivalent to ‘Main/Host’, so the complete path is ‘Main/Host/cpu-total’.
$__timeFilter("time")
is a Grafana maco, which expands to something like “time” BETWEEN ‘2022-08-22T12:03:25.593Z’ AND ‘2022-08-22T15:03:25.593Z’ in the final query.
This query will be run every 5 seconds, and the graph updated.
Click ‘Discard’ to exit the query editor.
Functions
Now let’s look at another query, for the timeline on the main page - click on ‘Main’ in the header.
Click on ‘Timeline’, and ‘Edit’. This has a series of queries, starting with
SELECT time, value as "Availability"
FROM get_timeline('$device', '$device/Availability', $__from, $__to, true, '1d')
In this case, we’re calling a function defined in the database that can be used for timeline graphs. It’s basically a wrapper around this -
SELECT history_all.time, history_all.value->>0 AS value -- note: ->>0 extracts the top-level jsonb value
FROM history_all
WHERE
device = devicename and
path = pathname and
history_all.time >= from_time and
history_all.time <= to_time
We’re selecting time and value from the history_all view for a given device, path, and time range.
DataItems
You can see the available dataitems in the ‘DataItems’ page.