fix(schema):1.Schema统一术语(TSG-7579) 2.更新ClickHouse、Engine测试SQL集文件内容 3.修改公共schema抽取代码

18 files changed, 143 insertions, 164 deletions

diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/active_defence_event_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/active_defence_event.json
index 19205b5e..44aeb53c 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/active_defence_event_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/active_defence_event.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "active_defence_event_log",
+  "name": "active_defence_event",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/ck-queries-template.sql b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/ck-queries-template.sql
index 9a8e18cb..fe0f7fa9 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/ck-queries-template.sql
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/ck-queries-template.sql
@@ -1,122 +1,122 @@
 --Q01.Count(1)
-select count(1) from connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end)
+select count(1) from session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end)
 --Q02.All Fields Query (default)
-SELECT * FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) LIMIT 30
+SELECT * FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) LIMIT 30
 --Q03.All Fields Query order by Time desc
-SELECT * FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30
+SELECT * FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30
 --Q04.All Fields Query order by Time asc
-SELECT * FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time asc LIMIT 30
+SELECT * FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time asc LIMIT 30
 --Q05.All Fields Query by Filter
-SELECT * FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @common_filter ORDER BY common_recv_time DESC LIMIT 30
+SELECT * FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @common_filter ORDER BY common_recv_time DESC LIMIT 30
 --Q06.Default Fields Query by Filter
-SELECT toDateTime(common_recv_time) AS common_recv_time , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @common_filter ORDER BY common_recv_time DESC LIMIT 30
+SELECT toDateTime(common_recv_time) AS common_recv_time , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @common_filter ORDER BY common_recv_time DESC LIMIT 30
 --Q07.All Fields Query (sub query by time)
-SELECT * FROM connection_record_log AS connection_record_log WHERE toDateTime(common_recv_time) IN ( SELECT toDateTime(common_recv_time) FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30 ) AND common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30
+SELECT * FROM session_record AS session_record WHERE toDateTime(common_recv_time) IN ( SELECT toDateTime(common_recv_time) FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30 ) AND common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30
 --Q08.All Fields Query (sub query by log id)
-SELECT  *  FROM connection_record_log AS connection_record_log WHERE common_log_id IN ( SELECT common_log_id FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30 ) AND common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30
+SELECT  *  FROM session_record AS session_record WHERE common_log_id IN ( SELECT common_log_id FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30 ) AND common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30
 --Q09.Default Field Query (sub query by time)
-SELECT toDateTime(common_recv_time) AS common_recv_time_str , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM connection_record_log AS connection_record_log WHERE toDateTime(common_recv_time) IN ( SELECT toDateTime(common_recv_time) FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30
+SELECT toDateTime(common_recv_time) AS common_recv_time_str , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM session_record AS session_record WHERE toDateTime(common_recv_time) IN ( SELECT toDateTime(common_recv_time) FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 30
 --Q10.Default Field Query (sub query by log id)
-SELECT toDateTime(common_recv_time) AS common_recv_time_str , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM connection_record_log AS connection_record_log WHERE common_log_id IN ( select common_log_id FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end)) ORDER BY common_recv_time DESC LIMIT 30
+SELECT toDateTime(common_recv_time) AS common_recv_time_str , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM session_record AS session_record WHERE common_log_id IN ( select common_log_id FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end)) ORDER BY common_recv_time DESC LIMIT 30
 --Q11.Default Field Query by Server IP (sub query by log id with Index Table)
-SELECT toDateTime(common_recv_time) AS common_recv_time_str , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM connection_record_log AS connection_record_log WHERE common_log_id IN ( Select common_log_id FROM connection_record_log_common_server_ip AS connection_record_log_common_server_ip WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time DESC LIMIT 30
+SELECT toDateTime(common_recv_time) AS common_recv_time_str , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM session_record AS session_record WHERE common_log_id IN ( Select common_log_id FROM session_record_common_server_ip AS session_record_common_server_ip WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time DESC LIMIT 30
 --Q12.Default Field Query by Client IP (sub query by log id with Index Table)
-SELECT toDateTime(common_recv_time) AS common_recv_time_str , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM connection_record_log AS connection_record_log WHERE common_log_id IN ( Select common_log_id FROM connection_record_log_common_client_ip AS connection_record_log_common_client_ip WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time DESC LIMIT 30
+SELECT toDateTime(common_recv_time) AS common_recv_time_str , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM session_record AS session_record WHERE common_log_id IN ( Select common_log_id FROM session_record_common_client_ip AS session_record_common_client_ip WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time DESC LIMIT 30
 --Q13.Default Field Query by Domain (sub query by log id with Index Table)
-SELECT toDateTime(common_recv_time) AS common_recv_time_str , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM connection_record_log AS connection_record_log WHERE common_log_id IN ( Select common_log_id FROM connection_record_log_http_domain AS connection_record_log_http_domain WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time DESC LIMIT 30
+SELECT toDateTime(common_recv_time) AS common_recv_time_str , common_log_id , common_client_ip , common_client_port , common_server_ip , common_server_port FROM session_record AS session_record WHERE common_log_id IN ( Select common_log_id FROM session_record_http_domain AS session_record_http_domain WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time DESC LIMIT 30
 --Q14.All Fields Query by Client IP (sub query by log id with index Table)
-SELECT * FROM connection_record_log AS connection_record_log WHERE common_log_id IN ( SELECT common_log_id FROM connection_record_log_common_client_ip AS connection_record_log_common_client_ip WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time desc LIMIT 30
+SELECT * FROM session_record AS session_record WHERE common_log_id IN ( SELECT common_log_id FROM session_record_common_client_ip AS session_record_common_client_ip WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY toDateTime(common_recv_time) DESC LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time desc LIMIT 30
 --Q15.All Fields Query by Server IP(sub query by log id with index Table)
-SELECT * FROM connection_record_log AS connection_record_log WHERE common_log_id IN ( SELECT common_log_id FROM connection_record_log_common_server_ip AS connection_record_log_common_server_ip WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY common_recv_time LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time desc LIMIT 30
+SELECT * FROM session_record AS session_record WHERE common_log_id IN ( SELECT common_log_id FROM session_record_common_server_ip AS session_record_common_server_ip WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY common_recv_time LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time desc LIMIT 30
 --Q16.All Fields Query by Domain(sub query by log id with index Table)
-SELECT * FROM connection_record_log AS connection_record_log WHERE common_log_id IN ( SELECT common_log_id FROM connection_record_log_http_domain AS connection_record_log_http_domain WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY common_recv_time LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time desc LIMIT 30
+SELECT * FROM session_record AS session_record WHERE common_log_id IN ( SELECT common_log_id FROM session_record_http_domain AS session_record_http_domain WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ORDER BY common_recv_time LIMIT 30 ) AND ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) AND @index_filter ) ORDER BY common_recv_time desc LIMIT 30
 --Q17.Session Logs Sent to Database Trend(Time Grain 5 minute)
-SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", count(common_log_id) AS "logs" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ) ) GROUP BY "Receive Time" LIMIT 10000
+SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", count(common_log_id) AS "logs" FROM session_record AS session_record WHERE ( ( common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ) ) GROUP BY "Receive Time" LIMIT 10000
 --Q18.Traffic Bandwidth Trend(Time Grain 30 second)
-SELECT toDateTime(toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 30 SECOND)))) AS stat_time, sum(common_c2s_byte_num) AS bytes_sent, sum(common_s2c_byte_num) AS bytes_received, sum(common_c2s_byte_num + common_s2c_byte_num) AS bytes, sum(common_c2s_pkt_num + common_s2c_pkt_num) AS packets, sum(common_sessions) AS sessions FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) GROUP BY stat_time ORDER BY stat_time ASC LIMIT 10000
+SELECT toDateTime(toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 30 SECOND)))) AS stat_time, sum(common_c2s_byte_num) AS bytes_sent, sum(common_s2c_byte_num) AS bytes_received, sum(common_c2s_byte_num + common_s2c_byte_num) AS bytes, sum(common_c2s_pkt_num + common_s2c_pkt_num) AS packets, sum(common_sessions) AS sessions FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) GROUP BY stat_time ORDER BY stat_time ASC LIMIT 10000
 --Q19.Log Tend by Type (Time Grain 5 minute)
-SELECT toDateTime(toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE)))) AS stat_time, common_schema_type AS type, sum(common_sessions) AS sessions, sum(common_c2s_byte_num + common_s2c_byte_num) AS bytes, sum(common_c2s_pkt_num + common_s2c_pkt_num) AS packets FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) GROUP BY stat_time, common_schema_type ORDER BY stat_time ASC LIMIT 10000
+SELECT toDateTime(toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE)))) AS stat_time, common_schema_type AS type, sum(common_sessions) AS sessions, sum(common_c2s_byte_num + common_s2c_byte_num) AS bytes, sum(common_c2s_pkt_num + common_s2c_pkt_num) AS packets FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) GROUP BY stat_time, common_schema_type ORDER BY stat_time ASC LIMIT 10000
 --Q20.Traffic Metrics Analytic
