StableNet queries
This section applies to systems integrated with StableNet® only
StableNet® data can be queried from an aggregated data set on a local SKOOR database as well as in real time from the StableNet® API. The following guidelines help to combine and optimize these two methods.
Prepare a materialized data query for devices and measurements
The following example query delivers basic data for use with a FilterMatrix widget (e.g. filter for devices or measurements) as well as to join measurement data from the StableNet® API or the aggregated data table. The data source must be set to one of type StableNet.
-- Data query example "device_measurement" SELECT std.id AS device_id, std.display_name AS device_name, sm.id as measurement_id, sm.name as measurement_name FROM stablenet_taggable_device std JOIN stablenet_measurement sm ON std.id = sm.dest_device_id
Because device and measurement data will not change very often, this query should be materialized. Performance will be increased significantly.
Set the refresh interval to daily and create indices for all attributes:
Join foreign table for realtime data
On top of the prepared materialized data query, data from the StableNet® API can be joined using a foreign table.
The FROM keyword in the following example points to the materialized query prepared above.
SELECT dm.device_id, dm.device_name, dm.measurement_name, dm.measurement_id, sma.metric_key, sma.metric_name, sma.interval, sma.min, sma.max, sma.avg, sma.timestamp FROM device_measurement dm JOIN stablenet_measurement_data sma ON dm.measurement_id = sma.measurement_id WHERE sma.start = ${begin}::timestamptz AND sma."end" = ${end}::timestamptz AND sma.bucket_size = EXTRACT( EPOCH FROM ('1 ' || ${bucket_size})::interval )::integer AND sma.metric_name = ${metric_name}
In the dashboard or the data preview, the following attributes must be set for this example query:
begin/end: ISO 8601 timestamp, e.g. 2024-05-01T00:00:00Z
bucket_size: Time bucket in words like hour, day, month, etc.
metric_name: A valid StableNet® metric name like Round Trip Time
Join aggregated data table
If performance is more important than realtime data, the aggregated data table should be preferred over the foreign tables.
The FROM keyword in the following example points to the materialized query prepared above.
SELECT dm.device_id, dm.device_name, dm.measurement_name, dm.measurement_id, sma.metric_key, sma.metric_name, sma.interval, sma.min, sma.max, sma.avg, sma.timestamp FROM device_measurement dm JOIN stablenet_measurement_data_aggregated sma ON dm.measurement_id = sma.measurement_id WHERE sma.timestamp BETWEEN ${begin}::timestamptz AND ${end}::timestamptz AND sma.metric_name = ${metric_name}
In the dashboard or the data preview, the following attributes must be set for this example query:
begin/end: ISO 8601 timestamp, e.g. 2024-05-01T00:00:00Z
metric_name: A valid StableNet® metric name like Round Trip Time
Foreign Tables
Name | Fields | Qualifiers |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Although measurement_id
and metric_key
are optional, it’s not recommended to execute queries without. Doing so reads measurement data of all measurements and all metrics which might take a long time to read and puts some heavy load on the StableNet server. Only advisable for very short time ranges < 1h. If multiple measurements and metrics are needed, table stablenet_measurement_data_aggregated
should be used.
Aggregated measurement data
Aggregated by hour, automatically updated every hour. Can be used to perform analytics queries over a wide range of measurements and over large time ranges.
Is basically a Timescale hypertable (https://docs.timescale.com/ ). Chunk size is 7 days, chunks are compressed after 7 days.
Name | Fields |
---|---|
|
|
stablenet_measurement_data_aggregated
only contains data for configured metric keys.