- Download Diff

Summary:

AE-2115: Install TimeScale DB and populating APV data

Review Request #1038 — Created Aug. 29, 2025 and submitted Sept. 3, 2025, 4:41 p.m.

Information
Owner:	apoorva.sn
Repository:	AMP
Branch:	amp_4_0
Bugs:	AE-2107
Depends On:
Reviewers
Groups:
People:	ngurunathan, pmurugaiyan, pradeep, shuinvy

Description

Problem

adding scripts to install Timescale DB and create Hypertables for populate the DB with APV data.

Solution

Since TimeScaleDB has a gentler learning curve, provides a clear migration path, and may suit our needs without requiring a hardware refresh.
Going with moving to TimeScaleDB for Time Series DB.

Have come up with the script for TimeScaleDB installation and also .sql file to create hypertables for the data to get inserted successfully

Testing Done

Data is successfully getting inserted in all tables
amp_ts=> select * time stnt_url_hits | static_hits header_hits | hash_url_hits ----------------------------- --------------+-------------+ ------------+---------------+ 2025-08-26 00:27:45.64191+05:30 |             | |               | 2025-08-29 17:25:20+05:30 0 |          30 | 0 |             0 | 2025-08-29 17:25:30+05:30 0 |          30 | 0 |             0 | 2025-08-29 17:25:40+05:30 0 |          30 | 0 |             0 | 2025-08-29 17:25:50+05:30 0 |          30 | 0 |             0 | 2025-08-29 17:26:00+05:30 0 |          30 | 0 |             0 | 2025-08-29 17:26:10+05:30 0 |          30 | 0 |             0 | 2025-08-29 17:26:20+05:30 0 |          30 | 0 |             0 | 2025-08-29 17:26:30+05:30 0 |          30 | 0 |             0 | 2025-08-29 17:26:40+05:30 0 |          30 | 0 |             0 | (10 rows)

from apv_virtual_stats limit 10;
 |   agent_host    | serverid |      addr      | port | protocol | url_hits | hostname_hits | perstnt_cookie_hits | qos_cookie_hits | default_hits | per
 | qos_network_hits | qos_url_hits | backup_hits | cache_hits | regex_hits | rcookie_hits | icookie_hits | conn_cnt | qos_client_port_hits | qos_body_hits | 
 | redirect_hits | conn_per_sec | in_byte_per_sec | out_byte_per_sec | in_packet_per_sec | out_packet_per_sec | health_status |         host        
 ----+-----------------+----------+----------------+------+----------+----------+---------------+---------------------+-----------------+--------------+----
 ------------------+--------------+-------------+------------+------------+--------------+--------------+----------+----------------------+---------------+-
 ---------------+--------------+-----------------+------------------+-------------------+--------------------+---------------+-----------------------
 | 192.168.162.166 | vs1      | 192.168.162.92 | 443  | 5        |          |               |                     |                 |              |  
 |              |             |            |            |              |              |          |                      |               | 
 |              |                 |                  |                   |                    |               | 
 | 192.168.162.166 | vs1      | 192.168.162.92 | 443  | 5        |        0 |             0 |                   0 |               0 |            0 |  
 0 |            0 |           0 |          0 |          0 |            0 |            0 |        0 |                    0 |             0 | 
 0 |            0 |               0 |                0 |                 0 |                  0 | 1             | localhost.localdomain
 | 192.168.162.166 | vs1      | 192.168.162.92 | 443  | 5        |        0 |             0 |                   0 |               0 |            0 |  
 0 |            0 |           0 |          0 |          0 |            0 |            0 |        0 |                    0 |             0 | 
 0 |            0 |               0 |                0 |                 0 |                  0 | 1             | localhost.localdomain
 | 192.168.162.166 | vs1      | 192.168.162.92 | 443  | 5        |        0 |             0 |                   0 |               0 |            0 |  
 0 |            0 |           0 |          0 |          0 |            0 |            0 |        0 |                    0 |             0 | 
 0 |            0 |               0 |                0 |                 0 |                  0 | 1             | localhost.localdomain
 | 192.168.162.166 | vs1      | 192.168.162.92 | 443  | 5        |        0 |             0 |                   0 |               0 |            0 |  
 0 |            0 |           0 |          0 |          0 |            0 |            0 |        0 |                    0 |             0 | 
 0 |            0 |               0 |                0 |                 0 |                  0 | 1             | localhost.localdomain
 | 192.168.162.166 | vs1      | 192.168.162.92 | 443  | 5        |        0 |             0 |                   0 |               0 |            0 |  
 0 |            0 |           0 |          0 |          0 |            0 |            0 |        0 |                    0 |             0 | 
 0 |            0 |               0 |                0 |                 0 |                  0 | 1             | localhost.localdomain
 | 192.168.162.166 | vs1      | 192.168.162.92 | 443  | 5        |        0 |             0 |                   0 |               0 |            0 |  
 0 |            0 |           0 |          0 |          0 |            0 |            0 |        0 |                    0 |             0 | 
 0 |            0 |               0 |                0 |                 0 |                  0 | 1             | localhost.localdomain
 | 192.168.162.166 | vs1      | 192.168.162.92 | 443  | 5        |        0 |             0 |                   0 |               0 |            0 |  
 0 |            0 |           0 |          0 |          0 |            0 |            0 |        0 |                    0 |             0 | 
 0 |            0 |               0 |                0 |                 0 |                  0 | 1             | localhost.localdomain
 | 192.168.162.166 | vs1      | 192.168.162.92 | 443  | 5        |        0 |             0 |                   0 |               0 |            0 |  
 0 |            0 |           0 |          0 |          0 |            0 |            0 |        0 |                    0 |             0 | 
 0 |            0 |               0 |                0 |                 0 |                  0 | 1             | localhost.localdomain
 | 192.168.162.166 | vs1      | 192.168.162.92 | 443  | 5        |        0 |             0 |                   0 |               0 |            0 |  
 0 |            0 |           0 |          0 |          0 |            0 |            0 |        0 |                    0 |             0 | 
 0 |            0 |               0 |                0 |                 0 |                  0 | 1             | localhost.localdomain