-SELECT round(sum(common_s2c_byte_num) * 8 / 300,2) AS trafficInBits, round(sum(common_c2s_byte_num) * 8 / 300,2) AS trafficOutBits, round(sum(common_s2c_byte_num + common_c2s_byte_num) * 8 / 300,2) AS trafficTotalBits, round(sum(common_s2c_pkt_num) / 300,2) AS trafficInPackets, round(sum(common_c2s_pkt_num) / 300,2) AS trafficOutPackets, round(sum(common_s2c_pkt_num + common_c2s_pkt_num) / 300,2) AS trafficTotalPackets, round(sum(common_sessions) / 300,2) AS sessions FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end)
+SELECT round(sum(common_s2c_byte_num) * 8 / 300,2) AS trafficInBits, round(sum(common_c2s_byte_num) * 8 / 300,2) AS trafficOutBits, round(sum(common_s2c_byte_num + common_c2s_byte_num) * 8 / 300,2) AS trafficTotalBits, round(sum(common_s2c_pkt_num) / 300,2) AS trafficInPackets, round(sum(common_c2s_pkt_num) / 300,2) AS trafficOutPackets, round(sum(common_s2c_pkt_num + common_c2s_pkt_num) / 300,2) AS trafficTotalPackets, round(sum(common_sessions) / 300,2) AS sessions FROM session_record AS session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end)
 --Q21.Traffic Endpoints Metrics Trend(Time Grain 5 minute)
-SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", uniq(common_internal_ip) AS "Unique Internal IP", uniq(common_external_ip) AS "Unique External IP", uniq(common_subscriber_id) AS "Unique Subscriber ID", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "Bytes", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Receive Time" LIMIT 10000
+SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", uniq(common_internal_ip) AS "Unique Internal IP", uniq(common_external_ip) AS "Unique External IP", uniq(common_subscriber_id) AS "Unique Subscriber ID", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "Bytes", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Receive Time" LIMIT 10000
 --Q22.Endpoint Unique Num by L4 Protocol
-SELECT 'all' AS type, uniq(common_client_ip) AS client_ips, uniq(common_internal_ip) AS internal_ips, uniq(common_server_ip) AS server_ips, uniq(common_external_ip) AS external_ips, uniq(common_subscriber_id) as subscriber_ids FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) UNION ALL SELECT 'tcp' AS type, uniq(common_client_ip) AS client_ips, uniq(common_internal_ip) AS internal_ips, uniq(common_server_ip) AS server_ips, uniq(common_external_ip) AS external_ips, uniq(common_subscriber_id) as subscriber_ids FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND common_l4_protocol IN ( 'IPv4_TCP', 'IPv6_TCP' ) UNION ALL SELECT 'UDP' AS type, uniq(common_client_ip) AS client_ips, uniq(common_internal_ip) AS internal_ips, uniq(common_server_ip) AS server_ips, uniq(common_external_ip) AS external_ips, uniq(common_subscriber_id) as subscriber_ids FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND common_l4_protocol IN ( 'IPv4_UDP', 'IPv6_UDP' )
+SELECT 'all' AS type, uniq(common_client_ip) AS client_ips, uniq(common_internal_ip) AS internal_ips, uniq(common_server_ip) AS server_ips, uniq(common_external_ip) AS external_ips, uniq(common_subscriber_id) as subscriber_ids FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) UNION ALL SELECT 'tcp' AS type, uniq(common_client_ip) AS client_ips, uniq(common_internal_ip) AS internal_ips, uniq(common_server_ip) AS server_ips, uniq(common_external_ip) AS external_ips, uniq(common_subscriber_id) as subscriber_ids FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND common_l4_protocol IN ( 'IPv4_TCP', 'IPv6_TCP' ) UNION ALL SELECT 'UDP' AS type, uniq(common_client_ip) AS client_ips, uniq(common_internal_ip) AS internal_ips, uniq(common_server_ip) AS server_ips, uniq(common_external_ip) AS external_ips, uniq(common_subscriber_id) as subscriber_ids FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND common_l4_protocol IN ( 'IPv4_UDP', 'IPv6_UDP' )
 --Q23.One-sided Connection Trend(Time Grain 5 minute)
-SELECT toDateTime(toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE)))) AS stat_time, (CASE WHEN common_stream_dir = 1 THEN 'c2s' WHEN common_stream_dir = 2 THEN 's2c' WHEN common_stream_dir = 3 THEN 'double' ELSE 'None' END) AS type, sum(common_sessions) AS sessions FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) GROUP BY stat_time, common_stream_dir ORDER BY stat_time ASC LIMIT 10000
+SELECT toDateTime(toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE)))) AS stat_time, (CASE WHEN common_stream_dir = 1 THEN 'c2s' WHEN common_stream_dir = 2 THEN 's2c' WHEN common_stream_dir = 3 THEN 'double' ELSE 'None' END) AS type, sum(common_sessions) AS sessions FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) GROUP BY stat_time, common_stream_dir ORDER BY stat_time ASC LIMIT 10000
 --Q24. Estimated One-sided Sessions with Bandwidth
-SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", sum(common_sessions) AS "sessions", sum(if(common_stream_dir <> 3, common_sessions, 0)) AS "one_side_sessions", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "Bytes", round(one_side_sessions / sessions, 2) AS one_side_percent FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Receive Time" LIMIT 10000
+SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", sum(common_sessions) AS "sessions", sum(if(common_stream_dir <> 3, common_sessions, 0)) AS "one_side_sessions", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "Bytes", round(one_side_sessions / sessions, 2) AS one_side_percent FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Receive Time" LIMIT 10000
 --Q25.Estimated TCP Sequence Gap Loss
-SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", sum(common_c2s_byte_num + common_s2c_byte_num) AS "bytes", sum(common_c2s_tcp_lostlen + common_s2c_tcp_lostlen) AS "gap_loss_bytes", round(gap_loss_bytes / bytes, 2) AS gap_loss_percent FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( common_l4_protocol IN ( 'IPv4_TCP', 'IPv6_TCP' ) ) GROUP BY "Receive Time" LIMIT 10000
+SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", sum(common_c2s_byte_num + common_s2c_byte_num) AS "bytes", sum(common_c2s_tcp_lostlen + common_s2c_tcp_lostlen) AS "gap_loss_bytes", round(gap_loss_bytes / bytes, 2) AS gap_loss_percent FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( common_l4_protocol IN ( 'IPv4_TCP', 'IPv6_TCP' ) ) GROUP BY "Receive Time" LIMIT 10000
 --Q26.Top30 Server IP by Bytes
-SELECT "server_ip" AS "server_ip" , SUM(coalesce("bytes",0)) AS "bytes" , SUM(coalesce("bytes_sent",0)) AS "Sent" , SUM(coalesce("bytes_received",0)) AS "Received" , SUM(coalesce("sessions",0)) AS "sessions" FROM ( SELECT SUM(coalesce(common_c2s_byte_num,0)) AS "bytes_sent" , SUM(coalesce(common_s2c_byte_num,0)) AS "bytes_received" , SUM(common_c2s_byte_num+common_s2c_byte_num) AS "bytes" , SUM(coalesce(common_sessions,0)) AS "sessions" , common_server_ip AS "server_ip" FROM connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty( common_server_ip) ) GROUP BY "server_ip" ORDER BY "bytes" desc ) GROUP BY "server_ip" ORDER BY "bytes" desc LIMIT 30
+SELECT "server_ip" AS "server_ip" , SUM(coalesce("bytes",0)) AS "bytes" , SUM(coalesce("bytes_sent",0)) AS "Sent" , SUM(coalesce("bytes_received",0)) AS "Received" , SUM(coalesce("sessions",0)) AS "sessions" FROM ( SELECT SUM(coalesce(common_c2s_byte_num,0)) AS "bytes_sent" , SUM(coalesce(common_s2c_byte_num,0)) AS "bytes_received" , SUM(common_c2s_byte_num+common_s2c_byte_num) AS "bytes" , SUM(coalesce(common_sessions,0)) AS "sessions" , common_server_ip AS "server_ip" FROM session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty( common_server_ip) ) GROUP BY "server_ip" ORDER BY "bytes" desc ) GROUP BY "server_ip" ORDER BY "bytes" desc LIMIT 30
 --Q27.Top30 Client IP by Sessions
-SELECT common_client_ip , COUNT(*) AS sessions FROM connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) GROUP BY common_client_ip ORDER BY sessions desc LIMIT 0,30
+SELECT common_client_ip , COUNT(*) AS sessions FROM session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) GROUP BY common_client_ip ORDER BY sessions desc LIMIT 0,30
 --Q28.Top30 TCP Server Ports by Sessions
