Index: /branches/amp_4_0/platform/tools/install_telegraf.sh =================================================================== --- /branches/amp_4_0/platform/tools/install_telegraf.sh (revision 2695) +++ /branches/amp_4_0/platform/tools/install_telegraf.sh (working copy) @@ -1,27 +1,37 @@ #!/bin/bash +# +# install_telegraf_timescale.sh +# +# Installs/validates Telegraf and configures it to write to TimescaleDB/PostgreSQL. +# Pulls SNMP agents/community/timeout from /etc/telegraf/telegraf.d/apv.conf +# or environment variables (ENV overrides take precedence and will patch apv.conf). +# +# ENV overrides (optional): +# SNMP_AGENTS='192.168.1.10:161,192.168.1.11:161' # or '["a:161","b:161"]' +# SNMP_COMMUNITY='public' +# SNMP_TIMEOUT='2s' +# +# Logs: /var/log/install_telegraf.log +# -set -e +set -euo pipefail # --- Configuration --- LOG_FILE="/var/log/install_telegraf.log" -CONFIG_FILE="/opt/influxdb3_token.toml" + TELEGRAF_CONFIG_DIR="/etc/telegraf/telegraf.d" TELEGRAF_CONFIG="/etc/telegraf/telegraf.conf" -INFLUXDB_HOST="http://localhost:8086" -INFLUXDB_ORG="AN" -TELEGRAF_BUCKET="AMP" -TELEGRAF_VERSION="1.34.1" +APV_CONF="/etc/telegraf/telegraf.d/apv.conf" -# Function to log messages -log_message() { - local level="$1" - local message="$2" - local timestamp="$(date +'%Y-%m-%d %H:%M:%S')" - local log_line="[$level] $timestamp - $message" - echo "$log_line" - echo "$log_line" >> "$LOG_FILE" -} +# Timescale/PostgreSQL connection +PG_HOST="${PG_HOST:-127.0.0.1}" +PG_PORT="${PG_PORT:-5432}" +PG_DB="${PG_DB:-amp_ts}" +PG_USER="${PG_USER:-amp_ts_user}" +PG_PASSWORD="${PG_PASSWORD:-Array@123$}" # override with ENV for safety +# --- Logging helpers --- +log_message() { local level="$1"; local message="$2"; local ts="$(date +'%Y-%m-%d %H:%M:%S')"; local line="[$level] $ts - $message"; echo "$line"; echo "$line" >> "$LOG_FILE"; } log_info() { log_message "INFO" "$1"; } log_error() { log_message "ERROR" "$1" >&2; } log_warning() { log_message "WARNING" "$1"; } \ No newline at end of file @@ -33,82 +43,175 @@ fi } -# --- Check directory permissions --- -log_info "Checking directory permissions..." +# --- Helpers --- +# Convert ENV list to TOML array if needed +env_agents_to_toml() { + local raw="${1:-}" + [[ -z "$raw" ]] && { echo ""; return 0; } + if [[ "$raw" =~ ^\[.*\]$ ]]; then + echo "$raw" + return 0 + fi + local list + list="$(echo "$raw" | tr ', ' '\n' | sed '/^$/d')" + [[ -z "$list" ]] && { echo ""; return 0; } + echo '["'"$(printf '%s\n' "$list" | paste -sd '","' -)"'"]' +} + +# Parse apv.conf to discover agents/community/timeout (best-effort) +pull_snmp_from_apv_conf() { + [[ ! -f "$APV_CONF" ]] && { log_info "apv.conf not found at $APV_CONF; will rely on ENV."; return 0; } + + log_info "Parsing SNMP settings from $APV_CONF ..." + + # Collect agents arrays (single-line recommended) + local agent_lines + agent_lines="$(grep -E '^[[:space:]]*agents[[:space:]]*=' "$APV_CONF" || true)" + + if [[ -n "$agent_lines" ]]; then + # Grab the first occurrence and turn it into a canonical TOML array + local first_agents + first_agents="$(echo "$agent_lines" | head -n1 | sed -E 's/^[^=]*=\s*(.*)$/\1/')" + # If the file already has a TOML array, keep it as-is; else build one + if [[ "$first_agents" =~ ^\[.*\]$ ]]; then + AGENTS_TOML_FROM_APV="$first_agents" + else + # If it's just a single string like "1.2.3.4:161", wrap it + first_agents="$(echo "$first_agents" | sed -E 's/^"([^"]+)"$/\1/')" + AGENTS_TOML_FROM_APV='["'"$first_agents"'"]' + fi + log_info "Agents from apv.conf: $AGENTS_TOML_FROM_APV" + fi + + local community_line timeout_line + community_line="$(grep -m1 -E '^[[:space:]]*community[[:space:]]*=' "$APV_CONF" || true)" + timeout_line="$(grep -m1 -E '^[[:space:]]*timeout[[:space:]]*=' "$APV_CONF" || true)" + + if [[ -n "$community_line" ]]; then + COMMUNITY_FROM_APV="$(sed -E 's/.*"([^"]+)".*/\1/' <<<"$community_line")" + [[ -n "$COMMUNITY_FROM_APV" ]] && log_info "Community from apv.conf: $COMMUNITY_FROM_APV" + fi + if [[ -n "$timeout_line" ]]; then + TIMEOUT_FROM_APV="$(sed -E 's/.*"([^"]+)".*/\1/' <<<"$timeout_line")" + [[ -n "$TIMEOUT_FROM_APV" ]] && log_info "Timeout from apv.conf: $TIMEOUT_FROM_APV" + fi +} + +# Apply ENV overrides to apv.conf in-place (all occurrences) +apply_env_overrides_to_apv_conf() { + [[ ! -f "$APV_CONF" ]] && { log_warning "Cannot patch apv.conf (not found). Skipping override apply."; return 0; } + + local need_patch="false" + + if [[ -n "${SNMP_AGENTS:-}" ]]; then + local toml; toml="$(env_agents_to_toml "${SNMP_AGENTS}")" + if [[ -n "$toml" ]]; then + need_patch="true" + # replace all agents arrays on single lines + sudo sed -i -E "s|^([[:space:]]*agents[[:space:]]*=\s*)\[[^]]*\]|\1${toml}|g" "$APV_CONF" + log_info "Patched apv.conf agents to: $toml" + fi + fi + + if [[ -n "${SNMP_COMMUNITY:-}" ]]; then + need_patch="true" + # quote-preserving replace + sudo sed -i -E "s|^([[:space:]]*community[[:space:]]*=\s*)\"[^\"]*\"|\1\"${SNMP_COMMUNITY}\"|g" "$APV_CONF" + log_info "Patched apv.conf community to: ${SNMP_COMMUNITY}" + fi + + if [[ -n "${SNMP_TIMEOUT:-}" ]]; then + need_patch="true" + sudo sed -i -E "s|^([[:space:]]*timeout[[:space:]]*=\s*)\"[^\"]*\"|\1\"${SNMP_TIMEOUT}\"|g" "$APV_CONF" + log_info "Patched apv.conf timeout to: ${SNMP_TIMEOUT}" + fi + + [[ "$need_patch" == "true" ]] && sudo restorecon -F "$APV_CONF" 2>/dev/null || true +} + +# --- Start --- +log_info "Checking directories and permissions..." for dir in "/var/log" "/etc/telegraf" "$TELEGRAF_CONFIG_DIR"; do if [[ ! -d "$dir" ]]; then - log_warning "$dir directory does not exist. Creating it..." + log_warning "$dir missing. Creating..." sudo mkdir -p "$dir" - [[ $? -ne 0 ]] && log_error "Failed to create directory $dir." && exit 1 fi - [[ ! -w "$dir" ]] && log_error "$dir directory is not writable. Please check permissions." && exit 1 + [[ ! -w "$dir" ]] && { log_error "$dir not writable. Fix permissions."; exit 1; } done -log_info "Directory permissions check complete." -# --- Prerequisites --- -log_info "Checking for required commands..." -for cmd in curl sudo dnf grep sed; do check_command "$cmd"; done +log_info "Checking required commands..." +for cmd in sudo dnf grep sed awk paste; do check_command "$cmd"; done log_info "All required commands are present." -# --- Add InfluxData Repo --- -log_info "Adding InfluxData repository..." -REPO_URL="https://repos.influxdata.com/centos/9/x86_64/stable" -REPO_FILE="/etc/yum.repos.d/influxdata.repo" - -if [[ ! -f "$REPO_FILE" ]]; then - cat </dev/null 2>&1; then + log_info "Telegraf not found. Installing…" + if sudo dnf install -y telegraf; then + log_info "Telegraf installed." + else + log_error "Telegraf not available in OS repositories. Please install manually (RPM/Repo)." + exit 1 + fi else - log_info "InfluxData repo already exists." + log_info "Telegraf already installed." fi -# --- Install Telegraf --- -log_info "Installing Telegraf version $TELEGRAF_VERSION..." -sudo dnf install -y "telegraf-${TELEGRAF_VERSION}" -[[ $? -ne 0 ]] && log_error "Telegraf installation failed." && exit 1 -log_info "Telegraf $TELEGRAF_VERSION installed successfully." +# Pull current values from apv.conf +pull_snmp_from_apv_conf -# --- Read Token --- -log_info "Reading InfluxDB token from $CONFIG_FILE..." -if [[ ! -f "$CONFIG_FILE" ]]; then - log_error "Token file not found. Please run the InfluxDB setup first." +# Resolve final SNMP settings (ENV overrides > apv.conf; no hardcoded defaults) +AGENTS_TOML="$(env_agents_to_toml "${SNMP_AGENTS:-}")" +[[ -z "$AGENTS_TOML" ]] && AGENTS_TOML="${AGENTS_TOML_FROM_APV:-}" + +SNMP_COMMUNITY_FINAL="${SNMP_COMMUNITY:-${COMMUNITY_FROM_APV:-}}" +SNMP_TIMEOUT_FINAL="${SNMP_TIMEOUT:-${TIMEOUT_FROM_APV:-}}" + +# Validate BEFORE patching +if [[ -z "${AGENTS_TOML:-}" ]]; then + log_error "No SNMP agents provided. Set SNMP_AGENTS (comma list or TOML array) or define 'agents = [...]' in ${APV_CONF}." exit 1 fi +if [[ -z "${SNMP_COMMUNITY_FINAL:-}" ]]; then + log_error "No SNMP community provided. Set SNMP_COMMUNITY or define 'community = \"...\"' in ${APV_CONF}." + exit 1 +fi +if [[ -z "${SNMP_TIMEOUT_FINAL:-}" ]]; then + log_error "No SNMP timeout provided. Set SNMP_TIMEOUT or define 'timeout = \"...\"' in ${APV_CONF}." + exit 1 +fi -STORED_TOKEN=$(grep '^token *= *"' "$CONFIG_FILE" | sed -E 's/token *= *"([^"]+)"/\1/') -[[ -z "$STORED_TOKEN" ]] && log_error "Failed to extract token." && exit 1 -log_info "Token read successfully." +# If ENV overrides exist, patch apv.conf so we don't create a duplicate SNMP file +if [[ -n "${SNMP_AGENTS:-}" || -n "${SNMP_COMMUNITY:-}" || -n "${SNMP_TIMEOUT:-}" ]]; then + log_info "Applying ENV overrides to ${APV_CONF}…" + sudo cp -a "$APV_CONF" "${APV_CONF}.bak.$(date +%s)" || true + apply_env_overrides_to_apv_conf +fi -# --- Configure Telegraf --- -log_info "Creating Telegraf configuration..." -sudo mkdir -p "$TELEGRAF_CONFIG_DIR" -sudo tee "$TELEGRAF_CONFIG" > /dev/null </dev/null <&2; } + +require() { command -v "$1" &>/dev/null || { error "Missing command: $1"; exit 1; } } + +[[ $EUID -eq 0 ]] || { error "Run as root (sudo)."; exit 1; } +require dnf +require systemctl +require sed +require tee + +log "Adding TimescaleDB repository…" +tee /etc/yum.repos.d/timescale_timescaledb.repo > /dev/null <<'EOF' +[timescale_timescaledb] +name=timescale_timescaledb +baseurl=https://packagecloud.io/timescale/timescaledb/el/9/$basearch +repo_gpgcheck=1 +enabled=1 +gpgcheck=0 +gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey +sslverify=1 +metadata_expire=300 +EOF + +dnf clean all -y +dnf makecache -y + +log "Installing TimescaleDB CE for PostgreSQL ${PG_VERSION}…" +dnf -y install "timescaledb-2-postgresql-${PG_VERSION}" + +# Optional tuning tool +dnf -y install timescaledb-tools || true + +# Ensure shared_preload_libraries includes timescaledb +if ! grep -q "^shared_preload_libraries.*timescaledb" "$CONF_FILE"; then + if grep -q "^shared_preload_libraries" "$CONF_FILE"; then + sed -i "s/^shared_preload_libraries.*/shared_preload_libraries = 'timescaledb'/" "$CONF_FILE" + else + echo "shared_preload_libraries = 'timescaledb'" >> "$CONF_FILE" + fi +fi + +# Run timescaledb-tune if available (non-interactive) +if command -v timescaledb-tune &>/dev/null; then + log "Running timescaledb-tune…" + timescaledb-tune --quiet --yes --pg-config "${PG_BIN_DIR}/pg_config" || true +fi + +log "Restarting ${PG_SERVICE}…" +systemctl restart "${PG_SERVICE}" + +# Wait for server +log "Waiting for PostgreSQL to accept connections…" +for i in {1..20}; do + sleep 2 + PGPASSWORD="$DB_SUPERUSER_PASSWORD" "${PG_BIN_DIR}/psql" -U "$DB_SUPERUSER" -h 127.0.0.1 -c "SELECT 1" &>/dev/null && break + [[ $i -eq 20 ]] && { error "PostgreSQL not accepting connections."; exit 1; } +done + +# Create role if missing +export PGPASSWORD="$DB_SUPERUSER_PASSWORD" +sudo -E -u postgres "${PG_BIN_DIR}/psql" -v ON_ERROR_STOP=1 </dev/null || true)" +if [[ "${DB_EXISTS:-}" != "1" ]]; then + log "Creating database ${NEW_DB_NAME}…" + sudo -E -u postgres "${PG_BIN_DIR}/psql" -c "CREATE DATABASE ${NEW_DB_NAME}" +else + log "Database ${NEW_DB_NAME} already exists." +fi + +# Enable extension + grants +sudo -E -u postgres "${PG_BIN_DIR}/psql" -v ON_ERROR_STOP=1 < 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_metrics_agent_time ON an_device_metrics (agent_host, time DESC); + +-- 2) MEASUREMENT: an_device_performance +CREATE TABLE IF NOT EXISTS an_device_performance ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + ssl_ae_core_utilization DOUBLE PRECISION, + ssl_se_core_utilization DOUBLE PRECISION, + PRIMARY KEY (time, agent_host) +); +SELECT create_hypertable('an_device_performance','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_perf_agent_time ON an_device_performance (agent_host, time DESC); + +-- 3) TABLE: an_device_storage (HOST-RESOURCES-MIB hrStorageTable) +-- hrStorage values are in allocation units; store alloc_unit to compute bytes later. +CREATE TABLE IF NOT EXISTS an_device_storage ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + prefix TEXT NOT NULL, + size BIGINT, -- hrStorageSize + used BIGINT, -- hrStorageUsed + alloc_unit BIGINT, -- hrStorageAllocationUnits + PRIMARY KEY (time, agent_host, prefix) +); +SELECT create_hypertable('an_device_storage','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_storage_prefix ON an_device_storage (prefix); +CREATE INDEX IF NOT EXISTS idx_storage_agent_time ON an_device_storage (agent_host, time DESC); + +-- 4) TABLE: apv_virtual_stats +CREATE TABLE IF NOT EXISTS apv_virtual_stats ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + serverid TEXT NOT NULL, + addr TEXT NOT NULL, + port TEXT NOT NULL, + protocol TEXT NOT NULL, + url_hits BIGINT, + hostname_hits BIGINT, + perstnt_cookie_hits BIGINT, + qos_cookie_hits BIGINT, + default_hits BIGINT, + perstnt_url_hits BIGINT, + static_hits BIGINT, + qos_network_hits BIGINT, + qos_url_hits BIGINT, + backup_hits BIGINT, + cache_hits BIGINT, + regex_hits BIGINT, + rcookie_hits BIGINT, + icookie_hits BIGINT, + conn_cnt BIGINT, + qos_client_port_hits BIGINT, + qos_body_hits BIGINT, + header_hits BIGINT, + hash_url_hits BIGINT, + redirect_hits BIGINT, + conn_per_sec DOUBLE PRECISION, + in_byte_per_sec DOUBLE PRECISION, + out_byte_per_sec DOUBLE PRECISION, + in_packet_per_sec DOUBLE PRECISION, + out_packet_per_sec DOUBLE PRECISION, + health_status TEXT, + PRIMARY KEY (time, agent_host, serverid, addr, port, protocol) +); +SELECT create_hypertable('apv_virtual_stats','time','agent_host', number_partitions => 8, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_virtual_serverid ON apv_virtual_stats (serverid); +CREATE INDEX IF NOT EXISTS idx_virtual_agent_time ON apv_virtual_stats (agent_host, time DESC); +CREATE INDEX IF NOT EXISTS idx_virtual_server_time ON apv_virtual_stats (serverid, time DESC); + +-- 5) TABLE: apv_real_stats +CREATE TABLE IF NOT EXISTS apv_real_stats ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + real_server_id TEXT NOT NULL, + addr TEXT NOT NULL, + port TEXT NOT NULL, + protocol TEXT NOT NULL, + status TEXT NOT NULL, + rs_cnt_of_req BIGINT, + rs_conn_cnt BIGINT, + rs_total_hits BIGINT, + rs_conn_per_sec DOUBLE PRECISION, + rs_in_byte_per_sec DOUBLE PRECISION, + rs_out_byte_per_sec DOUBLE PRECISION, + rs_in_packet_per_sec DOUBLE PRECISION, + rs_out_packet_per_sec DOUBLE PRECISION, + PRIMARY KEY (time, agent_host, real_server_id, addr, port, protocol) +); +SELECT create_hypertable('apv_real_stats','time','agent_host', number_partitions => 8, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_real_realserverid ON apv_real_stats (real_server_id); +CREATE INDEX IF NOT EXISTS idx_real_agent_time ON apv_real_stats (agent_host, time DESC); +CREATE INDEX IF NOT EXISTS idx_real_server_time ON apv_real_stats (real_server_id, time DESC); + +-- 6) TABLE: apv_llb_stats +-- Note: several fields arrive as strings (e.g., "7.502ms", "7+00:33:52"); keep TEXT. +CREATE TABLE IF NOT EXISTS apv_llb_stats ( + time TIMESTAMPTZ NOT NULL, + agent_host TEXT NOT NULL, + link_index BIGINT, + link_name TEXT NOT NULL, + link_gateway TEXT, + link_status TEXT NOT NULL, + link_resp_time TEXT, + link_up_time TEXT, + link_down_time TEXT, + link_down_count BIGINT, + link_down_event TEXT, + link_bandwid_in BIGINT, + link_bandwid_out BIGINT, + link_thresh BIGINT, + link_hits BIGINT, + link_conn BIGINT, + link_usage BIGINT, + PRIMARY KEY (time, agent_host, link_name) +); +SELECT create_hypertable('apv_llb_stats','time','agent_host', number_partitions => 4, if_not_exists => TRUE); +CREATE INDEX IF NOT EXISTS idx_llb_linkname ON apv_llb_stats (link_name); +CREATE INDEX IF NOT EXISTS idx_llb_agent_time ON apv_llb_stats (agent_host, time DESC); + +-- 7) Ensure NOT using columnstore policies (ignore if function/policy doesn’t exist) +DO $$ BEGIN PERFORM remove_columnstore_policy('apv_virtual_stats'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('apv_real_stats'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('apv_llb_stats'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('an_device_metrics'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('an_device_performance'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_columnstore_policy('an_device_storage'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; + +-- 8) Standard row-compression (NOT columnstore) +ALTER TABLE IF EXISTS apv_virtual_stats SET (timescaledb.compress, timescaledb.compress_segmentby = 'agent_host, serverid', timescaledb.compress_orderby = 'time DESC'); +ALTER TABLE IF EXISTS apv_real_stats SET (timescaledb.compress, timescaledb.compress_segmentby = 'agent_host, real_server_id', timescaledb.compress_orderby = 'time DESC'); +ALTER TABLE IF EXISTS apv_llb_stats SET (timescaledb.compress, timescaledb.compress_segmentby = 'agent_host, link_name', timescaledb.compress_orderby = 'time DESC'); +ALTER TABLE IF EXISTS an_device_metrics SET (timescaledb.compress, timescaledb.compress_segmentby = 'agent_host', timescaledb.compress_orderby = 'time DESC'); +ALTER TABLE IF EXISTS an_device_performance SET (timescaledb.compress, timescaledb.compress_segmentby = 'agent_host', timescaledb.compress_orderby = 'time DESC'); +ALTER TABLE IF EXISTS an_device_storage SET (timescaledb.compress, timescaledb.compress_segmentby = 'agent_host, prefix', timescaledb.compress_orderby = 'time DESC'); + +-- 9) Compression policies (compress after 7 days) +SELECT add_compression_policy('apv_virtual_stats', INTERVAL '7 days'); +SELECT add_compression_policy('apv_real_stats', INTERVAL '7 days'); +SELECT add_compression_policy('apv_llb_stats', INTERVAL '7 days'); +SELECT add_compression_policy('an_device_metrics', INTERVAL '7 days'); +SELECT add_compression_policy('an_device_performance', INTERVAL '7 days'); +SELECT add_compression_policy('an_device_storage', INTERVAL '7 days'); + +-- 10) Retention policies (drop after 180 days) +SELECT add_retention_policy('apv_virtual_stats', INTERVAL '180 days'); +SELECT add_retention_policy('apv_real_stats', INTERVAL '180 days'); +SELECT add_retention_policy('apv_llb_stats', INTERVAL '180 days'); +SELECT add_retention_policy('an_device_metrics', INTERVAL '180 days'); +SELECT add_retention_policy('an_device_performance', INTERVAL '180 days'); +SELECT add_retention_policy('an_device_storage', INTERVAL '180 days'); + +-- 11) Continuous aggregates (5-minute) — WITH NO DATA +-- 11.1) Virtual stats +CREATE MATERIALIZED VIEW IF NOT EXISTS cag_apv_virtual_stats_5m +WITH (timescaledb.continuous) AS +SELECT + time_bucket('5 minutes', time) AS bucket, + agent_host, + serverid, + avg(conn_per_sec) AS avg_connps, + avg(in_byte_per_sec) AS avg_in_bps, + avg(out_byte_per_sec) AS avg_out_bps, + avg(in_packet_per_sec) AS avg_in_pps, + avg(out_packet_per_sec) AS avg_out_pps +FROM apv_virtual_stats +GROUP BY bucket, agent_host, serverid +WITH NO DATA; + +-- 11.2) Real stats +CREATE MATERIALIZED VIEW IF NOT EXISTS cag_apv_real_stats_5m +WITH (timescaledb.continuous) AS +SELECT + time_bucket('5 minutes', time) AS bucket, + agent_host, + real_server_id, + avg(rs_conn_per_sec) AS avg_connps, + avg(rs_in_byte_per_sec) AS avg_in_bps, + avg(rs_out_byte_per_sec) AS avg_out_bps, + avg(rs_in_packet_per_sec) AS avg_in_pps, + avg(rs_out_packet_per_sec) AS avg_out_pps +FROM apv_real_stats +GROUP BY bucket, agent_host, real_server_id +WITH NO DATA; + +-- 11.3) LLB stats (link_resp_time is TEXT → parse number before averaging, assume milliseconds) +CREATE MATERIALIZED VIEW IF NOT EXISTS cag_apv_llb_stats_5m +WITH (timescaledb.continuous) AS +SELECT + time_bucket('5 minutes', time) AS bucket, + agent_host, + link_name, + avg( NULLIF(regexp_replace(link_resp_time, '[^0-9.]', '', 'g'), '')::double precision ) AS avg_resp_time_ms, + avg(link_usage) AS avg_usage, + avg(link_bandwid_in) AS avg_bandwid_in, + avg(link_bandwid_out) AS avg_bandwid_out, + max(link_down_count) AS max_down_count +FROM apv_llb_stats +GROUP BY bucket, agent_host, link_name +WITH NO DATA; + +-- 12) Refresh policies (remove if present, then add) +DO $$ BEGIN PERFORM remove_continuous_aggregate_policy('cag_apv_virtual_stats_5m'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_continuous_aggregate_policy('cag_apv_real_stats_5m'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; +DO $$ BEGIN PERFORM remove_continuous_aggregate_policy('cag_apv_llb_stats_5m'); EXCEPTION WHEN undefined_function THEN NULL; WHEN others THEN NULL; END $$; + +SELECT add_continuous_aggregate_policy('cag_apv_virtual_stats_5m', start_offset => INTERVAL '30 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '15 minutes'); +SELECT add_continuous_aggregate_policy('cag_apv_real_stats_5m', start_offset => INTERVAL '30 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '15 minutes'); +SELECT add_continuous_aggregate_policy('cag_apv_llb_stats_5m', start_offset => INTERVAL '30 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '15 minutes'); + +COMMIT; + +-- 13) Optional immediate backfill (outside tx) +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()); Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/storage.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/storage.py (revision 2695) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/storage.py (working copy) @@ -114,6 +114,7 @@ if path['type'] == "file_system": path['size'] = getdirsize(path['path']) elif path['type'] == "es_query": + httpClient = None try: httpClient = http.client.HTTPConnection(CMDATA['ELASTICSEARCH_IP'], str(CMDATA['ELASTICSEARCH_PORT']),