StableNet® Foreign Data Wrapper

Prerequisites

A PostgreSQL database with a user that is allowed to modify database objects is required. The foreign data wrapper has to be installed on the same server instance the database server is running.

Example: Create database infosim owned by role infosim:

export DB_NAME=infosim
mkdir -p /var/lib/pgsql/custom/user_tblspc/${DB_NAME}_tblspc/tab_space/${DB_NAME} \
 /var/lib/pgsql/custom/user_tblspc/${DB_NAME}_tblspc/idx_space/${DB_NAME} && \
 chown -R postgres:postgres /var/lib/pgsql/custom/user_tblspc
psql -Upostgres -c "CREATE ROLE $DB_NAME WITH LOGIN PASSWORD '$DB_NAME';"
psql -Upostgres -c "CREATE TABLESPACE ${DB_NAME}_tab OWNER $DB_NAME \
LOCATION '/var/lib/pgsql/custom/user_tblspc/${DB_NAME}_tblspc/tab_space/$DB_NAME';"
psql -Upostgres -c "CREATE TABLESPACE ${DB_NAME}_idx OWNER $DB_NAME \
LOCATION '/var/lib/pgsql/custom/user_tblspc/${DB_NAME}_tblspc/idx_space/$DB_NAME';"
psql -Upostgres -c "CREATE DATABASE $DB_NAME WITH OWNER $DB_NAME TEMPLATE template0 \
TABLESPACE ${DB_NAME}_tab ENCODING 'UTF8' LC_COLLATE 'de_CH.UTF-8' LC_CTYPE 'de_CH.UTF-8';"

Standard Installation

Install RPM from SKOOR Repo

dnf install eranger-stablenet-fdw

Configuration

Config file

/etc/stablenet-fdw/stablenet-fdw.conf

[global]

#accept_insecure_certificate = false
                                        # Accept insecure certificates
                                        # (self-signed, expired, etc.)
                                        # when connecting to the web service
default_user =
                                        # Default user name for the web service.
                                        # This is used in case no authorization
                                        # is provided by the SQL client.
default_password =
                                        # Default password for the web service.
                                        # This is used in case no authorization
                                        # is provided by the SQL client.
#skoor_auth_url = http://localhost:8094
                                        # URL of the SKOOR web service
                                        # (default: http://localhost:8094)
                                        # This is used to get the StableNet®
                                        # auth tokens.

#oracle_client_lib_dir =
                                        # Path to the Oracle client library directory.
                                        # This is used to load the Oracle client library
                                        # for the Oracle database connection.
                                        # If omitted, the thin client is used which might have
                                        # problems connectiong to older Oracle databases.

#history_days = 365
                                        # Number of days the history data is loaded
                                        # from the StableNet® database.
                                        # Defaults to 365 days.

[database]

#host = localhost
                                        # Hostname of the PostgreSQL database
#port = 5432
                                        # Port of the PostgreSQL database
#database = infosim
                                        # Name of the PostgreSQL database
#user = infosim
                                        # User name of the PostgreSQL database
#password = infosim
                                        # Password of the PostgreSQL database
#schema = stablenet
                                        # Schema of the PostgreSQL database
                                        # (default: stablenet)
#measurement_data_table = stablenet_measurement_data
                                        # Name of the measurement data table
                                        # (default: stablenet_measurement_data)

[retention_days]

#raw = 1
#minutely = 1
#five_minutely = 14
#fifteen_minutely = 31 # ~1 month
#hourly = 93 # ~3 months
#daily = 365 # ~1 years
#weekly = 365 # ~1 years
#monthly = 1825 # ~5 years
#quarterly = 1825 # ~5 years
#yearly = 3650 # ~10 years

[webservice]                            # use te form werbservice_<server_id>
                                        # to configure multiple StableNet® servers
                                        # (see below).

url =
                                        # URL of StableNet® the web service
database_connection =
                                        # Connection string to the StableNet® database.
                                        # This is used for the measurement data sync.
                                        # The connection string must be in the format:
                                        # MySQL: mysql+pymysql://<user>:<password>@<host>:3306/<database>
                                        # Oracle: oracle+oracledb://<user>:<password>@<host>:1521/?service_name=<service>
                                        # Connection info can be found on the StableNet server in
                                        # /opt/stablenet/snmw/wildfly/standalone/configuration/standalone.xml (<datasources>).
                                        # It's usually a good idea to use a different user and service than the one used by StableNet.