-SELECT "Server Port" AS "Server Port", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT common_server_port AS "Server Port", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( common_l4_protocol IN ( 'IPv4_TCP', 'IPv6_TCP' ) ) GROUP BY "Server Port" LIMIT 1048576) GROUP BY "Server Port" ORDER BY "Sessions" DESC LIMIT 30
+SELECT "Server Port" AS "Server Port", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT common_server_port AS "Server Port", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( common_l4_protocol IN ( 'IPv4_TCP', 'IPv6_TCP' ) ) GROUP BY "Server Port" LIMIT 1048576) GROUP BY "Server Port" ORDER BY "Sessions" DESC LIMIT 30
 --Q29.Top30 Domian by Bytes
-SELECT "domain" AS "Website Domain" , SUM(coalesce("bytes",0)) AS "Throughput" FROM ( SELECT SUM(coalesce(common_c2s_byte_num,0)) AS "bytes_sent" , SUM(coalesce(common_s2c_byte_num,0)) AS "bytes_received" , SUM(coalesce(common_c2s_byte_num+common_s2c_byte_num,0)) AS "bytes" , http_domain AS "domain" FROM connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty( http_domain) ) GROUP BY "domain" ORDER BY "bytes" desc ) GROUP BY "domain" ORDER BY "Throughput" desc LIMIT 30
+SELECT "domain" AS "Website Domain" , SUM(coalesce("bytes",0)) AS "Throughput" FROM ( SELECT SUM(coalesce(common_c2s_byte_num,0)) AS "bytes_sent" , SUM(coalesce(common_s2c_byte_num,0)) AS "bytes_received" , SUM(coalesce(common_c2s_byte_num+common_s2c_byte_num,0)) AS "bytes" , http_domain AS "domain" FROM session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty( http_domain) ) GROUP BY "domain" ORDER BY "bytes" desc ) GROUP BY "domain" ORDER BY "Throughput" desc LIMIT 30
 --Q30.Top30 Endpoint Devices by Bandwidth
-SELECT "device_id" AS "device_id", sum(coalesce("bytes", 0)) AS "bytes", sum(coalesce("bytes_sent", 0)) AS "Sent", sum(coalesce("bytes_received", 0)) AS "Received" FROM (SELECT sum(coalesce(common_c2s_byte_num, 0)) AS "bytes_sent", sum(coalesce(common_s2c_byte_num, 0)) AS "bytes_received", sum(common_c2s_byte_num + common_s2c_byte_num) AS bytes, common_device_id AS "device_id" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "device_id" ORDER BY "bytes" DESC LIMIT 1048576) GROUP BY "device_id" ORDER BY "bytes" DESC LIMIT 30
+SELECT "device_id" AS "device_id", sum(coalesce("bytes", 0)) AS "bytes", sum(coalesce("bytes_sent", 0)) AS "Sent", sum(coalesce("bytes_received", 0)) AS "Received" FROM (SELECT sum(coalesce(common_c2s_byte_num, 0)) AS "bytes_sent", sum(coalesce(common_s2c_byte_num, 0)) AS "bytes_received", sum(common_c2s_byte_num + common_s2c_byte_num) AS bytes, common_device_id AS "device_id" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "device_id" ORDER BY "bytes" DESC LIMIT 1048576) GROUP BY "device_id" ORDER BY "bytes" DESC LIMIT 30
 --Q31.Top30 Domain by Unique Client IP
-SELECT "Http.Domain" AS "Http.Domain", sum(coalesce("Client IP", 0)) AS "Client IP" FROM (SELECT http_domain AS "Http.Domain", uniq(common_client_ip) AS "Client IP" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(http_domain) ) GROUP BY "Http.Domain" ORDER BY "Client IP" DESC LIMIT 1048576) GROUP BY "Http.Domain" ORDER BY "Client IP" DESC LIMIT 30
+SELECT "Http.Domain" AS "Http.Domain", sum(coalesce("Client IP", 0)) AS "Client IP" FROM (SELECT http_domain AS "Http.Domain", uniq(common_client_ip) AS "Client IP" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(http_domain) ) GROUP BY "Http.Domain" ORDER BY "Client IP" DESC LIMIT 1048576) GROUP BY "Http.Domain" ORDER BY "Client IP" DESC LIMIT 30
 --Q32.Top100 Most Time Consuming Domains
-SELECT "Domain" AS "Domain", avg(coalesce("Avg Establish Latency(ms)", 0)) AS "Avg Establish Latency(ms)" FROM (SELECT http_domain AS "Domain", avg(coalesce(common_establish_latency_ms, 0)) AS "Avg Establish Latency(ms)" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(http_domain) ) GROUP BY "Domain" LIMIT 1048576) GROUP BY "Domain" ORDER BY "Avg Establish Latency(ms)" DESC LIMIT 100
+SELECT "Domain" AS "Domain", avg(coalesce("Avg Establish Latency(ms)", 0)) AS "Avg Establish Latency(ms)" FROM (SELECT http_domain AS "Domain", avg(coalesce(common_establish_latency_ms, 0)) AS "Avg Establish Latency(ms)" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(http_domain) ) GROUP BY "Domain" LIMIT 1048576) GROUP BY "Domain" ORDER BY "Avg Establish Latency(ms)" DESC LIMIT 100
 --Q33.Top30 Sources by Sessions