amp_ts=> select * from apv_llb_stats limit 10;

           time            |   agent_host    | link_index | link_name | link_gateway  | link_status | link_resp_time | link_up_time | link_down_time | link_down_count | link_down_event

 | link_bandwid_in | link_bandwid_out | link_thresh | link_hits | link_conn | link_usage |         host        

---------------------------+-----------------+------------+-----------+---------------+-------------+----------------+--------------+----------------+-----------------+----------------

-+-----------------+------------------+-------------+-----------+-----------+------------+-----------------------

 2025-08-29 17:25:20+05:30 | 192.168.162.166 |          1 | VLAN-162  | 192.168.162.1 | Up          | 0.679ms        | 10+19:05:54  | 00:00:00       |               0 | Link is Up   

 |                 |                  |             |           |           |            | localhost.localdomain

 2025-08-29 17:25:20+05:30 | 192.168.162.166 |          2 | VLAN-163  | 192.168.163.1 | Up          | 0.705ms        | 10+18:19:17  | 00:00:00       |               0 | Link is Up   

 |                 |                  |             |           |           |            | localhost.localdomain

 2025-08-29 17:25:30+05:30 | 192.168.162.166 |          1 | VLAN-162  | 192.168.162.1 | Up          | 0.679ms        | 10+19:06:04  | 00:00:00       |               0 | Link is Up   

 |                 |                  |             |           |           |            | localhost.localdomain

 2025-08-29 17:25:30+05:30 | 192.168.162.166 |          2 | VLAN-163  | 192.168.163.1 | Up          | 0.705ms        | 10+18:19:27  | 00:00:00       |               0 | Link is Up   

 |                 |                  |             |           |           |            | localhost.localdomain

 2025-08-29 17:25:40+05:30 | 192.168.162.166 |          1 | VLAN-162  | 192.168.162.1 | Up          | 0.679ms        | 10+19:06:14  | 00:00:00       |               0 | Link is Up   

 |                 |                  |             |           |           |            | localhost.localdomain

 2025-08-29 17:25:40+05:30 | 192.168.162.166 |          2 | VLAN-163  | 192.168.163.1 | Up          | 0.705ms        | 10+18:19:37  | 00:00:00       |               0 | Link is Up   

 |                 |                  |             |           |           |            | localhost.localdomain

 2025-08-29 17:25:50+05:30 | 192.168.162.166 |          1 | VLAN-162  | 192.168.162.1 | Up          | 0.679ms        | 10+19:06:24  | 00:00:00       |               0 | Link is Up   

 |                 |                  |             |           |           |            | localhost.localdomain

 2025-08-29 17:25:50+05:30 | 192.168.162.166 |          2 | VLAN-163  | 192.168.163.1 | Up          | 0.711ms        | 10+18:19:47  | 00:00:00       |               0 | Link is Up   

 |                 |                  |             |           |           |            | localhost.localdomain

 2025-08-29 17:26:00+05:30 | 192.168.162.166 |          1 | VLAN-162  | 192.168.162.1 | Up          | 0.689ms        | 10+19:06:34  | 00:00:00       |               0 | Link is Up   

 |                 |                  |             |           |           |            | localhost.localdomain

 2025-08-29 17:26:00+05:30 | 192.168.162.166 |          2 | VLAN-163  | 192.168.163.1 | Up          | 0.718ms        | 10+18:19:57  | 00:00:00       |               0 | Link is Up   

 |                 |                  |             |           |           |            | localhost.localdomain