#ping_measurement_filter =
                                        # Filter for the ping measurement (e.g. 'Device Domain' eq 'Test').
#snmp_measurement_filter =
                                        # Filter for the SNMP measurement (e.g. 'Device Domain' eq 'Test').
#snmp_indexes =
                                        # List of SNMP indexes to be used for the SNMP measurement.

#[webservice_<server_id>]               # Optional, if multiple StableNet® servers
                                        # are used. Replace <server_id> with a unique
                                        # identifier for the StableNet® server.
                                        # Only alphanumeric lowercase characters are allowed.
                                        # Please note, that every webservice section
                                        # must have a unique identifier if multiple
                                        # StableNet® servers are used. if the server_id
                                        # changes, a resync of the measurement data is needed
                                        # because the measurement ID change (<measurment_id>@<server_id>).
                                        # If you already have a webservice configured without server_id,
                                        # then it's recommended to leave the existing webservice without
                                        # server_id and add a new webservice with server_id. This avoids
                                        # a resync of the measurement data.

#server_name =
                                        # Unique name of the connected StableNet®
                                        # server. This should be a human readable
                                        # name, not a technical identifier.
                                        # Defaults to server_id if omitted.
#url =
                                        # URL of the StableNet® the web service

Options can also be provided by environment variables using the STABLENET_FDW_WEBSERVICE_ prefix and the option name in upper case (e.g. STABLENET_FDW_WEBSERVICE_URL). For the global settings, GLOBAL has to be omitted from the ENV key (e.g. STABLENET_FDW_DEFAULT_USER ).

url is the base URl of the StableNet® API web service. It usually has the form https://<host>:5443/api/1.

Multiple StableNet® instances can be defined using multiple webservice sections with unique server_id suffixes. The server_id is used as an internal identifier to e.g. get the matching authorization string from SKOOR Auth. An appropriate server_name should be set which is used to identify the server in the foreign and aggregated tables. This can also be configured using environment variables (e.g. STABLENET_FDW_WEBSERVICE_URL_TEST, STABLENET_FDW_WEBSERVICE_SERVER_NAME_TEST).

After a config change, the PostgreSQL Server has to be restarted.

systemctl restart postgresql-13

Initializing foreign tables

Initialized the foreign tables needed by the foreign data wrapper.

/opt/stablenet-fdw/init-foreign-tables.sh -h <host> -p <port> -d <database> -U <user> -o <owner>

e.g.

/opt/stablenet-fdw/init-foreign-tables.sh -U postgres -h localhost -p 5432 -d infosim -o infosim

This command should be performed after every update of eranger-stablenet-fdw to get new table definitions.

Testing the Connection

psql -h localhost -p 5432 -U infosim -d infosim -c "SELECT * from stablenet.stablenet_info;"

This should return the number of devices in the connected StableNet® instance.

Data aggregation (Deprecated)

Initializing aggregated data

Initializes the Timescale hypertable needed for aggregated metric data.

/opt/stablenet-fdw/init-aggregated-data.sh -h <host> -p <port> -d <database> -U <user> -o <owner> \
-i <interval> [-m <metric_keys>] [-f <tag_filter>] [-r <retention>]

-m Comma separated list of metric keys to pre aggregate. Can be omitted to preload data for all metrics, but this might take a very long time. E.g. "SNMP_1,SNMP_2,SNMP_3"

-i Interval to preload data. e.g. 6 months, 1 week, 5 days

-f Tag filter to filter measurements e.g. "'Device Domain' eq 'Test'"

-r Data retention policy, e.g. "1 year"

Initializing aggregated data refresher

Creates a TimescaleDB job to periodically update aggregated data for given metrics

/opt/stablenet-fdw/init-aggregated-data-refresher.sh -h <host> -p <port> -d <database> -U <user> -o <owner> \
[-m <metric_keys>][-f <tag_filter>]

-m Comma separated list of metric keys to aggregate. Can be omitted to refresh data for all metrics. E.g. "SNMP_1,SNMP_2,SNMP_3"

-f Tag filter to filter measurements e.g. "'Device Domain' eq 'Test'"

Add / remove metrics

Docker Compose installation

Configuration