-SELECT "source" AS "source", sum(coalesce("sessions", 0)) AS "sessions" FROM (SELECT coalesce(nullif(common_subscriber_id, ''), nullif(common_client_ip, '')) AS "source", sum(coalesce(common_sessions, 0)) AS "sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "source" ORDER BY "sessions" DESC LIMIT 1048576) GROUP BY "source" ORDER BY "sessions" DESC LIMIT 30
+SELECT "source" AS "source", sum(coalesce("sessions", 0)) AS "sessions" FROM (SELECT coalesce(nullif(common_subscriber_id, ''), nullif(common_client_ip, '')) AS "source", sum(coalesce(common_sessions, 0)) AS "sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "source" ORDER BY "sessions" DESC LIMIT 1048576) GROUP BY "source" ORDER BY "sessions" DESC LIMIT 30
 --Q34.Top30 Destinations by Sessions
-SELECT "destination" AS "destination", sum(coalesce("sessions", 0)) AS "sessions" FROM (SELECT coalesce(nullif(http_domain, ''), nullif(common_server_ip, '')) AS "destination", sum(coalesce(common_sessions, 0)) AS "sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "destination" ORDER BY "sessions" DESC LIMIT 1048576) GROUP BY "destination" ORDER BY "sessions" DESC LIMIT 30
+SELECT "destination" AS "destination", sum(coalesce("sessions", 0)) AS "sessions" FROM (SELECT coalesce(nullif(http_domain, ''), nullif(common_server_ip, '')) AS "destination", sum(coalesce(common_sessions, 0)) AS "sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "destination" ORDER BY "sessions" DESC LIMIT 1048576) GROUP BY "destination" ORDER BY "sessions" DESC LIMIT 30
 --Q35.Top30 Destination Regions by Bandwidth
-SELECT "server_location" AS "server_location", sum(coalesce("bytes", 0)) AS "bytes", sum(coalesce("bytes_sent", 0)) AS "Sent", sum(coalesce("bytes_received", 0)) AS "Received" FROM (SELECT arrayElement(splitByString(',', common_server_location), length(splitByString(',', common_server_location))) AS "server_location", sum(coalesce(common_c2s_byte_num, 0)) AS "bytes_sent", sum(coalesce(common_s2c_byte_num, 0)) AS "bytes_received", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "bytes", sum(coalesce(common_sessions, 0)) AS "sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "server_location" ORDER BY "bytes" DESC LIMIT 1048576) GROUP BY "server_location" ORDER BY "bytes" DESC LIMIT 30
+SELECT "server_location" AS "server_location", sum(coalesce("bytes", 0)) AS "bytes", sum(coalesce("bytes_sent", 0)) AS "Sent", sum(coalesce("bytes_received", 0)) AS "Received" FROM (SELECT arrayElement(splitByString(',', common_server_location), length(splitByString(',', common_server_location))) AS "server_location", sum(coalesce(common_c2s_byte_num, 0)) AS "bytes_sent", sum(coalesce(common_s2c_byte_num, 0)) AS "bytes_received", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "bytes", sum(coalesce(common_sessions, 0)) AS "sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "server_location" ORDER BY "bytes" DESC LIMIT 1048576) GROUP BY "server_location" ORDER BY "bytes" DESC LIMIT 30
 --Q36.Top30 URLS by Sessions
-SELECT "Http URL" AS "Http URL", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT http_url AS "Http URL", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Http URL" LIMIT 1048576) GROUP BY "Http URL" ORDER BY "Sessions" DESC LIMIT 30
+SELECT "Http URL" AS "Http URL", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT http_url AS "Http URL", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Http URL" LIMIT 1048576) GROUP BY "Http URL" ORDER BY "Sessions" DESC LIMIT 30
 --Q37.Top30 Destination Transmission APP by Bandwidth
-SELECT "server_ip" AS "server_ip", groupUniqArray(coalesce("trans_app", 0)) AS "trans_app", sum(coalesce("bytes", 0)) AS "bytes", sum(coalesce("bytes_sent", 0)) AS "Sent", sum(coalesce("bytes_received", 0)) AS "Received" FROM (SELECT sum(coalesce(common_c2s_byte_num, 0)) AS "bytes_sent", sum(coalesce(common_s2c_byte_num, 0)) AS "bytes_received", sum(common_c2s_byte_num + common_s2c_byte_num) AS "bytes", groupUniqArray(concat(common_l4_protocol, '/', toString(common_server_port))) AS "trans_app", common_server_ip AS "server_ip" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(common_server_ip) ) GROUP BY "server_ip" ORDER BY "bytes" DESC LIMIT 1048576) GROUP BY "server_ip" ORDER BY "bytes" DESC LIMIT 30
+SELECT "server_ip" AS "server_ip", groupUniqArray(coalesce("trans_app", 0)) AS "trans_app", sum(coalesce("bytes", 0)) AS "bytes", sum(coalesce("bytes_sent", 0)) AS "Sent", sum(coalesce("bytes_received", 0)) AS "Received" FROM (SELECT sum(coalesce(common_c2s_byte_num, 0)) AS "bytes_sent", sum(coalesce(common_s2c_byte_num, 0)) AS "bytes_received", sum(common_c2s_byte_num + common_s2c_byte_num) AS "bytes", groupUniqArray(concat(common_l4_protocol, '/', toString(common_server_port))) AS "trans_app", common_server_ip AS "server_ip" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(common_server_ip) ) GROUP BY "server_ip" ORDER BY "bytes" DESC LIMIT 1048576) GROUP BY "server_ip" ORDER BY "bytes" DESC LIMIT 30
 --Q38.Browsing Users by Website domains and Sessions
-SELECT "Subscriber ID" AS "Subscriber ID", "Http.Domain" AS "Http.Domain", sum(coalesce("sessions", 0)) AS "sessions" FROM (SELECT http_domain AS "Http.Domain", common_subscriber_id AS "Subscriber ID", sum(coalesce(common_sessions, 0)) AS "sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(http_domain) AND notEmpty(common_subscriber_id) ) GROUP BY "Http.Domain", "Subscriber ID" ORDER BY "sessions" DESC LIMIT 1048576) GROUP BY "Subscriber ID", "Http.Domain" ORDER BY "sessions" DESC LIMIT 10000
+SELECT "Subscriber ID" AS "Subscriber ID", "Http.Domain" AS "Http.Domain", sum(coalesce("sessions", 0)) AS "sessions" FROM (SELECT http_domain AS "Http.Domain", common_subscriber_id AS "Subscriber ID", sum(coalesce(common_sessions, 0)) AS "sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(http_domain) AND notEmpty(common_subscriber_id) ) GROUP BY "Http.Domain", "Subscriber ID" ORDER BY "sessions" DESC LIMIT 1048576) GROUP BY "Subscriber ID", "Http.Domain" ORDER BY "sessions" DESC LIMIT 10000
 --Q39.Top Domain and Server IP by Bytes Sent
-SELECT "Http.Domain" AS "Http.Domain" , "Server IP" AS "Server IP" , SUM(coalesce("Bytes Sent",0)) AS "Bytes Sent" FROM ( SELECT common_server_ip AS "Server IP" , http_domain AS "Http.Domain" , SUM(coalesce(common_c2s_byte_num+common_s2c_byte_num,0)) AS "Bytes" , SUM(coalesce(common_c2s_byte_num,0)) AS "Bytes Sent" , SUM(coalesce(common_s2c_byte_num,0)) AS "Bytes Received" FROM connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty( http_domain) ) GROUP BY "Server IP" , "Http.Domain" ORDER BY "Bytes" desc LIMIT 1048576 ) GROUP BY "Http.Domain" , "Server IP" ORDER BY "Bytes Sent" desc LIMIT 10000
+SELECT "Http.Domain" AS "Http.Domain" , "Server IP" AS "Server IP" , SUM(coalesce("Bytes Sent",0)) AS "Bytes Sent" FROM ( SELECT common_server_ip AS "Server IP" , http_domain AS "Http.Domain" , SUM(coalesce(common_c2s_byte_num+common_s2c_byte_num,0)) AS "Bytes" , SUM(coalesce(common_c2s_byte_num,0)) AS "Bytes Sent" , SUM(coalesce(common_s2c_byte_num,0)) AS "Bytes Received" FROM session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty( http_domain) ) GROUP BY "Server IP" , "Http.Domain" ORDER BY "Bytes" desc LIMIT 1048576 ) GROUP BY "Http.Domain" , "Server IP" ORDER BY "Bytes Sent" desc LIMIT 10000
 --Q40.Top30 Website Domains by Client IP and Sessions
-SELECT "Http.Domain" AS "Http.Domain", "Client IP" AS "Client IP", sum(coalesce("sessions", 0)) AS "sessions" FROM (SELECT common_client_ip AS "Client IP", http_domain AS "Http.Domain", sum(coalesce(common_sessions, 0)) AS "sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(http_domain) ) GROUP BY "Client IP", "Http.Domain" ORDER BY "sessions" DESC LIMIT 1048576) GROUP BY "Http.Domain", "Client IP" ORDER BY "sessions" DESC LIMIT 10000
+SELECT "Http.Domain" AS "Http.Domain", "Client IP" AS "Client IP", sum(coalesce("sessions", 0)) AS "sessions" FROM (SELECT common_client_ip AS "Client IP", http_domain AS "Http.Domain", sum(coalesce(common_sessions, 0)) AS "sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(http_domain) ) GROUP BY "Client IP", "Http.Domain" ORDER BY "sessions" DESC LIMIT 1048576) GROUP BY "Http.Domain", "Client IP" ORDER BY "sessions" DESC LIMIT 10000
 --Q41.Domain is Accessed by Unique Client IP Trend(bytes Time Grain 5 minute)
-SELECT toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) AS _time , http_domain AS Domain, COUNT(DISTINCT(common_client_ip)) AS nums FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND notEmpty(http_domain) AND http_domain IN ( SELECT http_domain FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND notEmpty(http_domain) GROUP BY http_domain ORDER BY SUM(common_s2c_byte_num+common_c2s_byte_num) DESC LIMIT 5 ) GROUP BY toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) , http_domain ORDER BY toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) DESC LIMIT 10000
+SELECT toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) AS _time , http_domain AS Domain, COUNT(DISTINCT(common_client_ip)) AS nums FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND notEmpty(http_domain) AND http_domain IN ( SELECT http_domain FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND notEmpty(http_domain) GROUP BY http_domain ORDER BY SUM(common_s2c_byte_num+common_c2s_byte_num) DESC LIMIT 5 ) GROUP BY toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) , http_domain ORDER BY toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) DESC LIMIT 10000
 --Q42. Domain is Accessed by Unique Client IP Trend(sessions,Time Grain 5 minute)
-SELECT toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),3600)*3600) AS stat_time , http_domain , uniq (common_client_ip) AS nums FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start)-604800 AND common_recv_time < toDateTime(@end) AND http_domain IN ( SELECT http_domain FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND notEmpty(http_domain) GROUP BY http_domain ORDER BY COUNT(*) desc LIMIT 5 ) group by toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 3600)*3600), http_domain ORDER BY stat_time desc LIMIT 10000
+SELECT toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),3600)*3600) AS stat_time , http_domain , uniq (common_client_ip) AS nums FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start)-604800 AND common_recv_time < toDateTime(@end) AND http_domain IN ( SELECT http_domain FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND notEmpty(http_domain) GROUP BY http_domain ORDER BY COUNT(*) desc LIMIT 5 ) group by toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 3600)*3600), http_domain ORDER BY stat_time desc LIMIT 10000
 --Q43.Bandwidth Trend with Device ID(Time Grain 5 minute)
-SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", common_device_id AS "Device ID", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "Bytes" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Receive Time", "Device ID" LIMIT 10000
+SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", common_device_id AS "Device ID", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "Bytes" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Receive Time", "Device ID" LIMIT 10000
 --Q44.Internal IP by Sled IP and Sessions
-SELECT "Internal IP" AS "Internal IP", "Sled IP" AS "Sled IP", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT common_sled_ip AS "Sled IP", common_internal_ip AS "Internal IP", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Sled IP", "Internal IP" LIMIT 1048576) GROUP BY "Internal IP", "Sled IP" ORDER BY "Sessions" DESC LIMIT 10000
+SELECT "Internal IP" AS "Internal IP", "Sled IP" AS "Sled IP", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT common_sled_ip AS "Sled IP", common_internal_ip AS "Internal IP", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Sled IP", "Internal IP" LIMIT 1048576) GROUP BY "Internal IP", "Sled IP" ORDER BY "Sessions" DESC LIMIT 10000
 --Q45.Bandwidth Trend with Internal IP (Time Grain 5 minute)
-SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "Bytes", sum(coalesce(common_c2s_pkt_num + common_s2c_pkt_num, 0)) AS "Packets", sum(coalesce(common_sessions, 0)) AS "New Sessions", sum(coalesce(common_c2s_byte_num, 0)) AS "Bytes Sent", sum(coalesce(common_s2c_byte_num, 0)) AS "Bytes Received", sum(coalesce(common_c2s_pkt_num, 0)) AS "Packets Sent", sum(coalesce(common_s2c_pkt_num, 0)) AS "Packets Received" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) AND @common_filter ) GROUP BY "Receive Time" LIMIT 10000
+SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(common_recv_time),INTERVAL 5 MINUTE))) AS "Receive Time", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "Bytes", sum(coalesce(common_c2s_pkt_num + common_s2c_pkt_num, 0)) AS "Packets", sum(coalesce(common_sessions, 0)) AS "New Sessions", sum(coalesce(common_c2s_byte_num, 0)) AS "Bytes Sent", sum(coalesce(common_s2c_byte_num, 0)) AS "Bytes Received", sum(coalesce(common_c2s_pkt_num, 0)) AS "Packets Sent", sum(coalesce(common_s2c_pkt_num, 0)) AS "Packets Received" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) AND @common_filter ) GROUP BY "Receive Time" LIMIT 10000
 --Q46.Top30 Domains Detail with Internal IP
-SELECT "Domain" AS "Domain", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT http_domain AS "Domain", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) AND @common_filter ) AND ( notEmpty(http_domain) ) GROUP BY "Domain" LIMIT 1048576) GROUP BY "Domain" ORDER BY "Sessions" DESC LIMIT 30
+SELECT "Domain" AS "Domain", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT http_domain AS "Domain", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) AND @common_filter ) AND ( notEmpty(http_domain) ) GROUP BY "Domain" LIMIT 1048576) GROUP BY "Domain" ORDER BY "Sessions" DESC LIMIT 30
 --Q47.Top30 URLS Detail with Internal IP
-SELECT "URL" AS "URL", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT http_url AS "URL", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) AND @common_filter ) AND ( notEmpty(http_url) ) GROUP BY "URL" LIMIT 1048576) GROUP BY "URL" ORDER BY "Sessions" DESC LIMIT 30
+SELECT "URL" AS "URL", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT http_url AS "URL", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) AND @common_filter ) AND ( notEmpty(http_url) ) GROUP BY "URL" LIMIT 1048576) GROUP BY "URL" ORDER BY "Sessions" DESC LIMIT 30
 --Q48.Top Domains with Unique Client IP and Subscriber ID
-SELECT "Http.Domain" AS "Http.Domain", sum(coalesce("Unique Client IP", 0)) AS "Unique Client IP", sum(coalesce("Unique Subscriber ID", 0)) AS "Unique Subscriber ID" FROM (SELECT http_domain AS "Http.Domain", uniq(common_client_ip) AS "Unique Client IP", uniq(common_subscriber_id) AS "Unique Subscriber ID" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(http_domain) ) GROUP BY "Http.Domain" LIMIT 1048576) GROUP BY "Http.Domain" ORDER BY "Unique Client IP" DESC LIMIT 100
+SELECT "Http.Domain" AS "Http.Domain", sum(coalesce("Unique Client IP", 0)) AS "Unique Client IP", sum(coalesce("Unique Subscriber ID", 0)) AS "Unique Subscriber ID" FROM (SELECT http_domain AS "Http.Domain", uniq(common_client_ip) AS "Unique Client IP", uniq(common_subscriber_id) AS "Unique Subscriber ID" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( notEmpty(http_domain) ) GROUP BY "Http.Domain" LIMIT 1048576) GROUP BY "Http.Domain" ORDER BY "Unique Client IP" DESC LIMIT 100
 --Q49.Top100 Domains by Packets sent
-SELECT "Http.Domain" AS "Http.Domain", sum(coalesce("Packets Sent", 0)) AS "Packets Sent" FROM (SELECT http_domain AS "Http.Domain", sum(coalesce(common_c2s_pkt_num, 0)) AS "Packets Sent" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Http.Domain" LIMIT 1048576) GROUP BY "Http.Domain" ORDER BY "Packets Sent" DESC LIMIT 100
+SELECT "Http.Domain" AS "Http.Domain", sum(coalesce("Packets Sent", 0)) AS "Packets Sent" FROM (SELECT http_domain AS "Http.Domain", sum(coalesce(common_c2s_pkt_num, 0)) AS "Packets Sent" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Http.Domain" LIMIT 1048576) GROUP BY "Http.Domain" ORDER BY "Packets Sent" DESC LIMIT 100
 --Q50.Internal and External asymmetric traffic
-SELECT "Internal IP" AS "Internal IP", "External IP" AS "External IP", "Sled IP" AS "Sled IP", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT common_sled_ip AS "Sled IP", common_external_ip AS "External IP", common_internal_ip AS "Internal IP", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "Bytes Sent+Bytes Received", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( common_stream_dir != 3 ) GROUP BY "Sled IP", "External IP", "Internal IP" LIMIT 1048576) GROUP BY "Internal IP", "External IP", "Sled IP" ORDER BY "Sessions" DESC LIMIT 500
+SELECT "Internal IP" AS "Internal IP", "External IP" AS "External IP", "Sled IP" AS "Sled IP", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT common_sled_ip AS "Sled IP", common_external_ip AS "External IP", common_internal_ip AS "Internal IP", sum(coalesce(common_c2s_byte_num + common_s2c_byte_num, 0)) AS "Bytes Sent+Bytes Received", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( common_stream_dir != 3 ) GROUP BY "Sled IP", "External IP", "Internal IP" LIMIT 1048576) GROUP BY "Internal IP", "External IP", "Sled IP" ORDER BY "Sessions" DESC LIMIT 500
 --Q51.Client and Server ASN asymmetric traffic
-SELECT "Client ASN" AS "Client ASN", "Server ASN" AS "Server ASN", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT common_server_asn AS "Server ASN", common_client_asn AS "Client ASN", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( common_stream_dir != 3 ) GROUP BY "Server ASN", "Client ASN" LIMIT 1048576) GROUP BY "Client ASN", "Server ASN" ORDER BY "Sessions" DESC LIMIT 500
+SELECT "Client ASN" AS "Client ASN", "Server ASN" AS "Server ASN", sum(coalesce("Sessions", 0)) AS "Sessions" FROM (SELECT common_server_asn AS "Server ASN", common_client_asn AS "Client ASN", sum(coalesce(common_sessions, 0)) AS "Sessions" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) AND ( common_stream_dir != 3 ) GROUP BY "Server ASN", "Client ASN" LIMIT 1048576) GROUP BY "Client ASN", "Server ASN" ORDER BY "Sessions" DESC LIMIT 500
 --Q52.Top handshake latency by Website and Client IPs
-SELECT "SSL.SNI" AS "SSL.SNI", "Client IP" AS "Client IP", avg(coalesce("Establish Latency(ms)", 0)) AS "Establish Latency(ms)" FROM (SELECT common_client_ip AS "Client IP", ssl_sni AS "SSL.SNI", avg(coalesce(common_establish_latency_ms, 0)) AS "Establish Latency(ms)" FROM connection_record_log AS connection_record_log WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Client IP", "SSL.SNI" LIMIT 1048576) GROUP BY "SSL.SNI", "Client IP" ORDER BY "Establish Latency(ms)" DESC LIMIT 500
+SELECT "SSL.SNI" AS "SSL.SNI", "Client IP" AS "Client IP", avg(coalesce("Establish Latency(ms)", 0)) AS "Establish Latency(ms)" FROM (SELECT common_client_ip AS "Client IP", ssl_sni AS "SSL.SNI", avg(coalesce(common_establish_latency_ms, 0)) AS "Establish Latency(ms)" FROM session_record AS session_record WHERE ( ( common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) ) ) GROUP BY "Client IP", "SSL.SNI" LIMIT 1048576) GROUP BY "SSL.SNI", "Client IP" ORDER BY "Establish Latency(ms)" DESC LIMIT 500
 --Q53.Domain Baidu.com Metrics
-select  FROM_UNIXTIME(min(common_recv_time)) as "First Seen" , FROM_UNIXTIME(max(common_recv_time)) as "Last Seen" ,  median(http_response_latency_ms) as "Server Processing Time Median(ms)", count(1) as Responses,any(common_server_location)  as Location from connection_record_log WHERE  common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end)  AND  http_domain='baidu.com'
+select  FROM_UNIXTIME(min(common_recv_time)) as "First Seen" , FROM_UNIXTIME(max(common_recv_time)) as "Last Seen" ,  median(http_response_latency_ms) as "Server Processing Time Median(ms)", count(1) as Responses,any(common_server_location)  as Location from session_record WHERE  common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end)  AND  http_domain='baidu.com'
 --Q54.Domain baidu.com Drill down Client IP
-select common_client_ip as "Client IP" , avg(common_establish_latency_ms) as "Establishing Time Mean(ms)", count(1) as Responses,any(common_client_location) as Location from connection_record_log where  common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end)  and http_domain='baidu.com' group by "Client IP" order by Responses desc limit 100
+select common_client_ip as "Client IP" , avg(common_establish_latency_ms) as "Establishing Time Mean(ms)", count(1) as Responses,any(common_client_location) as Location from session_record where  common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end)  and http_domain='baidu.com' group by "Client IP" order by Responses desc limit 100
 --Q55.Domain baidu.com Drill down Server IP
