Documentation

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.

[webservice]  

url =
                                        # URL of StableNet® the web service

#[webservice_<server_id>]               # Optional, if multiple StableNet® servers 
                                        # are used. Replace <server_id> with a unique
                                        # identifier for the StableNet® server.
                                        # Only alphanumeric lower case characters are allowed.
                                        # Please note, that every webservice section 
                                        # must have a unique identifier if multiple 
                                        # StableNet® servers are used.

#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

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-20240313-115126.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_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
tags 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.

Previous
StableNet® UI and styles
Next
CashPilot® integration