Index: /branches/amp_4_0/platform/tools/configure_telegraf_timescale.sh =================================================================== --- /branches/amp_4_0/platform/tools/configure_telegraf_timescale.sh (nonexistent) +++ /branches/amp_4_0/platform/tools/configure_telegraf_timescale.sh (working copy) @@ -0,0 +1,114 @@ +#!/bin/bash +# configure_telegraf_timescale.sh +# - No install, no repos, no tokens. +# - Validates at least one SNMP input exists in telegraf.d (apv/ag/asf). +# - Writes telegraf.conf with PostgreSQL output. +# - Restarts Telegraf. + +set -euo pipefail + +LOG_FILE="/var/log/install_telegraf.log" +TELEGRAF_CONFIG_DIR="/etc/telegraf/telegraf.d" +TELEGRAF_CONFIG="/etc/telegraf/telegraf.conf" + +# === Timescale/PostgreSQL target (adjust if needed) === +PG_HOST="127.0.0.1" +PG_PORT="5432" +PG_DB="amp_ts" +PG_USER="amp_ts_user" +PG_PASSWORD="Array@123$" # put this somewhere safer in production + +log() { echo "[$(date +'%F %T')] $*" | tee -a "$LOG_FILE" ; } +need() { command -v "$1" >/dev/null 2>&1 || { log "ERROR: $1 not found"; exit 1; }; } + +# --- sanity checks --- +for c in sudo grep sed awk paste telegraf systemctl; do need "$c"; done +for d in "/var/log" "/etc/telegraf" "$TELEGRAF_CONFIG_DIR"; do + if [[ ! -d "$d" ]]; then + log "WARN: $d missing, creating..." + sudo mkdir -p "$d" + fi +done + +# --- require at least one SNMP input file in telegraf.d --- +SNMP_FILES=( + "/etc/telegraf/telegraf.d/apv.conf" + "/etc/telegraf/telegraf.d/ag.conf" + "/etc/telegraf/telegraf.d/asf.conf" +) + +FOUND_SNMP=false +for f in "${SNMP_FILES[@]}"; do + if [[ -f "$f" ]]; then + FOUND_SNMP=true + # quick validation (best-effort) + grep -qE '^[[:space:]]*agents[[:space:]]*=' "$f" || { log "ERROR: 'agents = [...]' not found in $f"; exit 1; } + grep -qm1 -E '^[[:space:]]*community[[:space:]]*=' "$f" || { log "ERROR: 'community = \"...\"' not found in $f"; exit 1; } + grep -qm1 -E '^[[:space:]]*timeout[[:space:]]*=' "$f" || { log "ERROR: 'timeout = \"...\"' not found in $f"; exit 1; } + log "$(basename "$f") looks OK (agents/community/timeout present)." + fi +done +$FOUND_SNMP || { log "ERROR: no SNMP input file found (expected one of: ${SNMP_FILES[*]})"; exit 1; } + +# --- backup any existing main config --- +if [[ -f "$TELEGRAF_CONFIG" ]]; then + sudo cp -a "$TELEGRAF_CONFIG" "${TELEGRAF_CONFIG}.bak.$(date +%s)" + log "Backed up ${TELEGRAF_CONFIG}." +fi + +# --- write a clean main config that targets Timescale/PostgreSQL --- +log "Writing ${TELEGRAF_CONFIG}..." +sudo tee "$TELEGRAF_CONFIG" >/dev/null </dev/null 2>&1; then + log "ERROR: telegraf --test failed. Inspect output with:" + log " telegraf --config $TELEGRAF_CONFIG --config-directory $TELEGRAF_CONFIG_DIR --test" + exit 1 +fi +log "telegraf --test passed." + +# --- restart telegraf --- +log "Restarting Telegraf..." +sudo systemctl restart telegraf || true +sudo systemctl status telegraf --no-pager -l | sed -n '1,40p' || true + +if systemctl is-active --quiet telegraf; then + log "Telegraf is active." +else + log "ERROR: Telegraf failed to start. See: journalctl -u telegraf -n 200 --no-pager" + exit 1 +fi + +log "Done." Index: /branches/amp_4_0/platform/tools/telegraf_snmp_timescale.sql =================================================================== --- /branches/amp_4_0/platform/tools/telegraf_snmp_timescale.sql (revision 2700) +++ /branches/amp_4_0/platform/tools/telegraf_snmp_timescale.sql (working copy) @@ -238,3 +238,499 @@ CALL refresh_continuous_aggregate('cag_apv_virtual_stats_5m', now() - INTERVAL '30 days', now()); CALL refresh_continuous_aggregate('cag_apv_real_stats_5m', now() - INTERVAL '30 days', now()); CALL refresh_continuous_aggregate('cag_apv_llb_stats_5m', now() - INTERVAL '30 days', now()); + + +/* ========================= + AG (Array Gateway) schema + ========================= */ + +BEGIN; + +-- 1) MEASUREMENT: ag_device_metrics (from [[inputs.snmp]] name="snmp_system") +CREATE TABLE IF NOT EXISTS ag_device_metrics ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + cpu_usage DOUBLE PRECISION, + net_mem_usage DOUBLE PRECISION, + total_openssl_conns BIGINT, + connections BIGINT, + requests BIGINT, + total_in BIGINT, + total_out BIGINT, + PRIMARY KEY (time, agent_host) +); +SELECT create_hypertable('ag_device_metrics','time','agent_host', + number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_ag_metrics_agent_time ON ag_device_metrics (agent_host, time DESC); + +-- 2) TABLE: ag_virtual_site_stats (from [[inputs.snmp.table]] name="virtualSiteStats") +CREATE TABLE IF NOT EXISTS ag_virtual_site_stats ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + id TEXT NOT NULL, + ip TEXT NOT NULL, + active_sessions BIGINT, + success_login BIGINT, + failure_login BIGINT, + error_login BIGINT, + success_logout BIGINT, + client_bytes_in BIGINT, + client_bytes_out BIGINT, + locked_login BIGINT, + rejected_login BIGINT, + server_bytes_in BIGINT, + server_bytes_out BIGINT, + PRIMARY KEY (time, agent_host, id, ip) +); +SELECT create_hypertable('ag_virtual_site_stats','time','agent_host', + number_partitions => 8, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_ag_vsite_id ON ag_virtual_site_stats (id); +CREATE INDEX IF NOT EXISTS idx_ag_vsite_agent_time ON ag_virtual_site_stats (agent_host, time DESC); +CREATE INDEX IF NOT EXISTS idx_ag_vsite_id_time ON ag_virtual_site_stats (id, time DESC); + +-- 3) TABLE: ag_vpn_stats (from [[inputs.snmp.table]] name="vpnStats") +CREATE TABLE IF NOT EXISTS ag_vpn_stats ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + id TEXT NOT NULL, + tunnels_open BIGINT, + tunnels_est BIGINT, + tunnels_rejected BIGINT, + tunnels_terminated BIGINT, + bytes_in BIGINT, + bytes_out BIGINT, + unauth_packets_in BIGINT, + client_app_bytes_in BIGINT, + client_app_bytes_out BIGINT, + PRIMARY KEY (time, agent_host, id) +); +SELECT create_hypertable('ag_vpn_stats','time','agent_host', + number_partitions => 8, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_ag_vpn_id ON ag_vpn_stats (id); +CREATE INDEX IF NOT EXISTS idx_ag_vpn_agent_time ON ag_vpn_stats (agent_host, time DESC); +CREATE INDEX IF NOT EXISTS idx_ag_vpn_id_time ON ag_vpn_stats (id, time DESC); + +-- 4) TABLE: ag_web_stats (from [[inputs.snmp.table]] name="webStats") +CREATE TABLE IF NOT EXISTS ag_web_stats ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + id TEXT NOT NULL, + authorized_req BIGINT, + unauthorized_req BIGINT, + client_bytes_in BIGINT, + client_bytes_out BIGINT, + server_bytes_in BIGINT, + server_bytes_out BIGINT, + PRIMARY KEY (time, agent_host, id) +); +SELECT create_hypertable('ag_web_stats','time','agent_host', + number_partitions => 8, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_ag_web_id ON ag_web_stats (id); +CREATE INDEX IF NOT EXISTS idx_ag_web_agent_time ON ag_web_stats (agent_host, time DESC); +CREATE INDEX IF NOT EXISTS idx_ag_web_id_time ON ag_web_stats (id, time DESC); + +-- 5) Compression (row compression; segment by identity tags, order by time desc) +ALTER TABLE IF EXISTS ag_device_metrics SET (timescaledb.compress, + timescaledb.compress_segmentby = 'agent_host', + timescaledb.compress_orderby = 'time DESC'); + +ALTER TABLE IF EXISTS ag_virtual_site_stats SET (timescaledb.compress, + timescaledb.compress_segmentby = 'agent_host, id', + timescaledb.compress_orderby = 'time DESC'); + +ALTER TABLE IF EXISTS ag_vpn_stats SET (timescaledb.compress, + timescaledb.compress_segmentby = 'agent_host, id', + timescaledb.compress_orderby = 'time DESC'); + +ALTER TABLE IF EXISTS ag_web_stats SET (timescaledb.compress, + timescaledb.compress_segmentby = 'agent_host, id', + timescaledb.compress_orderby = 'time DESC'); + +-- 6) Compression policies (compress after 7 days) +SELECT add_compression_policy('ag_device_metrics', INTERVAL '7 days'); +SELECT add_compression_policy('ag_virtual_site_stats', INTERVAL '7 days'); +SELECT add_compression_policy('ag_vpn_stats', INTERVAL '7 days'); +SELECT add_compression_policy('ag_web_stats', INTERVAL '7 days'); + +-- 7) Retention policies (drop after 180 days) +SELECT add_retention_policy('ag_device_metrics', INTERVAL '180 days'); +SELECT add_retention_policy('ag_virtual_site_stats', INTERVAL '180 days'); +SELECT add_retention_policy('ag_vpn_stats', INTERVAL '180 days'); +SELECT add_retention_policy('ag_web_stats', INTERVAL '180 days'); + +-- 8) Continuous aggregates (5-minute) +-- NOTE: Most values look like gauges/counters sampled by Telegraf. +-- For simplicity we expose AVG over the bucket. If you later switch to true counters, +-- use Timescale Toolkit or DERIVATIVE in a view to compute rates. +CREATE MATERIALIZED VIEW IF NOT EXISTS cag_ag_device_metrics_5m +WITH (timescaledb.continuous) AS +SELECT + time_bucket('5 minutes', time) AS bucket, + agent_host, + avg(cpu_usage) AS avg_cpu_pct, + avg(net_mem_usage) AS avg_net_mem_pct, + avg(total_openssl_conns) AS avg_total_openssl_conns, + avg(connections) AS avg_connections, + avg(requests) AS avg_requests, + avg(total_in) AS avg_total_in, + avg(total_out) AS avg_total_out +FROM ag_device_metrics +GROUP BY bucket, agent_host +WITH NO DATA; + +CREATE MATERIALIZED VIEW IF NOT EXISTS cag_ag_virtual_site_stats_5m +WITH (timescaledb.continuous) AS +SELECT + time_bucket('5 minutes', time) AS bucket, + agent_host, + id, + avg(active_sessions) AS avg_active_sessions, + avg(success_login) AS avg_success_login, + avg(failure_login) AS avg_failure_login, + avg(error_login) AS avg_error_login, + avg(success_logout) AS avg_success_logout, + avg(client_bytes_in) AS avg_client_bytes_in, + avg(client_bytes_out) AS avg_client_bytes_out, + avg(locked_login) AS avg_locked_login, + avg(rejected_login) AS avg_rejected_login, + avg(server_bytes_in) AS avg_server_bytes_in, + avg(server_bytes_out) AS avg_server_bytes_out +FROM ag_virtual_site_stats +GROUP BY bucket, agent_host, id +WITH NO DATA; + +CREATE MATERIALIZED VIEW IF NOT EXISTS cag_ag_vpn_stats_5m +WITH (timescaledb.continuous) AS +SELECT + time_bucket('5 minutes', time) AS bucket, + agent_host, + id, + avg(tunnels_open) AS avg_tunnels_open, + avg(tunnels_est) AS avg_tunnels_est, + avg(tunnels_rejected) AS avg_tunnels_rejected, + avg(tunnels_terminated) AS avg_tunnels_terminated, + avg(bytes_in) AS avg_bytes_in, + avg(bytes_out) AS avg_bytes_out, + avg(unauth_packets_in) AS avg_unauth_packets_in, + avg(client_app_bytes_in) AS avg_client_app_bytes_in, + avg(client_app_bytes_out) AS avg_client_app_bytes_out +FROM ag_vpn_stats +GROUP BY bucket, agent_host, id +WITH NO DATA; + +CREATE MATERIALIZED VIEW IF NOT EXISTS cag_ag_web_stats_5m +WITH (timescaledb.continuous) AS +SELECT + time_bucket('5 minutes', time) AS bucket, + agent_host, + id, + avg(authorized_req) AS avg_authorized_req, + avg(unauthorized_req) AS avg_unauthorized_req, + avg(client_bytes_in) AS avg_client_bytes_in, + avg(client_bytes_out) AS avg_client_bytes_out, + avg(server_bytes_in) AS avg_server_bytes_in, + avg(server_bytes_out) AS avg_server_bytes_out +FROM ag_web_stats +GROUP BY bucket, agent_host, id +WITH NO DATA; + +-- 9) Refresh policies for CAs (drop if present, then add) +DO $$ BEGIN PERFORM remove_continuous_aggregate_policy('cag_ag_device_metrics_5m'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_continuous_aggregate_policy('cag_ag_virtual_site_stats_5m'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_continuous_aggregate_policy('cag_ag_vpn_stats_5m'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_continuous_aggregate_policy('cag_ag_web_stats_5m'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; + +SELECT add_continuous_aggregate_policy('cag_ag_device_metrics_5m', + start_offset => INTERVAL '30 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '15 minutes'); +SELECT add_continuous_aggregate_policy('cag_ag_virtual_site_stats_5m', + start_offset => INTERVAL '30 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '15 minutes'); +SELECT add_continuous_aggregate_policy('cag_ag_vpn_stats_5m', + start_offset => INTERVAL '30 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '15 minutes'); +SELECT add_continuous_aggregate_policy('cag_ag_web_stats_5m', + start_offset => INTERVAL '30 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '15 minutes'); + +COMMIT; + +-- 10) Optional immediate backfill (same window as APV) +CALL refresh_continuous_aggregate('cag_ag_device_metrics_5m', now() - INTERVAL '30 days', now()); +CALL refresh_continuous_aggregate('cag_ag_virtual_site_stats_5m', now() - INTERVAL '30 days', now()); +CALL refresh_continuous_aggregate('cag_ag_vpn_stats_5m', now() - INTERVAL '30 days', now()); +CALL refresh_continuous_aggregate('cag_ag_web_stats_5m', now() - INTERVAL '30 days', now()); + + +/* ========================= + ASF (Array Gateway) schema + ========================= */ + + +BEGIN; +CREATE EXTENSION IF NOT EXISTS timescaledb; + +-- 1) asf_device_metrics (single OIDs above) +CREATE TABLE IF NOT EXISTS asf_device_metrics ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + cpu_usage DOUBLE PRECISION, + mem_usage DOUBLE PRECISION, + net_mem_usage DOUBLE PRECISION, + total_openssl_conns BIGINT, + connections BIGINT, + requests BIGINT, + total_in BIGINT, + total_out BIGINT, + PRIMARY KEY (time, agent_host) +); +SELECT create_hypertable('asf_device_metrics','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_asf_devmetrics_agent_time ON asf_device_metrics (agent_host, time DESC); + +-- 2) asf_device_storage +CREATE TABLE IF NOT EXISTS asf_device_storage ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + prefix TEXT NOT NULL, + size BIGINT, + used BIGINT, + alloc_unit BIGINT, + PRIMARY KEY (time, agent_host, prefix) +); +SELECT create_hypertable('asf_device_storage','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_asf_storage_prefix ON asf_device_storage (prefix); +CREATE INDEX IF NOT EXISTS idx_asf_storage_agent_time ON asf_device_storage (agent_host, time DESC); + +-- 3) asf_ssl_statistics (totals) +CREATE TABLE IF NOT EXISTS asf_ssl_statistics ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + total_openssl_conns BIGINT, + total_accepted_conns BIGINT, + total_requested_conns BIGINT, + PRIMARY KEY (time, agent_host) +); +SELECT create_hypertable('asf_ssl_statistics','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_asf_sslstats_agent_time ON asf_ssl_statistics (agent_host, time DESC); + +-- 4) asf_ssl_host_statistics +CREATE TABLE IF NOT EXISTS asf_ssl_host_statistics ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + ssl_index BIGINT, + vhost_name TEXT, + open_ssl_conns BIGINT, + accepted_conns BIGINT, + requested_conns BIGINT, + resumed_sess BIGINT, + resumable_sess BIGINT, + miss_sess BIGINT, + conns_per_sec DOUBLE PRECISION, + PRIMARY KEY (time, agent_host, ssl_index) +); +SELECT create_hypertable('asf_ssl_host_statistics','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_asf_sslhost_vhost_time ON asf_ssl_host_statistics (vhost_name, time DESC); +CREATE INDEX IF NOT EXISTS idx_asf_sslhost_agent_time ON asf_ssl_host_statistics (agent_host, time DESC); + +-- 5) asf_vip_group_statistics +CREATE TABLE IF NOT EXISTS asf_vip_group_statistics ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + vip_status BIGINT, + host_name TEXT, + current_tme TEXT, + total_ip_pkts_in BIGINT, + total_ip_pkts_out BIGINT, + total_ip_bytes_in BIGINT, + total_ip_bytes_out BIGINT, + PRIMARY KEY (time, agent_host) +); +SELECT create_hypertable('asf_vip_group_statistics','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_asf_vipgrp_agent_time ON asf_vip_group_statistics (agent_host, time DESC); + +-- 6) asf_vip_statistics (per IP) +CREATE TABLE IF NOT EXISTS asf_vip_statistics ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + ip_index BIGINT NOT NULL, + ip_address TEXT, + ip_pkts_in BIGINT, + ip_bytes_in BIGINT, + ip_pkts_out BIGINT, + ip_bytes_out BIGINT, + start_time TEXT, + ip_addr_type TEXT, + PRIMARY KEY (time, agent_host, ip_index) +); +SELECT create_hypertable('asf_vip_statistics','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_asf_vip_ip_time ON asf_vip_statistics (ip_address, time DESC); +CREATE INDEX IF NOT EXISTS idx_asf_vip_agent_time ON asf_vip_statistics (agent_host, time DESC); + +-- 7) asf_syslog_history +CREATE TABLE IF NOT EXISTS asf_syslog_history ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + idx BIGINT NOT NULL, + severity BIGINT, + msg_text TEXT, + PRIMARY KEY (time, agent_host, idx) +); +SELECT create_hypertable('asf_syslog_history','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_asf_syslog_agent_time ON asf_syslog_history (agent_host, time DESC); + +-- 8) asf_performance_statistics +CREATE TABLE IF NOT EXISTS asf_performance_statistics ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + cpu_utilization DOUBLE PRECISION, + connections_per_sec DOUBLE PRECISION, + requests_per_sec DOUBLE PRECISION, + ssl_core_utilization DOUBLE PRECISION, + ssl_ae_core_utilization DOUBLE PRECISION, + ssl_se_core_utilization DOUBLE PRECISION, + PRIMARY KEY (time, agent_host) +); +SELECT create_hypertable('asf_performance_statistics','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_asf_perf_agent_time ON asf_performance_statistics (agent_host, time DESC); + +-- 9) asf_http_service +CREATE TABLE IF NOT EXISTS asf_http_service ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + http_service_index BIGINT NOT NULL, + http_service_id TEXT, + http_service_cc BIGINT, + http_service_cps BIGINT, + http_service_rps_get BIGINT, + http_service_rps_post BIGINT, + http_service_rps_head BIGINT, + http_service_rps_put BIGINT, + http_service_rps_delete BIGINT, + http_service_rps_total BIGINT, + http_service_anomaly_method BIGINT, + http_service_anomaly_requestline BIGINT, + http_service_anomaly_host BIGINT, + http_service_anomaly_connection BIGINT, + http_service_anomaly_contentlength BIGINT, + http_service_anomaly_range BIGINT, + http_service_traffic_inbound_in_byte BIGINT, + http_service_traffic_inbound_in_packet BIGINT, + http_service_traffic_inbound_out_byte BIGINT, + http_service_traffic_inbound_out_packet BIGINT, + http_service_traffic_outbound_in_byte BIGINT, + http_service_traffic_outbound_in_packet BIGINT, + http_service_traffic_outbound_out_byte BIGINT, + http_service_traffic_outbound_out_packet BIGINT, + http_service_drop_total BIGINT, + http_service_drop_type_source BIGINT, + http_service_drop_type_man_bl BIGINT, + http_service_drop_type_dyn_bl BIGINT, + http_service_drop_type_acl BIGINT, + http_service_drop_type_ddos BIGINT, + http_service_drop_type_waf BIGINT, + http_service_drop_type_filter BIGINT, + http_service_drop_type_anomaly BIGINT, + http_service_drop_type_parse_fail BIGINT, + http_service_drop_type_resource BIGINT, + http_service_drop_type_profile BIGINT, + PRIMARY KEY (time, agent_host, http_service_index) +); +SELECT create_hypertable('asf_http_service','time','agent_host', number_partitions => 8, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_asf_http_id_time ON asf_http_service (http_service_id, time DESC); +CREATE INDEX IF NOT EXISTS idx_asf_http_agent_time ON asf_http_service (agent_host, time DESC); + +-- 10) asf_https_service +CREATE TABLE IF NOT EXISTS asf_https_service ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + https_service_index BIGINT NOT NULL, + https_service_id TEXT, + https_service_cc BIGINT, + https_service_cps BIGINT, + https_service_rps_get BIGINT, + https_service_rps_post BIGINT, + https_service_rps_head BIGINT, + https_service_rps_put BIGINT, + https_service_rps_delete BIGINT, + https_service_rps_total BIGINT, + https_service_anomaly_method BIGINT, + https_service_anomaly_requestline BIGINT, + https_service_anomaly_host BIGINT, + https_service_anomaly_connection BIGINT, + https_service_anomaly_contentlength BIGINT, + https_service_anomaly_range BIGINT, + https_service_traffic_inbound_in_byte BIGINT, + https_service_traffic_inbound_in_packets BIGINT, + https_service_traffic_inbound_out_byte BIGINT, + https_service_traffic_inbound_out_packets BIGINT, + https_service_traffic_outbound_in_byte BIGINT, + https_service_traffic_outbound_in_packets BIGINT, + https_service_traffic_outbound_out_byte BIGINT, + https_service_traffic_outbound_out_packets BIGINT, + https_service_ssl_traffic_inbound_in_byte BIGINT, + https_service_ssl_traffic_inbound_in_packets BIGINT, + https_service_ssl_traffic_inbound_out_byte BIGINT, + https_service_ssl_traffic_inbound_out_packets BIGINT, + https_service_ssl_traffic_outbound_in_byte BIGINT, + https_service_ssl_traffic_outbound_in_packets BIGINT, + https_service_ssl_traffic_outbound_out_byte BIGINT, + https_service_ssl_traffic_outbound_out_packets BIGINT, + https_service_http_drop_total BIGINT, + https_service_http_drop_type_source BIGINT, + https_service_http_drop_type_man_bl BIGINT, + https_service_http_drop_type_dyn_bl BIGINT, + https_service_http_drop_type_acl BIGINT, + https_service_http_drop_type_ddos BIGINT, + https_service_http_drop_type_waf BIGINT, + https_service_http_drop_type_filter BIGINT, + https_service_http_drop_type_anomaly BIGINT, + https_service_http_drop_type_parse_fail BIGINT, + https_service_http_drop_type_resource BIGINT, + https_service_http_drop_type_profile BIGINT, + PRIMARY KEY (time, agent_host, https_service_index) +); +SELECT create_hypertable('asf_https_service','time','agent_host', number_partitions => 8, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_asf_https_id_time ON asf_https_service (https_service_id, time DESC); +CREATE INDEX IF NOT EXISTS idx_asf_https_agent_time ON asf_https_service (agent_host, time DESC); + +-- Compression (row), 7d; Retention 180d (mirror AG/APV style) +DO $$ BEGIN PERFORM remove_columnstore_policy('asf_device_metrics'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('asf_device_storage'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('asf_ssl_statistics'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('asf_ssl_host_statistics'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('asf_vip_group_statistics'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('asf_vip_statistics'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('asf_syslog_history'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('asf_performance_statistics'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('asf_http_service'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('asf_https_service'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; + +ALTER TABLE IF EXISTS asf_device_metrics SET (timescaledb.compress, timescaledb.compress_segmentby='agent_host', timescaledb.compress_orderby='time DESC'); +ALTER TABLE IF EXISTS asf_device_storage SET (timescaledb.compress, timescaledb.compress_segmentby='agent_host, prefix', timescaledb.compress_orderby='time DESC'); +ALTER TABLE IF EXISTS asf_ssl_statistics SET (timescaledb.compress, timescaledb.compress_segmentby='agent_host', timescaledb.compress_orderby='time DESC'); +ALTER TABLE IF EXISTS asf_ssl_host_statistics SET (timescaledb.compress, timescaledb.compress_segmentby='agent_host, ssl_index', timescaledb.compress_orderby='time DESC'); +ALTER TABLE IF EXISTS asf_vip_group_statistics SET (timescaledb.compress, timescaledb.compress_segmentby='agent_host', timescaledb.compress_orderby='time DESC'); +ALTER TABLE IF EXISTS asf_vip_statistics SET (timescaledb.compress, timescaledb.compress_segmentby='agent_host, ip_index', timescaledb.compress_orderby='time DESC'); +ALTER TABLE IF EXISTS asf_syslog_history SET (timescaledb.compress, timescaledb.compress_segmentby='agent_host', timescaledb.compress_orderby='time DESC'); +ALTER TABLE IF EXISTS asf_performance_statistics SET (timescaledb.compress, timescaledb.compress_segmentby='agent_host', timescaledb.compress_orderby='time DESC'); +ALTER TABLE IF EXISTS asf_http_service SET (timescaledb.compress, timescaledb.compress_segmentby='agent_host, http_service_index', timescaledb.compress_orderby='time DESC'); +ALTER TABLE IF EXISTS asf_https_service SET (timescaledb.compress, timescaledb.compress_segmentby='agent_host, https_service_index', timescaledb.compress_orderby='time DESC'); + +SELECT add_compression_policy('asf_device_metrics', INTERVAL '7 days'); +SELECT add_compression_policy('asf_device_storage', INTERVAL '7 days'); +SELECT add_compression_policy('asf_ssl_statistics', INTERVAL '7 days'); +SELECT add_compression_policy('asf_ssl_host_statistics', INTERVAL '7 days'); +SELECT add_compression_policy('asf_vip_group_statistics', INTERVAL '7 days'); +SELECT add_compression_policy('asf_vip_statistics', INTERVAL '7 days'); +SELECT add_compression_policy('asf_syslog_history', INTERVAL '7 days'); +SELECT add_compression_policy('asf_performance_statistics', INTERVAL '7 days'); +SELECT add_compression_policy('asf_http_service', INTERVAL '7 days'); +SELECT add_compression_policy('asf_https_service', INTERVAL '7 days'); + +SELECT add_retention_policy('asf_device_metrics', INTERVAL '180 days'); +SELECT add_retention_policy('asf_device_storage', INTERVAL '180 days'); +SELECT add_retention_policy('asf_ssl_statistics', INTERVAL '180 days'); +SELECT add_retention_policy('asf_ssl_host_statistics', INTERVAL '180 days'); +SELECT add_retention_policy('asf_vip_group_statistics', INTERVAL '180 days'); +SELECT add_retention_policy('asf_vip_statistics', INTERVAL '180 days'); +SELECT add_retention_policy('asf_syslog_history', INTERVAL '180 days'); +SELECT add_retention_policy('asf_performance_statistics', INTERVAL '180 days'); +SELECT add_retention_policy('asf_http_service', INTERVAL '180 days'); +SELECT add_retention_policy('asf_https_service', INTERVAL '180 days'); +COMMIT;