Preparation

psql -U infosim -d infosim -c "SELECT delete_job((SELECT max(job_id) FROM timescaledb_information.jobs where proc_schema = 'stablenet' AND proc_name = 'update_measurement_data_aggregated'));"

Ensure that the settings in /etc/stablenet-fdw/stablenet-fdw.conf are correct.

Initial Synchronization

The following commands are used to sync measurement data from StableNet/Oracle/MySQL.

The commands should be run manually the first time in sequential order to ensure everything is working fine.

When running the commands for the first time, only data for the current day are synced. Historical data can be synced later, after setup of the automatic synchronization.

The server_id argument is only needed if configured. This commands have to be executed for every server_id separately.

Measurement Sync

sudo -u eranger /opt/stablenet-fdw/measurement-data-sync/measurement-sync.sh [server_id>]

This command syncs the measurements from the StableNet API based on the configured tag filters. This is later used in the incremental sync to sync only data for required measurements.

Incremental Sync

sudo -u eranger /opt/stablenet-fdw/measurement-data-sync/incremental-sync.sh [server_id>]

This command syncs raw measurement data from Oracle/MySQL to PostgreSQL. With every run, only new data is synced. The data is stored in sn_ping_data, sn_snmp_data and sn_usrscript_data.

Aggregate Sync

sudo -u eranger /opt/stablenet-fdw/measurement-data-sync/aggregate-sync.sh [server_id>]

Aggregates raw data from incremental sync into minutely, 5 minutely and 15 minutely buckets. Data is stored in stablenet_measurement_data_minutely, stablenet_measurement_data_5_minutely and stablenet_measurement_data_15_minutely. The resulting tables contain value_avg, value_min and value_max colmns for the aggregated rows.

Aggregate Hourly Sync

sudo -u eranger /opt/stablenet-fdw/measurement-data-sync/aggregate-hourly-sync.sh [server_id>]

Aggregates data from the stablenet_measurement_data_minutely table into stablenet_measurement_data_hourly. This table does not contain average, min and max values, but timescaledb statistic columns instead (stats_agg statssummary2d, stats_agg_max statssummary2d, percentile_agg uddsketch, percentile_agg_max uddsketch). This columns can later be used to get many statistical information about the aggregated data.

Stats Sync

sudo -u eranger /opt/stablenet-fdw/measurement-data-sync/aggregate-hourly-sync.sh [server_id>]

Performs a rollup on statistical data from stablenet_measurement_data_hourly into stablenet_measurement_data_daily, stablenet_measurement_data_weekly, stablenet_measurement_data_monthly, stablenet_measurement_data_quartely and stablenet_measurement_data_yearly.

Automatic Synchronization

After running the sync commands manually, we want to setup some jobs in SKOOR Engine to perform the sychronization automatically.

1. Create a group called StableNet Data Sync in root

   

2. Create a device called localhost below the StableNet Data Sync group

   

3. Create 5 Execute jobs below localhost

   1. StableNet Measurement Sync [server_id]
      Interval: 1 hour, fix, if longer immediately
      Program path: /opt/stablenet-fdw/measurement-data-sync/measurement-sync.sh [server_id]
      Save output to file: stablenet-measurement-sync.log

   2. StableNet Incremental Sync [server_id]
      Interval: 1 minute, fix, if longer immediately
      Program path: /opt/stablenet-fdw/measurement-data-sync/incremental-sync.sh [server_id]
      Save output to file: stablenet-incremental-sync.log

   3. StableNet Aggregate Sync
      Interval: 5 minutes, fix, if longer immediately
      Program path: /opt/stablenet-fdw/measurement-data-sync/aggregate-sync.sh [server_id]
      Save output to file: stablenet-aggregate-sync[-server_id].log

   4. StableNet Aggregate Hourly Sync
      Interval: 15 minutes, fix, if longer immediately
      Program path: /opt/stablenet-fdw/measurement-data-sync/aggregate-hourly-sync.sh [server_id]
      Save output to file: stablenet-aggregate-hourly-sync[-server_id].log

   5. StableNet Stats Sync
      Interval: 1 hour, fix, if longer immediately
      Program path: /opt/stablenet-fdw/measurement-data-sync/stats-sync.sh [server_id]
      Save output to file: stablenet-stats-sync[-server_id].log

History Sync

sudo -u eranger /opt/stablenet-fdw/measurement-data-sync/history-sync.sh [server_id>]

This commands syncs historical data based on the history_days config.