Docs

StableNet® Foreign Data Wrapper

Prerequisites

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

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 my 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>]

-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'"

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

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
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.

Previous
Third party alarming setup
Next
SKOOR Tutorials