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
`stablenet_info`	`server_name TEXT` `name TEXT` `version TEXT` `technical_version TEXT`
`stablenet_taggable`	`server_name TEXT` `domain TEXT` `id BIGINT` `name TEXT` `display_name TEXT` `state TEXT` `attribute_tags JSONB` `custom_tags JSONB` `tag_filter TEXT`	`domain TEXT` (mandatory) `id BIGINT` `tag_filter TEXT`
`stablenet_taggable_device`	`server_name TEXT` `id BIGINT` `name TEXT` `display_name TEXT` `state TEXT` `attribute_tags JSONB` `custom_tags JSONB` `tag_filter TEXT`	`id BIGINT` `tag_filter TEXT`
`stablenet_taggable_interface`	`server_name TEXT` `id BIGINT` `name TEXT` `display_name TEXT` `state TEXT` `attribute_tags JSONB` `custom_tags JSONB` `tag_filter TEXT`	`id BIGINT` `tag_filter TEXT`
`stablenet_taggable_job`	`server_name TEXT` `id BIGINT` `name TEXT` `display_name TEXT` `state TEXT` `attribute_tags JSONB` `custom_tags JSONB` `tag_filter TEXT`	`id BIGINT` `tag_filter TEXT`
`stablenet_taggable_link`	`server_name TEXT` `id BIGINT` `name TEXT` `display_name TEXT` `state TEXT` `attribute_tags JSONB` `custom_tags JSONB` `tag_filter TEXT`	`id BIGINT` `tag_filter TEXT`
`stablenet_taggable_service`	`server_name TEXT` `id BIGINT` `name TEXT` `display_name TEXT` `state TEXT` `attribute_tags JSONB` `custom_tags JSONB` `tag_filter TEXT`	`id BIGINT` `tag_filter TEXT`
`stablenet_taggable_agent`	`server_name TEXT` `id BIGINT` `name TEXT` `display_name TEXT` `state TEXT` `attribute_tags JSONB` `custom_tags JSONB` `tag_filter TEXT`	`id BIGINT` `tag_filter TEXT`
`stablenet_tag_category`	`server_name TEXT` `domain TEXT` `id BIGINT` `name TEXT` `description TEXT`	`domain TEXT` (mandatory) `id BIGINT`
`stablenet_measurement`	`server_name TEXT` `id BIGINT` `name TEXT` `description TEXT` `categories JSONB` `dest_device_id BIGINT` `dest_interface_id BIGINT` `agent_id BIGINT` `start_time TIMESTAMPTZ` `end_time TIMESTAMPTZ` `state TEXT` `type TEXT` `template_name TEXT` `permanent BOOL` `payload JSONB` `active BOOL` `tag_filter TEXT`	`tag_filter TEXT` `dest_device_id BIGINT`
`stablenet_measurement_metric`	`server_name TEXT` `measurement_id BIGINT` `data_id BIGINT` `description TEXT` `expected_interval INT` `key TEXT` `name TEXT` `only_average BOOL` `unit TEXT`	`measurement_id BIGINT`
`stablenet_measurement_data`	`server_name TEXT` `measurement_id BIGINT` `metric_key TEXT` `metric_name TEXT` `"start" TIMESTAMPTZ` `"end" TIMESTAMPTZ` `bucket_size INT` (seconds) `"timestamp" TIMESTAMPTZ` `min double precision` `max double precision` `avg double precision` `tag_filter TEXT`	`measurement_id BIGINT` `metric_key TEXT` `"start" TIMESTAMPTZ` (mandatory) `"end" TIMESTAMPTZ` (mandatory) `bucket_size INT` (mandatory) `tag_filter TEXT`

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`	`server_name TEXT` `measurement_id BIGINT` `metric_key TEXT` `metric_name TEXT` `"timestamp" TIMESTAMPTZ NOT NULL` `min double precision` `max double precision` `avg double precision`

stablenet_measurement_data_aggregated only contains data for configured metric keys.