AE-2116: Creating Hypertables for ASF and AG in Timescale DB
Review Request #1049 — Created Sept. 4, 2025 and submitted
| Information | |
|---|---|
| apoorva.sn | |
| AMP | |
| amp_4_0 | |
| AE-2116 | |
| Reviewers | |
| ngurunathan, pmurugaiyan, pradeep, shuinvy | |
Problem
Creating Hypertables for ASF and AG in Timescale DB.
Also test for data populated for the same in Timescale DB
Solution
Have updated the .sql file for creating hypertables for AG and ASF devices
Have added the telegraf config to refer the config files for APV, AG and ASF to get the data based on the data specified in config filesSome of the tables dont have data as the device is not sending any data for that. Verified that with the SNMPWalk command
[root@localhost admin]# snmpwalk -v2c -c public 192.168.85.53 1.3.6.1.4.1.7564.33.1.2.2.1
SNMPv2-SMI::enterprises.7564.33.1.2.2.1 = No Such Object available on this agent at this OID
[root@localhost admin]#
[root@localhost admin]#
[root@localhost admin]# snmpwalk -v2c -c public 192.168.85.53 1.3.6.1.4.1.7564.33.1.3.2.1
SNMPv2-SMI::enterprises.7564.33.1.3.2.1 = No Such Object available on this agent at this OID
[root@localhost admin]#
[root@localhost admin]#
[root@localhost admin]# snmpwalk -v2c -c public 192.168.85.53 1.3.6.1.4.1.7564.20.2.4.1
SNMPv2-SMI::enterprises.7564.20.2.4.1 = No Such Object available on this agent at this OID
[root@localhost admin]#
[root@localhost admin]#
[root@localhost admin]# snmpwalk -v2c -c public 192.168.85.53 1.3.6.1.4.1.7564.22.8.1
SNMPv2-SMI::enterprises.7564.22.8.1 = No Such Object available on this agent at this OID
AG Tables populated
amp_ts=> \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+-------------
public | ag_device_metrics | table | amp_ts_user
public | ag_virtual_site_stats | table | amp_ts_user
public | ag_vpn_stats | table | amp_ts_user
public | ag_web_stats | table | amp_ts_user
public | an_device_metrics | table | amp_ts_user
public | an_device_performance | table | amp_ts_user
public | an_device_storage | table | amp_ts_user
public | apv_llb_stats | table | amp_ts_user
public | apv_real_stats | table | amp_ts_user
public | apv_virtual_stats | table | amp_ts_user
public | cpu | table | amp_ts_user
public | disk | table | amp_ts_user
public | mem | table | amp_ts_user
public | system | table | amp_ts_user
(14 rows)amp_ts=>
amp_ts=>
amp_ts=> select * from ag_vpn_stats limit 10;
time | agent_host | id | bytes_in | bytes_out | client_app_bytes_in | client_app_bytes_out | tunnels_est | tunnels_open | tunnels_rejected | tunnels_terminated
| unauth_packets_in
---------------------+----------------+--------+----------+-----------+---------------------+----------------------+-------------+--------------+------------------+--------------------
+-------------------
2025-09-03 18:55:30 | 192.168.162.88 | vsite1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0
2025-09-03 18:55:30 | 192.168.162.88 | vsite2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0
2025-09-03 18:55:40 | 192.168.162.88 | vsite2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0
2025-09-03 18:55:40 | 192.168.162.88 | vsite1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0
2025-09-03 18:55:50 | 192.168.162.88 | vsite1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0
2025-09-03 18:55:50 | 192.168.162.88 | vsite2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0
2025-09-03 18:56:00 | 192.168.162.88 | vsite1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0
2025-09-03 18:56:00 | 192.168.162.88 | vsite2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0
2025-09-03 18:56:10 | 192.168.162.88 | vsite1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0
2025-09-03 18:56:10 | 192.168.162.88 | vsite2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0
(10 rows)amp_ts=> select * from ag_device_metrics limit 10;
time | agent_host | connections | cpu_usage | net_mem_usage | requests | total_in | total_openssl_conns | total_out
---------------------+----------------+-------------+-----------+---------------+----------+----------+---------------------+-----------
2025-09-03 18:55:30 | 192.168.162.88 | 0 | 6 | 5 | 0 | 20398346 | 1 | 30939231
2025-09-03 18:55:40 | 192.168.162.88 | 0 | 5 | 5 | 0 | 20415714 | 1 | 30982108
2025-09-03 18:55:50 | 192.168.162.88 | 0 | 9 | 5 | 0 | 20441737 | 1 | 31045237
2025-09-03 18:56:00 | 192.168.162.88 | 0 | 6 | 5 | 0 | 20453679 | 1 | 31076875
2025-09-03 18:56:10 | 192.168.162.88 | 0 | 6 | 5 | 0 | 20472757 | 1 | 31112629
2025-09-03 18:56:20 | 192.168.162.88 | 0 | 10 | 5 | 0 | 20492898 | 1 | 31155904
(6 rows)amp_ts=> select * from ag_virtual_site_stats limit 10;
time | agent_host | id | ip | active_sessions | client_bytes_in | client_bytes_out | error_login | failure_login | locked_login | rejected_log
in | server_bytes_in | server_bytes_out | success_login | success_logout
---------------------+----------------+--------+----------------------+-----------------+-----------------+------------------+-------------+---------------+--------------+-------------
---+-----------------+------------------+---------------+----------------
2025-09-03 18:55:30 | 192.168.162.88 | vsite1 | 192.168.162.12:443, | 0 | 111672 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
2025-09-03 18:55:30 | 192.168.162.88 | vsite2 | 192.168.162.14:443, | 0 | 70092 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
2025-09-03 18:55:40 | 192.168.162.88 | vsite1 | 192.168.162.12:443, | 0 | 112266 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
2025-09-03 18:55:40 | 192.168.162.88 | vsite2 | 192.168.162.14:443, | 0 | 70092 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
2025-09-03 18:55:50 | 192.168.162.88 | vsite2 | 192.168.162.14:443, | 0 | 70092 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
2025-09-03 18:55:50 | 192.168.162.88 | vsite1 | 192.168.162.12:443, | 0 | 112266 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
2025-09-03 18:56:00 | 192.168.162.88 | vsite2 | 192.168.162.14:443, | 0 | 70092 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
2025-09-03 18:56:00 | 192.168.162.88 | vsite1 | 192.168.162.12:443, | 0 | 112266 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
2025-09-03 18:56:10 | 192.168.162.88 | vsite2 | 192.168.162.14:443, | 0 | 70092 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
2025-09-03 18:56:10 | 192.168.162.88 | vsite1 | 192.168.162.12:443, | 0 | 112266 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0
(10 rows)amp_ts=> select * from ag_web_stats limit 10;
time | agent_host | id | authorized_req | client_bytes_in | client_bytes_out | server_bytes_in | server_bytes_out | unauthorized_req
---------------------+----------------+--------+----------------+-----------------+------------------+-----------------+------------------+------------------
2025-09-03 18:55:30 | 192.168.162.88 | vsite1 | 0 | 111672 | 0 | 0 | 0 | 0
2025-09-03 18:55:30 | 192.168.162.88 | vsite2 | 0 | 70092 | 0 | 0 | 0 | 0
2025-09-03 18:55:40 | 192.168.162.88 | vsite2 | 0 | 70092 | 0 | 0 | 0 | 0
2025-09-03 18:55:40 | 192.168.162.88 | vsite1 | 0 | 112266 | 0 | 0 | 0 | 0
2025-09-03 18:55:50 | 192.168.162.88 | vsite1 | 0 | 112266 | 0 | 0 | 0 | 0
2025-09-03 18:55:50 | 192.168.162.88 | vsite2 | 0 | 70092 | 0 | 0 | 0 | 0
2025-09-03 18:56:00 | 192.168.162.88 | vsite2 | 0 | 70092 | 0 | 0 | 0 | 0
2025-09-03 18:56:00 | 192.168.162.88 | vsite1 | 0 | 112266 | 0 | 0 | 0 | 0
2025-09-03 18:56:10 | 192.168.162.88 | vsite1 | 0 | 112266 | 0 | 0 | 0 | 0
2025-09-03 18:56:10 | 192.168.162.88 | vsite2 | 0 | 70092 | 0 | 0 | 0 | 0
(10 rows)ASF Tables populated
amp_ts=> \dt
List of relations
Schema | Name | Type | Owner
--------+----------------------------+-------+-------------
public | ag_device_metrics | table | amp_ts_user
public | ag_virtual_site_stats | table | amp_ts_user
public | ag_vpn_stats | table | amp_ts_user
public | ag_web_stats | table | amp_ts_user
public | an_device_metrics | table | amp_ts_user
public | an_device_performance | table | amp_ts_user
public | an_device_storage | table | amp_ts_user
public | apv_llb_stats | table | amp_ts_user
public | apv_real_stats | table | amp_ts_user
public | apv_virtual_stats | table | amp_ts_user
public | asf_device_metrics | table | amp_ts_user
public | asf_device_storage | table | amp_ts_user
public | asf_http_service | table | amp_ts_user
public | asf_https_service | table | amp_ts_user
public | asf_performance_statistics | table | amp_ts_user
public | asf_ssl_host_statistics | table | amp_ts_user
public | asf_ssl_statistics | table | amp_ts_user
public | asf_syslog_history | table | amp_ts_user
public | asf_vip_group_statistics | table | amp_ts_user
public | asf_vip_statistics | table | amp_ts_user
public | cpu | table | amp_ts_user
public | disk | table | amp_ts_user
public | mem | table | amp_ts_user
public | snmp | table | amp_ts_user
public | system | table | amp_ts_user
(25 rows)amp_ts=> select * from asf_device_metrics limit 5;
time | agent_host | cpu_usage | mem_usage | net_mem_usage | total_openssl_conns | connections | requests | total_in | total_out
---------------------------+---------------+-----------+-----------+---------------+---------------------+-------------+----------+-------------+--------------
2025-09-04 22:47:10+05:30 | 192.168.85.53 | 17 | 45 | 1 | 0 | 0 | 0 | 39279165923 | 152046323950
2025-09-04 22:47:20+05:30 | 192.168.85.53 | 5 | 45 | 1 | 0 | 0 | 0 | 39279275841 | 152047234020
2025-09-04 22:47:30+05:30 | 192.168.85.53 | 5 | 45 | 1 | 0 | 0 | 0 | 39279328574 | 152047722883
2025-09-04 22:47:40+05:30 | 192.168.85.53 | 7 | 45 | 1 | 0 | 0 | 0 | 39279379499 | 152048211843
2025-09-04 22:47:50+05:30 | 192.168.85.53 | 6 | 45 | 1 | 0 | 0 | 0 | 39279430640 | 152048700803
(5 rows)amp_ts=>
amp_ts=> select * from asf_device_performance limit 5;
ERROR: relation "asf_device_performance" does not exist
LINE 1: select * from asf_device_performance limit 5;
^
amp_ts=> select * from asf_device_storage limit 5;
time | agent_host | prefix | size | used | alloc_unit
---------------------------+---------------+------------------+---------+---------+------------
2025-09-04 22:47:10+05:30 | 192.168.85.53 | Virtual memory | 5918464 | 3641780 | 1024
2025-09-04 22:47:10+05:30 | 192.168.85.53 | Memory buffers | 3821316 | 72 | 1024
2025-09-04 22:47:10+05:30 | 192.168.85.53 | Cached memory | 583760 | 583760 | 1024
2025-09-04 22:47:10+05:30 | 192.168.85.53 | Swap space | 2097148 | 142016 | 1024
2025-09-04 22:47:10+05:30 | 192.168.85.53 | Available memory | 597388 | 0 | 1024
(5 rows)amp_ts=> select * from asf_http_service limit 5;
time | agent_host | http_service_index | http_service_id | http_service_cc | http_service_cps | http_service_rps_get | http_service_rps_post | http_service_rps_head | http_service_rps
_put | http_service_rps_delete | http_service_rps_total | http_service_anomaly_method | http_service_anomaly_requestline | http_service_anomaly_host | http_service_anomaly_connection |
http_service_anomaly_contentlength | http_service_anomaly_range | http_service_traffic_inbound_in_byte | http_service_traffic_inbound_in_packet | http_service_traffic_inbound_out_byte
| http_service_traffic_inbound_out_packet | http_service_traffic_outbound_in_byte | http_service_traffic_outbound_in_packet | http_service_traffic_outbound_out_byte | http_service_tra
ffic_outbound_out_packet | http_service_drop_total | http_service_drop_type_source | http_service_drop_type_man_bl | http_service_drop_type_dyn_bl | http_service_drop_type_acl | http_s
ervice_drop_type_ddos | http_service_drop_type_waf | http_service_drop_type_filter | http_service_drop_type_anomaly | http_service_drop_type_parse_fail | http_service_drop_type_resourc
e | http_service_drop_type_profile
------+------------+--------------------+-----------------+-----------------+------------------+----------------------+-----------------------+-----------------------+-----------------
-----+-------------------------+------------------------+-----------------------------+----------------------------------+---------------------------+---------------------------------+
------------------------------------+----------------------------+--------------------------------------+----------------------------------------+--------------------------------------
-+-----------------------------------------+---------------------------------------+-----------------------------------------+----------------------------------------+-----------------
-------------------------+-------------------------+-------------------------------+-------------------------------+-------------------------------+----------------------------+-------
----------------------+----------------------------+-------------------------------+--------------------------------+-----------------------------------+-------------------------------
--+--------------------------------
(0 rows)amp_ts=>
amp_ts=>
amp_ts=> select * from asf_https_service limit 5;
time | agent_host | https_service_index | https_service_id | https_service_cc | https_service_cps | https_service_rps_get | https_service_rps_post | https_service_rps_head | https_ser
vice_rps_put | https_service_rps_delete | https_service_rps_total | https_service_anomaly_method | https_service_anomaly_requestline | https_service_anomaly_host | https_service_anomal
y_connection | https_service_anomaly_contentlength | https_service_anomaly_range | https_service_traffic_inbound_in_byte | https_service_traffic_inbound_in_packets | https_service_traf
fic_inbound_out_byte | https_service_traffic_inbound_out_packets | https_service_traffic_outbound_in_byte | https_service_traffic_outbound_in_packets | https_service_traffic_outbound_o
ut_byte | https_service_traffic_outbound_out_packets | https_service_ssl_traffic_inbound_in_byte | https_service_ssl_traffic_inbound_in_packets | https_service_ssl_traffic_inbound_out_
byte | https_service_ssl_traffic_inbound_out_packets | https_service_ssl_traffic_outbound_in_byte | https_service_ssl_traffic_outbound_in_packets | https_service_ssl_traffic_outbound_o
ut_byte | https_service_ssl_traffic_outbound_out_packets | https_service_http_drop_total | https_service_http_drop_type_source | https_service_http_drop_type_man_bl | https_service_htt
p_drop_type_dyn_bl | https_service_http_drop_type_acl | https_service_http_drop_type_ddos | https_service_http_drop_type_waf | https_service_http_drop_type_filter | https_service_http_
drop_type_anomaly | https_service_http_drop_type_parse_fail | https_service_http_drop_type_resource | https_service_http_drop_type_profile
------+------------+---------------------+------------------+------------------+-------------------+-----------------------+------------------------+------------------------+----------
-------------+--------------------------+-------------------------+------------------------------+-----------------------------------+----------------------------+---------------------
-------------+-------------------------------------+-----------------------------+---------------------------------------+------------------------------------------+-------------------
---------------------+-------------------------------------------+----------------------------------------+-------------------------------------------+---------------------------------
--------+--------------------------------------------+-------------------------------------------+----------------------------------------------+---------------------------------------
-----+-----------------------------------------------+--------------------------------------------+-----------------------------------------------+-------------------------------------
--------+------------------------------------------------+-------------------------------+-------------------------------------+-------------------------------------+------------------
-------------------+----------------------------------+-----------------------------------+----------------------------------+-------------------------------------+--------------------
------------------+-----------------------------------------+---------------------------------------+--------------------------------------
(0 rows)amp_ts=> select * from asf_performance_statistics limit 5;
time | agent_host | cpu_utilization | connections_per_sec | requests_per_sec | ssl_core_utilization | ssl_ae_core_utilization | ssl_se_core_utilization
---------------------------+---------------+-----------------+---------------------+------------------+----------------------+-------------------------+-------------------------
2025-09-04 22:47:11+05:30 | 192.168.85.53 | 49 | 0 | 0 | 0 | 0 | 0
2025-09-04 22:47:21+05:30 | 192.168.85.53 | 20 | 0 | 0 | 0 | 0 | 0
2025-09-04 22:47:31+05:30 | 192.168.85.53 | 19 | 0 | 0 | 0 | 0 | 0
2025-09-04 22:47:41+05:30 | 192.168.85.53 | 18 | 0 | 0 | 0 | 0 | 0
2025-09-04 22:47:51+05:30 | 192.168.85.53 | 18 | 0 | 0 | 0 | 0 | 0
(5 rows)amp_ts=>
amp_ts=> select * from asf_ssl_host_statistics limit 5;
time | agent_host | ssl_index | vhost_name | open_ssl_conns | accepted_conns | requested_conns | resumed_sess | resumable_sess | miss_sess | conns_per_sec
------+------------+-----------+------------+----------------+----------------+-----------------+--------------+----------------+-----------+---------------
(0 rows)amp_ts=>
amp_ts=>
amp_ts=> select * from asf_ssl_statistics limit 5;
time | agent_host | total_openssl_conns | total_accepted_conns | total_requested_conns
---------------------------+---------------+---------------------+----------------------+-----------------------
2025-09-04 22:47:10+05:30 | 192.168.85.53 | 0 | 0 | 0
2025-09-04 22:47:20+05:30 | 192.168.85.53 | 0 | 0 | 0
2025-09-04 22:47:30+05:30 | 192.168.85.53 | 0 | 0 | 0
2025-09-04 22:47:40+05:30 | 192.168.85.53 | 0 | 0 | 0
2025-09-04 22:47:50+05:30 | 192.168.85.53 | 0 | 0 | 0
(5 rows)amp_ts=>
amp_ts=> select * from asf_syslog_history limit 5;
time | agent_host | idx | severity | msg_text---------------------------+---------------+------+----------+--------------------------------------------------------------------------------------------------------------------------
2025-09-04 22:47:11+05:30 | 192.168.85.53 | 713 | 6 | <134>1 2025-08-15T17:38:45Z Peri - - 100013004 - CLI: User "webui_agent" from "localhost" executed cmd "http verifycode r
efresh"
2025-09-04 22:47:11+05:30 | 192.168.85.53 | 1165 | 6 | <134>1 2025-09-03T13:38:46Z Peri - - 100013004 - CLI: User "webui_agent" from "localhost" executed cmd "http verifycode r
efresh"
2025-09-04 22:47:11+05:30 | 192.168.85.53 | 1456 | 6 | <134>1 2025-09-04T14:37:07Z Peri - - 100028015 - user array CLI level change: LOG-->USER by WEBUI from 192.168.172.5:0.
2025-09-04 22:47:11+05:30 | 192.168.85.53 | 723 | 6 | <134>1 2025-08-16T03:38:45Z Peri - - 100013004 - CLI: User "webui_agent" from "localhost" executed cmd "http verifycode r
efresh"
2025-09-04 22:47:11+05:30 | 192.168.85.53 | 538 | 6 | <134>1 2025-08-08T14:38:44Z Peri - - 100013004 - CLI: User "webui_agent" from "localhost" executed cmd "http verifycode r
efresh"
(5 rows)amp_ts=>
amp_ts=> select * from asf_vip_group_statistics limit 5;
time | agent_host | vip_status | host_name | current_tme | total_ip_pkts_in | total_ip_pkts_out | total_ip_bytes_in | total_ip_bytes_out
---------------------------+---------------+------------+-----------+----------------+------------------+-------------------+-------------------+--------------------
2025-09-04 22:47:10+05:30 | 192.168.85.53 | 0 | Peri | 09/04/25 17:17+| 0 | 0 | 0 | 0
| | | | | | | |
2025-09-04 22:47:20+05:30 | 192.168.85.53 | 0 | Peri | 09/04/25 17:17+| 0 | 0 | 0 | 0
| | | | | | | |
2025-09-04 22:47:30+05:30 | 192.168.85.53 | 0 | Peri | 09/04/25 17:17+| 0 | 0 | 0 | 0
| | | | | | | |
2025-09-04 22:47:40+05:30 | 192.168.85.53 | 0 | Peri | 09/04/25 17:17+| 0 | 0 | 0 | 0
| | | | | | | |
2025-09-04 22:47:50+05:30 | 192.168.85.53 | 0 | Peri | 09/04/25 17:18+| 0 | 0 | 0 | 0
| | | | | | | |
(5 rows)amp_ts=>
amp_ts=> select * from asf_vip_statistics limit 5;
time | agent_host | ip_index | ip_address | ip_pkts_in | ip_bytes_in | ip_pkts_out | ip_bytes_out | start_time | ip_addr_type
------+------------+----------+------------+------------+-------------+-------------+--------------+------------+--------------
(0 rows)Some of the tables dont have data as the device is not sending any data for that. Verified that with the SNMPWalk command
[root@localhost admin]# snmpwalk -v2c -c public 192.168.85.53 1.3.6.1.4.1.7564.33.1.2.2.1
SNMPv2-SMI::enterprises.7564.33.1.2.2.1 = No Such Object available on this agent at this OID
[root@localhost admin]#
[root@localhost admin]#
[root@localhost admin]# snmpwalk -v2c -c public 192.168.85.53 1.3.6.1.4.1.7564.33.1.3.2.1
SNMPv2-SMI::enterprises.7564.33.1.3.2.1 = No Such Object available on this agent at this OID
[root@localhost admin]#
[root@localhost admin]#
[root@localhost admin]# snmpwalk -v2c -c public 192.168.85.53 1.3.6.1.4.1.7564.20.2.4.1
SNMPv2-SMI::enterprises.7564.20.2.4.1 = No Such Object available on this agent at this OID
[root@localhost admin]#
[root@localhost admin]#
[root@localhost admin]# snmpwalk -v2c -c public 192.168.85.53 1.3.6.1.4.1.7564.22.8.1
SNMPv2-SMI::enterprises.7564.22.8.1 = No Such Object available on this agent at this OID