Add the following environment variables to the eranger-postgresql services environment section of the SKOOR docker-compose.yaml and set the STABLENET_FDW_WEBSERVICE_URL variable to a valid StableNet host URL:

      - STABLENET_FDW_WEBSERVICE_URL=https://<StableNet host>:5443/api/1
      - STABLENET_FDW_ACCEPT_INSECURE_CERTIFICATE=false
      - STABLENET_FDW_DEFAULT_USER=myuser
      - STABLENET_FDW_DEFAULT_PASSWORD=mypass
      - STABLENET_FDW_SKOOR_AUTH_URL=http://eranger-auth-${INSTANCE_NAME}:8094

Webservice container configuration

The webservice must be able to communicate with the eranger-auth component to authenticate requests to StableNet. In the eranger-webservice service section, add the following environment variable:

      - SKOOR_WEBSERVICE_auth_service_url=http://eranger-auth-${INSTANCE_NAME}:8094

Initializing foreign tables

In the running container, run the init-database.sh script to drop and recreate the stablenet schema (caution: all objects in the schema are dropped!).

Example with a customer database named infosim with user infosim (set the PGSQL_CONTAINER variable to the eranger-postgresql container name):

export PGSQL_CONTAINER=eranger-postgresql-<instance>
docker exec $PGSQL_CONTAINER /opt/stablenet-fdw/init-foreign-tables.sh -h localhost \
-p 5432 -d infosim -U postgres -o infosim

Run a data query in the dashboard to verify the foreign data wrapper:

SELECT * from stablenet.stablenet_info

Data aggregation

Initializing aggregated data

Initializes the Timescale hypertable needed for aggregated metric data.

docker exec $PGSQL_CONTAINER /opt/stablenet-fdw/init-aggregated-data.sh -h localhost \
-p 5432 -d infosim -U postgres -o infosim -i <interval> [-m <metric_keys>] [-f <tag_filter>] [-r <retention>]

-m Comma separated list of metric keys to pre aggregate. Can be omitted to preload data for all metrics, but this might take a very long time.

-i Interval to preload data. e.g. 6 months, 1 week, 5 days

-f Tag filter to filter measurements e.g. "'Device Domain' eq 'Test'"

-r Data retention policy, e.g. "1 year"

Initializing aggregated data refresher

Creates a TimescaleDB job to periodically update aggregated data for given metrics

docker exec $PGSQL_CONTAINER /opt/stablenet-fdw/init-aggregated-data-refresher.sh -h localhost \
-p 5432 -d infosim -U postgres -o infosim [-m <metric_keys>] [-f <tag_filter>]

-m Comma separated list of metric keys to aggregate. Can be omitted to refresh data for all metrics. E.g. "SNMP_1,SNMP_2,SNMP_3"

-f Tag filter to filter measurements e.g. "'Device Domain' eq 'Test'"

Integrate into SKOOR

The StabeleNet FDW can be integrated by creating a new data source in the Data & Objects section.

image-20250423-054101.png

After saving, the lightbulb in the navigation should turn green.

image-20240313-120727.png

The foreign tables can be inspected on the Data tab.

image-20240313-121026.png

While some foreign tables can be inspected right away (e.g. stablenet_taggable_device), others need some mandatory qualifiers and can not be previewed. See table below for mandatory qualifiers.

Foreign Tables

Name

Fields

Qualifiers

stablenet_info

server_id TEXT
name TEXT
version TEXT
technical_version TEXT

stablenet_taggable

server_id TEXT
domain TEXT
id TEXT
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_id TEXT
id TEXT
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_id TEXT
id TEXT
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_id TEXT
id TEXT
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_id TEXT
id TEXT
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_id TEXT
id TEXT
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_id TEXT
id TEXT
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_id TEXT
domain TEXT
id TEXT
name TEXT
description TEXT

domain TEXT (mandatory)
id BIGINT

stablenet_measurement

server_id TEXT
id TEXT
name TEXT
description TEXT
tags JSONB
dest_device_id TEXT
dest_interface_id TEXT
agent_id TEXT
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_id TEXT
measurement_id TEXT
data_id BIGINT
description TEXT
expected_interval INT
key TEXT
name TEXT
only_average BOOL
unit TEXT

measurement_id BIGINT

stablenet_measurement_data

server_id TEXT
measurement_id TEXT
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.