-select common_server_ip as "Server IP" , avg(http_response_latency_ms) as "Server Processing Time Mean(ms)", count(1) as Responses,any(common_server_location) as Location from connection_record_log where common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) and http_domain='baidu.com' group by "Server IP" order by Responses desc limit 100
+select common_server_ip as "Server IP" , avg(http_response_latency_ms) as "Server Processing Time Mean(ms)", count(1) as Responses,any(common_server_location) as Location from session_record where common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) and http_domain='baidu.com' group by "Server IP" order by Responses desc limit 100
 --Q56.Domain baidu.com Drill down URI
-select http_url as "URI" , avg(http_response_latency_ms) as "Server Processing Time Mean(ms)", count(1) as Responses from connection_record_log where  common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) and http_domain='baidu.com' group by "URI" order by Responses desc limit 100
+select http_url as "URI" , avg(http_response_latency_ms) as "Server Processing Time Mean(ms)", count(1) as Responses from session_record where  common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) and http_domain='baidu.com' group by "URI" order by Responses desc limit 100
 --Q57.L7 Protocol Metrics
-select  common_l7_protocol as "Protocol" , uniq(common_client_ip) as "Clients" , uniq(common_server_ip) as "Servers", count(1) as Sessions,sum(common_c2s_byte_num+common_s2c_byte_num)  as bytes from connection_record_log where common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) and notEmpty(common_l7_protocol)  group by common_l7_protocol  order by bytes desc
+select  common_l7_protocol as "Protocol" , uniq(common_client_ip) as "Clients" , uniq(common_server_ip) as "Servers", count(1) as Sessions,sum(common_c2s_byte_num+common_s2c_byte_num)  as bytes from session_record where common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) and notEmpty(common_l7_protocol)  group by common_l7_protocol  order by bytes desc
 --Q58.L7 Protocol SIP Drill down Client IP
-select common_client_ip as "Client IP" , count(1) as Sessions,sum(common_c2s_byte_num) as "Bytes Out", sum(common_s2c_byte_num) as "Bytes In",any(common_client_location) as Location from connection_record_log where common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) and common_l7_protocol='SIP' group by "Client IP" order by Sessions desc limit 100
+select common_client_ip as "Client IP" , count(1) as Sessions,sum(common_c2s_byte_num) as "Bytes Out", sum(common_s2c_byte_num) as "Bytes In",any(common_client_location) as Location from session_record where common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) and common_l7_protocol='SIP' group by "Client IP" order by Sessions desc limit 100
 --Q59.L7 Protocol SIP Drill down Server IP
-select common_server_ip as "Server IP" , count(1) as Sessions,sum(common_c2s_byte_num) as "Bytes Out", sum(common_s2c_byte_num) as "Bytes In",any(common_server_location) as Location from connection_record_log where common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) and common_l7_protocol='SIP' group by "Server IP" order by Sessions desc limit 100
+select common_server_ip as "Server IP" , count(1) as Sessions,sum(common_c2s_byte_num) as "Bytes Out", sum(common_s2c_byte_num) as "Bytes In",any(common_server_location) as Location from session_record where common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) and common_l7_protocol='SIP' group by "Server IP" order by Sessions desc limit 100
 --Q60.Top5 Server IP keys with Unique Client IPs Trend (Grain 5 minute)
-SELECT toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) AS _time , common_server_ip AS server_ip, COUNT(DISTINCT(common_client_ip)) AS nums FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND common_server_ip IN ( SELECT common_server_ip FROM connection_record_log AS connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end)  GROUP BY common_server_ip ORDER BY count(*) DESC LIMIT 5 ) GROUP BY toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) , server_ip ORDER BY toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) DESC LIMIT 10000
+SELECT toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) AS _time , common_server_ip AS server_ip, COUNT(DISTINCT(common_client_ip)) AS nums FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND common_server_ip IN ( SELECT common_server_ip FROM session_record AS session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end)  GROUP BY common_server_ip ORDER BY count(*) DESC LIMIT 5 ) GROUP BY toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) , server_ip ORDER BY toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))),300)*300) DESC LIMIT 10000
 --Q61.TopN Optimizer
-SELECT http_url AS url, SUM(common_sessions) AS sessions FROM connection_record_log WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND notEmpty(http_url) GROUP BY http_url ORDER BY sessions DESC limit 10
\ No newline at end of file
+SELECT http_url AS url, SUM(common_sessions) AS sessions FROM session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND notEmpty(http_url) GROUP BY http_url ORDER BY sessions DESC limit 10
\ No newline at end of file
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/dos_event_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/dos_event.json
index 10fd1ac4..52478467 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/dos_event_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/dos_event.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "dos_event_log",
+  "name": "dos_event",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/engine-queries-template.sql b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/engine-queries-template.sql
index 04763a2e..583eb4a6 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/engine-queries-template.sql
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/engine-queries-template.sql
@@ -1,39 +1,39 @@
 --Q01.CK DateTime
-select toDateTime(common_recv_time) as common_recv_time from connection_record_log where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) limit 20
+select toDateTime(common_recv_time) as common_recv_time from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) limit 20
 --Q02.Standard DateTime
-select FROM_UNIXTIME(common_recv_time) as common_recv_time from connection_record_log where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) limit 20
+select FROM_UNIXTIME(common_recv_time) as common_recv_time from session_record where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) limit 20
 --Q03.count(1)
-select count(1) from connection_record_log where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)
+select count(1) from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)
 --Q04.count(*)
-select count(*) from connection_record_log where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)
+select count(*) from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)
 --Q05.UDF APPROX_COUNT_DISTINCT_DS_HLL
 SELECT policy_id, APPROX_COUNT_DISTINCT_DS_HLL(isp) as num  FROM proxy_event_hits_log where  __time >= @start and __time < @end and policy_id=0 group by policy_id
 --Q06.UDF TIME_FLOOR_WITH_FILL
-select TIME_FLOOR_WITH_FILL(common_recv_time,'PT5M','previous') as stat_time from connection_record_log where  common_recv_time > @start and common_recv_time < @end  group by stat_time
+select TIME_FLOOR_WITH_FILL(common_recv_time,'PT5M','previous') as stat_time from session_record where  common_recv_time > @start and common_recv_time < @end  group by stat_time
 --Q07.UDF GEO IP
-select IP_TO_GEO(common_client_ip) as geo,IP_TO_CITY(common_server_ip) as city,IP_TO_COUNTRY(common_server_ip) as country from connection_record_log limit 10
+select IP_TO_GEO(common_client_ip) as geo,IP_TO_CITY(common_server_ip) as city,IP_TO_COUNTRY(common_server_ip) as country from session_record limit 10
 --Q08.Special characters
-select * from connection_record_log where (common_protocol_label ='/$' or common_client_ip like'%') limit 10
+select * from session_record where (common_protocol_label ='/$' or common_client_ip like'%') limit 10
 --Q09.Federation Query
-select * from (select FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(common_recv_time,'PT5M','zero')) as stat_time from connection_record_log where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) group by stat_time order by stat_time asc)
+select * from (select FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(common_recv_time,'PT5M','zero')) as stat_time from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) group by stat_time order by stat_time asc)
 --Q10.Catalog Database
-select * from tsg_galaxy_v3.connection_record_log where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) limit 20
+select * from tsg_galaxy_v3.session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) limit 20
 --Q11.Session Record Logs
-select * from connection_record_log where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)  AND @common_filter order by common_recv_time desc limit 20
+select * from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)  AND @common_filter order by common_recv_time desc limit 20
 --Q12.Live Session Record Logs
-select * from live_session_record_log where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)  AND @common_filter order by common_recv_time desc limit 20
+select * from interim_session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)  AND @common_filter order by common_recv_time desc limit 20
 --Q13.Transaction  Record Logs
-select * from transaction_record_log where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)  order by common_recv_time desc limit 20
+select * from transaction_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)  order by common_recv_time desc limit 20
 --Q14.Security Event Logs
-select * from security_event_log where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) AND @common_filter order by common_recv_time desc limit 0,20
+select * from security_event where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) AND @common_filter order by common_recv_time desc limit 0,20
 --Q15.Proxy Event Logs
-select * from proxy_event_log where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) order by common_recv_time desc limit 0,20
+select * from proxy_event where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) order by common_recv_time desc limit 0,20
 --Q16.Radius Record Logs
-select * from radius_record_log where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) order by common_recv_time desc limit 0,20
+select * from radius_record where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) order by common_recv_time desc limit 0,20
 --Q17.GTPC Record Logs
-select * from gtpc_record_log where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) order by common_recv_time desc limit 0,20
+select * from gtpc_record where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) order by common_recv_time desc limit 0,20
 --Q18.Security Event Logs with fields