(10 rows)
amp_ts=> 

amp_ts=> 

amp_ts=> select * from an_device_storage limit 10;

           time            |   agent_host    |     prefix      |  size   |  used   | alloc_unit |         host        

---------------------------+-----------------+-----------------+---------+---------+------------+-----------------------

 2025-08-29 17:25:20+05:30 | 192.168.162.166 | /run/user/0     |   96988 |       0 |       4096 | localhost.localdomain

 2025-08-29 17:25:20+05:30 | 192.168.162.166 | /run            |  484937 |    3257 |       4096 | localhost.localdomain

 2025-08-29 17:25:20+05:30 | 192.168.162.166 | /var            | 5710363 |  852873 |       4096 | localhost.localdomain

 2025-08-29 17:25:20+05:30 | 192.168.162.166 | /boot           |  194235 |   80519 |       1024 | localhost.localdomain

 2025-08-29 17:25:20+05:30 | 192.168.162.166 | Physical memory | 3879500 | 3701520 |       1024 | localhost.localdomain

 2025-08-29 17:25:20+05:30 | 192.168.162.166 | Memory buffers  | 3879500 |      68 |       1024 | localhost.localdomain

 2025-08-29 17:25:20+05:30 | 192.168.162.166 | Swap space      | 2097148 |  317924 |       1024 | localhost.localdomain

 2025-08-29 17:25:20+05:30 | 192.168.162.166 | /dev/shm        |  484937 |       1 |       4096 | localhost.localdomain

 2025-08-29 17:25:20+05:30 | 192.168.162.166 | Shared memory   |  484304 |  484304 |       1024 | localhost.localdomain

 2025-08-29 17:25:20+05:30 | 192.168.162.166 | /sys/fs/cgroup  |  484937 |       0 |       4096 | localhost.localdomain

(10 rows)
amp_ts=> select * from an_device_metrics limit 10;

           time            |   agent_host    | cpu_usage | mem_usage | net_mem_usage | total_openssl_conns | connections | requests |  total_in   |  total_out  |         host        

---------------------------+-----------------+-----------+-----------+---------------+---------------------+-------------+----------+-------------+-------------+-----------------------

 2025-08-29 17:25:20+05:30 | 192.168.162.166 |         9 |        57 |             3 |                   0 |           0 |        0 | 27518994722 | 22368147330 | localhost.localdomain

 2025-08-29 17:25:30+05:30 | 192.168.162.166 |        12 |        57 |             3 |                   0 |           0 |        0 | 27519054465 | 22368233917 | localhost.localdomain

 2025-08-29 17:25:40+05:30 | 192.168.162.166 |        11 |        57 |             3 |                   0 |           0 |        0 | 27519102079 | 22368320329 | localhost.localdomain

 2025-08-29 17:25:50+05:30 | 192.168.162.166 |        10 |        57 |             3 |                   0 |           0 |        0 | 27519151292 | 22368414634 | localhost.localdomain

 2025-08-29 17:26:00+05:30 | 192.168.162.166 |        11 |        57 |             3 |                   0 |           3 |        0 | 27519251537 | 22368529182 | localhost.localdomain

 2025-08-29 17:26:10+05:30 | 192.168.162.166 |        11 |        57 |             3 |                   0 |           0 |        0 | 27519313087 | 22368618256 | localhost.localdomain

 2025-08-29 17:26:20+05:30 | 192.168.162.166 |        10 |        57 |             3 |                   0 |           0 |        0 | 27519375302 | 22368704540 | localhost.localdomain

 2025-08-29 17:26:30+05:30 | 192.168.162.166 |        12 |        57 |             3 |                   0 |           0 |        0 | 27519438610 | 22368790897 | localhost.localdomain

 2025-08-29 17:26:40+05:30 | 192.168.162.166 |        10 |        57 |             3 |                   0 |           0 |        0 | 27519489012 | 22368877054 | localhost.localdomain

 2025-08-29 17:26:50+05:30 | 192.168.162.166 |         9 |        57 |             3 |                   0 |           0 |        0 | 27519552185 | 22368983295 | localhost.localdomain

(10 rows)

Issues

