Aggregated Measurement Data

Preparation

If the old data aggregation (<=8.1.2) is active, it is needed to disable the PostgreSQL sync job.

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

Furthermore it’s advised to remove the old aggregate tables and refactor the data queries to use the ones created by the new data sync.

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. Additionally, it aggregates this raw data into the stablenet_measurement_data_minutely table which contains value_avg, value_min and value_max.

Aggregate Sync

This step is optional and only required if one needs the 5 and 15 minutes buckets. If not, continue with the hourly sync instead.

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

Aggregates raw data from incremental sync into 5 minutely and 15 minutely buckets. Data is stored in 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.

Create a group called StableNet Data Sync in root
Create a device called localhost below the StableNet Data Sync group
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 (OPTIONAL, only add if you need 5 and 15 minute buckets)
  Interval: 1 minute, 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: 10 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

Run this command AFTER the initial sync and AFTER setting up the automatic synchronization.

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

This command syncs historical data based on the [history_sync] days, start_date and end_date config. Usually, for an initial history sync, you might only want to set days or keeps the settings as is and simply sync back for one year.

It might also be a good idea to use nohup to run the command as it might take quite some time to complete.