-select FROM_UNIXTIME(common_recv_time) as common_recv_time,common_log_id,common_policy_id,common_subscriber_id,common_client_ip,common_client_port,common_l4_protocol,common_address_type,common_server_ip,common_server_port,common_action,common_direction,common_sled_ip,common_client_location,common_client_asn,common_server_location,common_server_asn,common_c2s_pkt_num,common_s2c_pkt_num,common_c2s_byte_num,common_s2c_byte_num,common_schema_type,common_sub_action,common_device_id, FROM_UNIXTIME(common_start_time) as common_start_time, FROM_UNIXTIME(common_end_time) as common_end_time,common_establish_latency_ms,common_con_duration_ms,common_stream_dir,common_stream_trace_id,http_url,http_host,http_domain,http_request_body,http_response_body,http_cookie,http_referer,http_user_agent,http_content_length,http_content_type,http_set_cookie,http_version,http_response_latency_ms,http_action_file_size,http_session_duration_ms,mail_protocol_type,mail_account,mail_from_cmd,mail_to_cmd,mail_from,mail_to,mail_cc,mail_bcc,mail_subject,mail_attachment_name,mail_eml_file,dns_message_id,dns_qr,dns_opcode,dns_aa,dns_tc,dns_rd,dns_ra,dns_rcode,dns_qdcount,dns_ancount,dns_nscount,dns_arcount,dns_qname,dns_qtype,dns_qclass,dns_cname,dns_sub,dns_rr,ssl_sni,ssl_san,ssl_cn,ssl_pinningst,ssl_intercept_state,ssl_server_side_latency,ssl_client_side_latency,ssl_server_side_version,ssl_client_side_version,ssl_cert_verify,ssl_error,quic_version,quic_sni,quic_user_agent,ftp_account,ftp_url,ftp_content  from security_event_log where  common_recv_time  >= @start and common_recv_time < @end  order by  common_recv_time desc  limit 10000
+select FROM_UNIXTIME(common_recv_time) as common_recv_time,common_log_id,common_policy_id,common_subscriber_id,common_client_ip,common_client_port,common_l4_protocol,common_address_type,common_server_ip,common_server_port,common_action,common_direction,common_sled_ip,common_client_location,common_client_asn,common_server_location,common_server_asn,common_c2s_pkt_num,common_s2c_pkt_num,common_c2s_byte_num,common_s2c_byte_num,common_schema_type,common_sub_action,common_device_id, FROM_UNIXTIME(common_start_time) as common_start_time, FROM_UNIXTIME(common_end_time) as common_end_time,common_establish_latency_ms,common_con_duration_ms,common_stream_dir,common_stream_trace_id,http_url,http_host,http_domain,http_request_body,http_response_body,http_cookie,http_referer,http_user_agent,http_content_length,http_content_type,http_set_cookie,http_version,http_response_latency_ms,http_action_file_size,http_session_duration_ms,mail_protocol_type,mail_account,mail_from_cmd,mail_to_cmd,mail_from,mail_to,mail_cc,mail_bcc,mail_subject,mail_attachment_name,mail_eml_file,dns_message_id,dns_qr,dns_opcode,dns_aa,dns_tc,dns_rd,dns_ra,dns_rcode,dns_qdcount,dns_ancount,dns_nscount,dns_arcount,dns_qname,dns_qtype,dns_qclass,dns_cname,dns_sub,dns_rr,ssl_sni,ssl_san,ssl_cn,ssl_pinningst,ssl_intercept_state,ssl_server_side_latency,ssl_client_side_latency,ssl_server_side_version,ssl_client_side_version,ssl_cert_verify,ssl_error,quic_version,quic_sni,quic_user_agent,ftp_account,ftp_url,ftp_content  from security_event where  common_recv_time  >= @start and common_recv_time < @end  order by  common_recv_time desc  limit 10000
 --Q19.Radius ON/OFF Logs For Frame IP
 select framed_ip, arraySlice(groupUniqArray(concat(toString(event_timestamp),':', if(acct_status_type=1,'start','stop'))),1,100000) as timeseries from radius_onff_log where event_timestamp >=toDateTime(@start) and event_timestamp <toDateTime(@end) group by framed_ip limit 20
 --Q20.Radius ON/OFF Logs For Account
@@ -43,27 +43,27 @@ select count(distinct(framed_ip)) as active_ip_num , sum(acct_session_time) as o
 --Q22.Radius ON/OFF Logs Account Access Detail
 select max(if(acct_status_type=1,event_timestamp,0)) as start_time,max(if(acct_status_type=2,event_timestamp,0)) as end_time, any(framed_ip) as ip,max(acct_session_time) as online_duration  from radius_onff_log where event_timestamp >= @start and event_timestamp < @end  group by acct_session_id  order by start_time desc limit 200
 --Q23.Report for Client IP
-select common_client_ip, count(*) as sessions from connection_record_log where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@end))  group by common_client_ip order by sessions desc limit 0,100
+select common_client_ip, count(*) as sessions from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@end))  group by common_client_ip order by sessions desc limit 0,100
 --Q24.Report for Server IP
-select common_server_ip, count(*) as sessions from connection_record_log where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  group by common_server_ip order by sessions desc limit 0,100
+select common_server_ip, count(*) as sessions from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  group by common_server_ip order by sessions desc limit 0,100
 --Q25.Report for SSL SNI
-select ssl_sni, count(*) as sessions from connection_record_log where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  group by ssl_sni order by sessions desc limit 0,100
+select ssl_sni, count(*) as sessions from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  group by ssl_sni order by sessions desc limit 0,100
 --Q26.Report for SSL APP
-select common_app_label as applicaiton, count(*) as sessions from connection_record_log where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  group by applicaiton order by sessions desc limit 0,100
+select common_app_label as applicaiton, count(*) as sessions from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  group by applicaiton order by sessions desc limit 0,100
 --Q27.Report for Domains
-select http_domain AS domain,SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes,SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes,SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes FROM connection_record_log WHERE common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and notEmpty(domain)  GROUP BY domain ORDER BY bytes DESC LIMIT 100
+select http_domain AS domain,SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes,SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes,SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes FROM session_record WHERE common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and notEmpty(domain)  GROUP BY domain ORDER BY bytes DESC LIMIT 100
 --Q28.Report for Domains with unique Client IP
-select toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300) as stat_time, http_domain, uniq (common_client_ip) as nums  from connection_record_log where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start))  and http_domain in (select http_domain from connection_record_log where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start))  and notEmpty(http_domain) group by http_domain order by SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) desc limit 10 ) group by toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300), http_domain order by stat_time asc limit 500
+select toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300) as stat_time, http_domain, uniq (common_client_ip) as nums  from session_record where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start))  and http_domain in (select http_domain from session_record where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start))  and notEmpty(http_domain) group by http_domain order by SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) desc limit 10 ) group by toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300), http_domain order by stat_time asc limit 500
 --Q29. Report for HTTP Host
-SELECT http_host as host, SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes,SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes,SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes FROM connection_record_log WHERE common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  and notEmpty(http_host) GROUP BY host ORDER BY bytes DESC limit 100 union all SELECT  'totals'  as host, SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes, SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes, SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes from connection_record_log where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  and notEmpty(http_host)
+SELECT http_host as host, SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes,SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes,SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes FROM session_record WHERE common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  and notEmpty(http_host) GROUP BY host ORDER BY bytes DESC limit 100 union all SELECT  'totals'  as host, SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes, SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes, SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  and notEmpty(http_host)
 --Q30.Report for HTTP/HTTPS URLS with Sessions
-SELECT http_url AS url,count(*) AS sessions FROM proxy_event_log WHERE common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and notEmpty(http_url) GROUP BY url ORDER BY sessions DESC LIMIT 100
+SELECT http_url AS url,count(*) AS sessions FROM proxy_event WHERE common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and notEmpty(http_url) GROUP BY url ORDER BY sessions DESC LIMIT 100
 --Q31.Report for HTTP/HTTPS URLS with UNIQUE Client IP
-select toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300) as stat_time, http_url, count(distinct(common_client_ip)) as nums  from proxy_event_log where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and http_url IN (select http_url from proxy_event_log where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and notEmpty(http_url)  group by http_url order by count(*) desc limit 10 ) group by toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300), http_url order by stat_time asc limit 500
+select toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300) as stat_time, http_url, count(distinct(common_client_ip)) as nums  from proxy_event where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and http_url IN (select http_url from proxy_event where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and notEmpty(http_url)  group by http_url order by count(*) desc limit 10 ) group by toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300), http_url order by stat_time asc limit 500
 --Q32.Report for Subscriber ID with Sessions
-select common_subscriber_id as user, count(*) as sessions from connection_record_log where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  and notEmpty(user)   group by common_subscriber_id order by sessions desc limit 0,100
+select common_subscriber_id as user, count(*) as sessions from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))  and notEmpty(user)   group by common_subscriber_id order by sessions desc limit 0,100
 --Q33.Report for Subscriber ID with Bandwidth