Description	From	Last Updated
we can remove the config to output to inlfuxdb_v2. Also, how the telegraf data is output to timescaledb!	pmurugaiyan	Sept. 1, 2025, 12:44 p.m.
What about the ASF & AG configurations? The script intended for the telegraf installation and output configurations. If this adds …	pmurugaiyan	Sept. 1, 2025, 5:06 p.m.
What will happen when there is no APV!	pmurugaiyan	Sept. 1, 2025, 5:06 p.m.

Ship it!

```
Ship It!
```

branches/amp_4_0/platform/tools/install_telegraf.sh (Diff revision 1)

Show all issues

we can remove the config to output to inlfuxdb_v2. Also, how the telegraf data is output to timescaledb!

apoorva.sn Sept. 1, 2025, 12:48 p.m.

I have created hypertables which matches the data coming from Telegraf. It refers to the apv.conf for getting data w.r.t the oid and populates the tables respectively.
This is done by Timescale only.

Diff:

Revision 2 (+357 -7)

Show changes

	branches/amp_4_0/platform/tools/install_telegraf.sh
	branches/amp_4_0/platform/tools/install_timescaledb.sh
	branches/amp_4_0/platform/tools/telegraf_snmp_timescale.sql
	branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/storage.py

Diff:

Revision 3 (+539 -78)

Show changes

	branches/amp_4_0/platform/tools/install_telegraf.sh
	branches/amp_4_0/platform/tools/install_timescaledb.sh
	branches/amp_4_0/platform/tools/telegraf_snmp_timescale.sql
	branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/storage.py

branches/amp_4_0/platform/tools/install_telegraf.sh (Diff revisions 2 - 3)

Show all issues

What about the ASF & AG configurations? The script intended for the telegraf installation and output configurations. If this adds array internals, can we split it into two scripts.

apoorva.sn Sept. 1, 2025, 5:06 p.m.

I have split the script into 2 parts
1. install_telegraf.sh : deals with only installation of telegraf
2. configure_telegraf_timescale.sh : deals with - Validates apv.conf exists and contains SNMP keys.
- Writes telegraf.conf with [agent] + PostgreSQL output (+ optional local inputs).
- Tests config and restarts Telegraf.

AG we get the data from rest api is what I know. ASF I have not worked on

apoorva.sn Sept. 3, 2025, 3:30 p.m.

For AG and ASF will update the SQL as part of next review

branches/amp_4_0/platform/tools/install_telegraf.sh (Diff revisions 2 - 3)

Show all issues

What will happen when there is no APV!

apoorva.sn Sept. 1, 2025, 5:06 p.m.

no APV.conf file or no APV device?

With no APV.conf file we get the error

[[ -z "$AGENTS_LINE"    ]] && { log "ERROR: 'agents = [...]' not found in $APV_CONF"; exit 1; }

[[ -z "$COMMUNITY_LINE" ]] && { log "ERROR: 'community = \"...\"' not found in $APV_CONF"; exit 1; }

[[ -z "$TIMEOUT_LINE"   ]] && { log "ERROR: 'timeout = \"...\"' not found in $APV_CONF"; exit 1; }

pmurugaiyan Sept. 3, 2025, 3:55 p.m.

Why errors, can we pass it on. There could deployments without APV and only ASF or AG also possible.

Summary:

-	AE-2107: POC on Time Series DB
+	AE-2115: Install TimeScale DB and populating APV data

Description:

		Problem

~		Finalizing which Time Series DB we have to consider installing for AMP
	~	adding scripts to install Timescale DB and create Hypertables for populate the DB with APV data.
-
-		Check the installation and for opensource edition.
-
-		Need to check the Migration path from Influx 1.x to TimeScale

		Solution

		Since TimeScaleDB has a gentler learning curve, provides a clear migration path, and may suit our needs without requiring a hardware refresh.
		Going with moving to TimeScaleDB for Time Series DB.

		Have come up with the script for TimeScaleDB installation and also .sql file to create hypertables for the data to get inserted successfully

Diff:

Revision 4 (+378 -121)

Show changes

	branches/amp_4_0/platform/tools/install_telegraf.sh
	branches/amp_4_0/platform/tools/install_timescaledb.sh
	branches/amp_4_0/platform/tools/telegraf_snmp_timescale.sql
	branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/storage.py

Ship it!

```
Ship It!
```

Ship it!

```
Ship It!
```

You have a pending review.

Review Board 5.0.5

AE-2115: Install TimeScale DB and populating APV data

Problem

Solution

Diff:

Diff:

Summary:

Description:

Problem

Solution

Diff:

Status: Closed (submitted)