DocumentationStableNet® 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.
After saving, the lightbulb in the navigation should turn green.
The foreign tables can be inspected on the Data tab.
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 |
|---|---|---|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.