-SELECT common_subscriber_id as user,SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes,SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes,SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes FROM connection_record_log WHERE common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start)) and notEmpty(user)   GROUP BY user ORDER BY bytes  DESC LIMIT 100
+SELECT common_subscriber_id as user,SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes,SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes,SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes FROM session_record WHERE common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start)) and notEmpty(user)   GROUP BY user ORDER BY bytes  DESC LIMIT 100
 --Q34.Report Unique Endpoints
-select uniq(common_client_ip) as "Client IP",uniq(common_server_ip) as "Server IP",uniq(common_internal_ip) as "Internal IP",uniq(common_external_ip) as "External IP",uniq(http_domain) as "Domain",uniq(ssl_sni) as "SNI"  from connection_record_log where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))
+select uniq(common_client_ip) as "Client IP",uniq(common_server_ip) as "Server IP",uniq(common_internal_ip) as "Internal IP",uniq(common_external_ip) as "External IP",uniq(http_domain) as "Domain",uniq(ssl_sni) as "SNI"  from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))
 
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/gtpc_record_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/gtpc_record.json
index 8d384871..80b129b8 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/gtpc_record_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/gtpc_record.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "gtpc_record_log",
+  "name": "gtpc_record",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/live_session_record_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/interim_session_record.json
index 2b41d5e9..4c1a07f5 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/live_session_record_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/interim_session_record.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "live_session_record_log",
+  "name": "interim_session_record",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/meta_data.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/meta_data.json
index 625bb984..5b2fdcc8 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/meta_data.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/meta_data.json
@@ -4,24 +4,24 @@
       "namespace": "tsg_galaxy_v3",
       "group": "CLICKHOUSE_GROUP",
       "tables": [
-        "connection_record_log",
         "proxy_ip_info",
-        "radius_record_log",
         "security_ip_info",
-        "proxy_event_log",
-        "security_event_log",
-        "connection_record_log_http_domain",
-        "sys_packet_capture_log",
         "radius_onff_log",
-        "active_defence_event_log",
-        "connection_record_log_common_server_ip",
         "security_website_domain_info",
-        "connection_record_log_common_client_ip",
-        "voip_record_log",
-        "transaction_record_log",
-        "gtpc_record_log",
-        "live_session_record_log",
-        "dos_event_log"
+        "session_record",
+        "session_record_common_client_ip",
+        "session_record_common_server_ip",
+        "session_record_http_domain",
+        "interim_session_record",
+        "transaction_record",
+        "radius_record",
+        "voip_record",
+        "gtpc_record",
+        "security_event",
+        "proxy_event",
+        "dos_event",
+        "active_defence_event",
+        "sys_packet_capture_event"
       ]
     },
     {
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/proxy_event_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/proxy_event.json
index d8a3f7ae..99ee8cbc 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/proxy_event_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/proxy_event.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "proxy_event_log",
+  "name": "proxy_event",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/radius_record_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/radius_record.json
index ba586eca..727afc21 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/radius_record_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/radius_record.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "radius_record_log",
+  "name": "radius_record",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/security_event_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/security_event.json
index dc926257..802756e8 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/security_event_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/security_event.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "security_event_log",
+  "name": "security_event",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/connection_record_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/session_record.json
index 14346eb8..d873c693 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/connection_record_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/session_record.json
@@ -1,11 +1,11 @@
 {
   "type": "record",
-  "name": "connection_record_log",
+  "name": "session_record",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
     "partition_key": "common_recv_time",
-    "index_table": "connection_record_log_common_client_ip,connection_record_log_common_server_ip,connection_record_log_http_domain",
+    "index_table": "session_record_common_client_ip,session_record_common_server_ip,session_record_http_domain",
     "functions": {
       "$ref": "public_schema_info.json#/functions"
     },
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/connection_record_log_common_client_ip.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/session_record_common_client_ip.json
index 234e7d58..9184e36c 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/connection_record_log_common_client_ip.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/session_record_common_client_ip.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "connection_record_log_common_client_ip",
+  "name": "session_record_common_client_ip",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/connection_record_log_common_server_ip.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/session_record_common_server_ip.json
index 3024dc68..a7c977f6 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/connection_record_log_common_server_ip.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/session_record_common_server_ip.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "connection_record_log_common_server_ip",
+  "name": "session_record_common_server_ip",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/connection_record_log_http_domain.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/session_record_http_domain.json
index 1289b507..65414eac 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/connection_record_log_http_domain.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/session_record_http_domain.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "connection_record_log_http_domain",
+  "name": "session_record_http_domain",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/sys_packet_capture_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/sys_packet_capture_event.json
index 744ce904..2e62d87d 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/sys_packet_capture_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/sys_packet_capture_event.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "sys_packet_capture_log",
+  "name": "sys_packet_capture_event",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/transaction_record_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/transaction_record.json
index 856a064a..f58c2cf2 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/transaction_record_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/transaction_record.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "transaction_record_log",
+  "name": "transaction_record",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_stream_trace_id",
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/voip_record_log.json b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/voip_record.json
index 1b372822..86019a32 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/voip_record_log.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-dev_nacos/data/config-data-tenant/dev/Galaxy/voip_record.json
@@ -1,6 +1,6 @@
 {
   "type": "record",
-  "name": "voip_record_log",
+  "name": "voip_record",
   "namespace": "tsg_galaxy_v3",
   "doc": {
     "primary_key": "common_log_id",
diff --git a/src/main/java/com/mesalab/qgw/service/impl/MetadataServiceImpl.java b/src/main/java/com/mesalab/qgw/service/impl/MetadataServiceImpl.java
index 74821336..6a35a626 100644
--- a/src/main/java/com/mesalab/qgw/service/impl/MetadataServiceImpl.java
+++ b/src/main/java/com/mesalab/qgw/service/impl/MetadataServiceImpl.java
@@ -209,57 +209,36 @@ public class MetadataServiceImpl implements MetadataService {
                 log.error("{} field's doc isn't jsonString and won't convert: {}", next.get("name"), next.get("doc"));
             }
         }
-        return (Map) fillRef(resultMap, name);
+        fillReference(resultMap, name);
+        return resultMap;
     }
 
     /**
-     * Desc: $ref实际引用赋值
-     *
-     * @param obj 需操作对象
-     * @param fileName 当前文件名称(不含.json)
-     * @return {@link Object}
-     * @created by wWei
-     * @date 2021/8/17 6:00 下午
+     * @param map       需操作对象
+     * @param cfgName   当前文件名称
+     * @return void
+     * @Description     $ref实际引用部分赋值
+     * @author wanghao
+     * @date 2021/9/1 17:02
      */
-    private Object fillRef(Object obj, String fileName) {
-        if (obj instanceof Map) {
-            Map map = (Map) obj;
-            if (map.containsKey(KEY_REF)) {
-                return getRefValue(fileName, map);
-            } else {
-                fillRefMap(map, fileName);
-            }
-        } else if (obj instanceof List) {
-            fillRefList(fileName, (List) obj);
-        }
-        return obj;
-    }
 
-    private void fillRefList(String fileName, List list) {
-        for (int i = 0; i < list.size(); i++) {
-            if (list.get(i) instanceof Map) {
-                Map map = (Map) list.get(i);
-                if (map.containsKey(KEY_REF)) {
-                    list.set(i, getRefValue(fileName, map));
-                } else {
-                    fillRefMap(map, fileName);
-                }
-            }
-        }
-    }
-
-    private void fillRefMap(Map map, String fileName) {
-        for (Object key : map.keySet()) {
-            Object value = map.get(key);
-            if (value instanceof Map) {
-                Map mapValue = (Map) value;
+    private void fillReference(Map map, String cfgName) {
+        for (Object mapKey : map.keySet()) {
+            Object keyObj = map.get(mapKey);
+            if (keyObj instanceof Map) {
+                Map mapValue = (Map) keyObj;
                 if (mapValue.containsKey(KEY_REF)) {
-                    map.put(key, getRefValue(fileName, mapValue));
+                    map.put(mapKey, getRefValue(cfgName, mapValue));
                 } else {
-                    fillRefMap(mapValue, fileName);
+                    fillReference(mapValue, cfgName);
+                }
+            } else if (keyObj instanceof List) {
+                List listValue = (List) keyObj;
+                for (Object next : listValue) {
+                    if (next instanceof Map) {
+                        fillReference((Map) next, cfgName);
+                    }
                 }
-            } else if (value instanceof List) {
-                fillRefList(fileName, (List) value);
             }
         }
     }
@@ -290,10 +269,10 @@ public class MetadataServiceImpl implements MetadataService {
         return content;
     }
 
-    private Object getRefValue(String fileName, Map map) {
+    private Object getRefValue(String schemaName, Map map) {
         String[] split = getRefStrArray(map);
         String jsonPath = getJsonPath(split);
-        return JsonPath.read(getCfg(getFileName(split, fileName)), jsonPath);
+        return JsonPath.read(getCfg(getFileName(split, schemaName)), jsonPath);
     }
 
     private String[] getRefStrArray(Map refMap) {