DocumentationStableNet 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
Instead of the metric_name, try the metric_key attribute in the WHERE clause to select a specific metric if more than one is available
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
Instead of the metric_name, try the metric_key attribute in the WHERE clause to select a specific metric if more than one is available
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.