diff options
14 files changed, 120 insertions, 120 deletions
diff --git a/template/2022-05-11/IDC字节跳动服务质量监测报告/test/freemarker/config-new.json b/template/2022-05-11/IDC字节跳动服务质量监测报告/test/freemarker/config-new.json index 8bc0e6a..2b81b25 100644 --- a/template/2022-05-11/IDC字节跳动服务质量监测报告/test/freemarker/config-new.json +++ b/template/2022-05-11/IDC字节跳动服务质量监测报告/test/freemarker/config-new.json @@ -117,42 +117,42 @@ "valueAlias": "xxxServiceTrafficChangingCurve" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceZATcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceZATcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceZAPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceZAPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceAZTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceAZTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceAZPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (has(client_ip_tags, '${Tenant}') OR has(server_ip_tags, '${Tenant}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceAZPacketRetrans" }, diff --git a/template/2022-05-11/IDC整体流量监测报告/test/freemarker/config-new.json b/template/2022-05-11/IDC整体流量监测报告/test/freemarker/config-new.json index 79e3b17..cee0c5c 100644 --- a/template/2022-05-11/IDC整体流量监测报告/test/freemarker/config-new.json +++ b/template/2022-05-11/IDC整体流量监测报告/test/freemarker/config-new.json @@ -101,62 +101,62 @@ "valueAlias": "appCompanyTrafficTop10Pre2nd" }, { - "querySql": "SELECT IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') GROUP BY granularity)", + "querySql": "SELECT IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') GROUP BY granularity)", "type": "table", "valueAlias": "TCPConnectionEstablishmentZA" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') GROUP BY granularity)", "type": "table", "valueAlias": "TCPConnectionEstablishmentZAPre1st" }, { - "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') GROUP BY granularity)", + "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') GROUP BY granularity)", "type": "table", "valueAlias": "packetRetransRateZA" }, { - "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') GROUP BY granularity)", + "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') GROUP BY granularity)", "type": "table", "valueAlias": "packetRetransRateZAPre1st" }, { - "querySql": "SELECT IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') GROUP BY granularity)", + "querySql": "SELECT IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') GROUP BY granularity)", "type": "table", "valueAlias": "TCPConnectionEstablishmentAZ" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') GROUP BY granularity)", "type": "table", "valueAlias": "TCPConnectionEstablishmentAZPre1st" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') GROUP BY granularity)", "type": "table", "valueAlias": "packetRetransRateAZ" }, { - "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') GROUP BY granularity)", + "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') GROUP BY granularity)", "type": "table", "valueAlias": "packetRetransRateAZPre1st" }, { - "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, SUM(sent_pkts + received_pkts) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", + "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, SUM(sent_pkts + received_pkts) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", "type": "table", "valueAlias": "TCPConnectionEstablishmentTop10AppCompany" }, { - "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, SUM(sent_pkts + received_pkts) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", + "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, SUM(sent_pkts + received_pkts) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", "type": "table", "valueAlias": "TCPConnectionEstablishmentTop10AppCompanyPre1st" }, { - "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", + "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", "type": "table", "valueAlias": "packetRetransRateTop10AppCompany" }, { - "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", + "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", "type": "table", "valueAlias": "packetRetransRateTop10AppCompanyPre1st" }, diff --git a/template/2022-05-23/出入口提供商报告/test/freemarker/config-new.json b/template/2022-05-23/出入口提供商报告/test/freemarker/config-new.json index 393ead8..b7af56b 100644 --- a/template/2022-05-23/出入口提供商报告/test/freemarker/config-new.json +++ b/template/2022-05-23/出入口提供商报告/test/freemarker/config-new.json @@ -87,42 +87,42 @@ "valueAlias": "xxxServiceTrafficChangingCurve" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceOut2inTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceOut2inTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceOut2inPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceOut2inPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceIn2outTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceIn2outTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceIn2outPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceIn2outPacketRetrans" }, diff --git a/template/2022-05-23/出入口整体流量监测报告/test/freemarker/config-new.json b/template/2022-05-23/出入口整体流量监测报告/test/freemarker/config-new.json index ef92817..e4b3e45 100644 --- a/template/2022-05-23/出入口整体流量监测报告/test/freemarker/config-new.json +++ b/template/2022-05-23/出入口整体流量监测报告/test/freemarker/config-new.json @@ -81,62 +81,62 @@ "valueAlias": "appCompanyTrafficTop10Pre2nd" }, { - "querySql": "SELECT IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' GROUP BY granularity)", + "querySql": "SELECT IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' GROUP BY granularity)", "type": "table", "valueAlias": "TCPConnectionEstablishmentOut2In" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' GROUP BY granularity)", "type": "table", "valueAlias": "TCPConnectionEstablishmentOut2InPre1st" }, { - "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' GROUP BY granularity)", + "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' GROUP BY granularity)", "type": "table", "valueAlias": "packetRetransRateOut2In" }, { - "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' GROUP BY granularity)", + "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' GROUP BY granularity)", "type": "table", "valueAlias": "packetRetransRateOut2InPre1st" }, { - "querySql": "SELECT IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' GROUP BY granularity)", + "querySql": "SELECT IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' GROUP BY granularity)", "type": "table", "valueAlias": "TCPConnectionEstablishmentIn2Out" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' GROUP BY granularity)", "type": "table", "valueAlias": "TCPConnectionEstablishmentIn2OutPre1st" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' GROUP BY granularity)", "type": "table", "valueAlias": "packetRetransRateIn2Out" }, { - "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' GROUP BY granularity)", + "querySql": "SELECT IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' GROUP BY granularity)", "type": "table", "valueAlias": "packetRetransRateIn2OutPre1st" }, { - "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", + "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", "type": "table", "valueAlias": "TCPConnectionEstablishmentTop10AppCompany" }, { - "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", + "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", "type": "table", "valueAlias": "TCPConnectionEstablishmentTop10AppCompanyPre1st" }, { - "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", + "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", "type": "table", "valueAlias": "packetRetransRateTop10AppCompany" }, { - "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", + "querySql": "SELECT app_company AS app_company, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company ORDER BY SUM(bytes) DESC LIMIT 10", "type": "table", "valueAlias": "packetRetransRateTop10AppCompanyPre1st" }, diff --git a/template/2022-08-30/IDC阿里巴巴服务质量监测报告/test/freemarker/config-应用程序.json b/template/2022-08-30/IDC阿里巴巴服务质量监测报告/test/freemarker/config-应用程序.json index a16700f..d4bab0c 100644 --- a/template/2022-08-30/IDC阿里巴巴服务质量监测报告/test/freemarker/config-应用程序.json +++ b/template/2022-08-30/IDC阿里巴巴服务质量监测报告/test/freemarker/config-应用程序.json @@ -107,42 +107,42 @@ "valueAlias":"xxxServiceTrafficChangingCurve" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceIn2outTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceIn2outTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceIn2outPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceIn2outPacketRetrans" }, diff --git a/template/2022-08-30/IDC阿里巴巴服务质量监测报告/test/freemarker/config-服务提供商.json b/template/2022-08-30/IDC阿里巴巴服务质量监测报告/test/freemarker/config-服务提供商.json index 453aff0..c7c394d 100644 --- a/template/2022-08-30/IDC阿里巴巴服务质量监测报告/test/freemarker/config-服务提供商.json +++ b/template/2022-08-30/IDC阿里巴巴服务质量监测报告/test/freemarker/config-服务提供商.json @@ -107,42 +107,42 @@ "valueAlias":"xxxServiceTrafficChangingCurve" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area NOT IN ('IDC') AND server_admin_area IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceIn2outTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceIn2outTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceIn2outPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_admin_area IN ('IDC') AND server_admin_area NOT IN ('IDC') AND (app_company IN ('${Provider}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceIn2outPacketRetrans" }, diff --git a/template/2022-08-30/出入口应用程序报告/test/freemarker/config.json b/template/2022-08-30/出入口应用程序报告/test/freemarker/config.json index ff9d7f5..bc6f23f 100644 --- a/template/2022-08-30/出入口应用程序报告/test/freemarker/config.json +++ b/template/2022-08-30/出入口应用程序报告/test/freemarker/config.json @@ -77,42 +77,42 @@ "valueAlias": "xxxServiceTrafficChangingCurve" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceOut2inTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceOut2inTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceOut2inPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceOut2inPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceIn2outTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceIn2outTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceIn2outPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceIn2outPacketRetrans" }, diff --git a/template/2022-12-30/Application Report/test/freemarker/config.json b/template/2022-12-30/Application Report/test/freemarker/config.json index 69a0ba1..536b72b 100644 --- a/template/2022-12-30/Application Report/test/freemarker/config.json +++ b/template/2022-12-30/Application Report/test/freemarker/config.json @@ -77,42 +77,42 @@ "valueAlias": "xxxServiceTrafficChangingCurve" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceOut2inTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceOut2inTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceOut2inPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceOut2inPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceIn2outTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceIn2outTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceIn2outPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (app IN ('${overviewApp}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceIn2outPacketRetrans" }, diff --git a/template/2023-03-02/IDC域名baidu.com报告/test/freemarker/config.json b/template/2023-03-02/IDC域名baidu.com报告/test/freemarker/config.json index 10cd28e..942658d 100644 --- a/template/2023-03-02/IDC域名baidu.com报告/test/freemarker/config.json +++ b/template/2023-03-02/IDC域名baidu.com报告/test/freemarker/config.json @@ -117,42 +117,42 @@ "valueAlias": "xxxServiceTrafficChangingCurve" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceOut2inTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceOut2inTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceOut2inPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceOut2inPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceIn2outTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceIn2outTcp" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", "type": "table", "valueAlias": "xxxServiceIn2outPacketRetrans" }, { - "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql": "SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", "type": "table", "valueAlias": "pre1stxxxServiceIn2outPacketRetrans" }, diff --git a/template/2023-03-02/IP标签报告/test/freemarker/config-idc.json b/template/2023-03-02/IP标签报告/test/freemarker/config-idc.json index 3090334..58d4656 100644 --- a/template/2023-03-02/IP标签报告/test/freemarker/config-idc.json +++ b/template/2023-03-02/IP标签报告/test/freemarker/config-idc.json @@ -122,42 +122,42 @@ "valueAlias":"xxxServiceTrafficChangingCurve" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inPacketRetrans" }, { - "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(establish_latency,0.5), 4) AS establish_latency_p50, ROUND(QUANTILE(establish_latency,0.9), 4) AS establish_latency_p90, ROUND(QUANTILE(establish_latency,0.99), 4) AS establish_latency_p99, ROUND(AVG(establish_latency), 4) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", + "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(establish_latency,0.5), 4) AS establish_latency_p50, ROUND(QUANTILE(establish_latency,0.9), 4) AS establish_latency_p90, ROUND(QUANTILE(establish_latency,0.99), 4) AS establish_latency_p99, ROUND(AVG(establish_latency), 4) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", "type":"table", "valueAlias":"xxxIPTagsTop10AppTCP" }, { - "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(establish_latency,0.5), 4) AS establish_latency_p50, ROUND(QUANTILE(establish_latency,0.9), 4) AS establish_latency_p90, ROUND(QUANTILE(establish_latency,0.99), 4) AS establish_latency_p99, ROUND(AVG(establish_latency), 4) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", + "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(establish_latency,0.5), 4) AS establish_latency_p50, ROUND(QUANTILE(establish_latency,0.9), 4) AS establish_latency_p90, ROUND(QUANTILE(establish_latency,0.99), 4) AS establish_latency_p99, ROUND(AVG(establish_latency), 4) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", "type":"table", "valueAlias":"pre1stxxxIPTagsTop10AppTCP" }, { - "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(pkt_retrans_percent,0.5), 4) as pkt_retrans_percent_p50, ROUND(QUANTILE(pkt_retrans_percent,0.9), 4) as pkt_retrans_percent_p90, ROUND(QUANTILE(pkt_retrans_percent,0.99), 4) as pkt_retrans_percent_p99, ROUND(AVG(pkt_retrans_percent), 4) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", + "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(pkt_retrans_percent,0.5), 4) as pkt_retrans_percent_p50, ROUND(QUANTILE(pkt_retrans_percent,0.9), 4) as pkt_retrans_percent_p90, ROUND(QUANTILE(pkt_retrans_percent,0.99), 4) as pkt_retrans_percent_p99, ROUND(AVG(pkt_retrans_percent), 4) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", "type":"table", "valueAlias":"xxxIPTagsTop10AppPacketRetrans" }, { - "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(pkt_retrans_percent,0.5), 4) as pkt_retrans_percent_p50, ROUND(QUANTILE(pkt_retrans_percent,0.9), 4) as pkt_retrans_percent_p90, ROUND(QUANTILE(pkt_retrans_percent,0.99), 4) as pkt_retrans_percent_p99, ROUND(AVG(pkt_retrans_percent), 4) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", + "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(pkt_retrans_percent,0.5), 4) as pkt_retrans_percent_p50, ROUND(QUANTILE(pkt_retrans_percent,0.9), 4) as pkt_retrans_percent_p90, ROUND(QUANTILE(pkt_retrans_percent,0.99), 4) as pkt_retrans_percent_p99, ROUND(AVG(pkt_retrans_percent), 4) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", "type":"table", "valueAlias":"pre1stxxxIPTagsTop10AppPacketRetrans" }, diff --git a/template/2023-03-02/IP标签报告/test/freemarker/config-出入口.json b/template/2023-03-02/IP标签报告/test/freemarker/config-出入口.json index ed24494..c523dca 100644 --- a/template/2023-03-02/IP标签报告/test/freemarker/config-出入口.json +++ b/template/2023-03-02/IP标签报告/test/freemarker/config-出入口.json @@ -122,42 +122,42 @@ "valueAlias":"xxxServiceTrafficChangingCurve" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inPacketRetrans" }, { - "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(establish_latency,0.5), 4) AS establish_latency_p50, ROUND(QUANTILE(establish_latency,0.9), 4) AS establish_latency_p90, ROUND(QUANTILE(establish_latency,0.99), 4) AS establish_latency_p99, ROUND(AVG(establish_latency), 4) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", + "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(establish_latency,0.5), 4) AS establish_latency_p50, ROUND(QUANTILE(establish_latency,0.9), 4) AS establish_latency_p90, ROUND(QUANTILE(establish_latency,0.99), 4) AS establish_latency_p99, ROUND(AVG(establish_latency), 4) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", "type":"table", "valueAlias":"xxxIPTagsTop10AppTCP" }, { - "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(establish_latency,0.5), 4) AS establish_latency_p50, ROUND(QUANTILE(establish_latency,0.9), 4) AS establish_latency_p90, ROUND(QUANTILE(establish_latency,0.99), 4) AS establish_latency_p99, ROUND(AVG(establish_latency), 4) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", + "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(establish_latency,0.5), 4) AS establish_latency_p50, ROUND(QUANTILE(establish_latency,0.9), 4) AS establish_latency_p90, ROUND(QUANTILE(establish_latency,0.99), 4) AS establish_latency_p99, ROUND(AVG(establish_latency), 4) AS establish_latency_avg, ROUND(MAX(establish_latency), 4) AS establish_latency_max, ROUND(MIN(establish_latency), 4) AS establish_latency_min FROM ( SELECT app_company, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", "type":"table", "valueAlias":"pre1stxxxIPTagsTop10AppTCP" }, { - "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(pkt_retrans_percent,0.5), 4) as pkt_retrans_percent_p50, ROUND(QUANTILE(pkt_retrans_percent,0.9), 4) as pkt_retrans_percent_p90, ROUND(QUANTILE(pkt_retrans_percent,0.99), 4) as pkt_retrans_percent_p99, ROUND(AVG(pkt_retrans_percent), 4) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", + "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(pkt_retrans_percent,0.5), 4) as pkt_retrans_percent_p50, ROUND(QUANTILE(pkt_retrans_percent,0.9), 4) as pkt_retrans_percent_p90, ROUND(QUANTILE(pkt_retrans_percent,0.99), 4) as pkt_retrans_percent_p99, ROUND(AVG(pkt_retrans_percent), 4) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", "type":"table", "valueAlias":"xxxIPTagsTop10AppPacketRetrans" }, { - "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(pkt_retrans_percent,0.5), 4) as pkt_retrans_percent_p50, ROUND(QUANTILE(pkt_retrans_percent,0.9), 4) as pkt_retrans_percent_p90, ROUND(QUANTILE(pkt_retrans_percent,0.99), 4) as pkt_retrans_percent_p99, ROUND(AVG(pkt_retrans_percent), 4) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", + "querySql":"SELECT app_company AS app_company, ROUND(QUANTILE(pkt_retrans_percent,0.5), 4) as pkt_retrans_percent_p50, ROUND(QUANTILE(pkt_retrans_percent,0.9), 4) as pkt_retrans_percent_p90, ROUND(QUANTILE(pkt_retrans_percent,0.99), 4) as pkt_retrans_percent_p99, ROUND(AVG(pkt_retrans_percent), 4) as pkt_retrans_percent_avg, ROUND(max(pkt_retrans_percent), 4) as pkt_retrans_percent_max, ROUND(min(pkt_retrans_percent), 4) as pkt_retrans_percent_min FROM ( SELECT app_company, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) AND app_company IN ( SELECT app_company FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND notEmpty(app_company) AND ((has(client_ip_tags,'${ipTag}') or has(server_ip_tags,'${ipTag}'))) GROUP BY app_company ORDER BY SUM(sent_bytes + received_bytes) DESC LIMIT 10) GROUP BY granularity, app_company) GROUP BY app_company LIMIT 10", "type":"table", "valueAlias":"pre1stxxxIPTagsTop10AppPacketRetrans" }, diff --git a/template/2023-03-02/IP流量报告/test/freemarker/config-idc.json b/template/2023-03-02/IP流量报告/test/freemarker/config-idc.json index 7662f19..cfd3ba5 100644 --- a/template/2023-03-02/IP流量报告/test/freemarker/config-idc.json +++ b/template/2023-03-02/IP流量报告/test/freemarker/config-idc.json @@ -107,42 +107,42 @@ "valueAlias":"xxxServiceTrafficChangingCurve" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (server_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (server_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inTcpAsServer" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (server_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (server_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inTcpAsServer" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (server_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (server_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inPacketRetransAsServer" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (server_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (server_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inPacketRetransAsServer" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (client_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (client_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inTcpAsClient" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (client_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (client_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inTcpAsClient" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (client_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (client_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inPacketRetransAsClient" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (client_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (client_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inPacketRetransAsClient" }, diff --git a/template/2023-03-02/IP流量报告/test/freemarker/config-出入口.json b/template/2023-03-02/IP流量报告/test/freemarker/config-出入口.json index 21edd37..8cae3a2 100644 --- a/template/2023-03-02/IP流量报告/test/freemarker/config-出入口.json +++ b/template/2023-03-02/IP流量报告/test/freemarker/config-出入口.json @@ -107,42 +107,42 @@ "valueAlias":"xxxServiceTrafficChangingCurve" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (server_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (server_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inTcpAsServer" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (server_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (server_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inTcpAsServer" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (server_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (server_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inPacketRetransAsServer" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (server_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (server_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inPacketRetransAsServer" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (client_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (client_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inTcpAsClient" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (client_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (client_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inTcpAsClient" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (client_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND (client_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inPacketRetransAsClient" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND (client_ip = '${ip}') GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND (client_ip = '${ip}') GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inPacketRetransAsClient" }, diff --git a/template/2023-03-02/出入口域名报告/test/freemarker/config.json b/template/2023-03-02/出入口域名报告/test/freemarker/config.json index bf007c0..9f06340 100644 --- a/template/2023-03-02/出入口域名报告/test/freemarker/config.json +++ b/template/2023-03-02/出入口域名报告/test/freemarker/config.json @@ -87,42 +87,42 @@ "valueAlias":"xxxServiceTrafficChangingCurve" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceOut2inPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'external' AND server_zone = 'internal' AND (domain = ('${domain}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceOut2inPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceIn2outTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(establish_latency,0.5), 4), 0) AS establish_latency_p50, IFNULL(ROUND(QUANTILE(establish_latency,0.9), 4), 0) AS establish_latency_p90, IFNULL(ROUND(QUANTILE(establish_latency,0.99), 4), 0) AS establish_latency_p99, IFNULL(ROUND(AVG(establish_latency), 4), 0) AS establish_latency_avg, IFNULL(ROUND(MAX(establish_latency), 4), 0) AS establish_latency_max, IFNULL(ROUND(MIN(establish_latency), 4), 0) AS establish_latency_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(AVG(tcp_rtt_ms)) AS establish_latency, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) AS granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceIn2outTcp" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} AND recv_time < ${endTime} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", "type":"table", "valueAlias":"xxxServiceIn2outPacketRetrans" }, { - "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND decoded_path IN ('IPv4_TCP', 'IPv6_TCP') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", + "querySql":"SELECT SUM(bytes) AS all_bytes, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.5), 4), 0) as pkt_retrans_percent_p50, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.9), 4), 0) as pkt_retrans_percent_p90, IFNULL(ROUND(QUANTILE(pkt_retrans_percent,0.99), 4), 0) as pkt_retrans_percent_p99, IFNULL(ROUND(AVG(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_avg, IFNULL(ROUND(max(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_max, IFNULL(ROUND(min(pkt_retrans_percent), 4), 0) as pkt_retrans_percent_min FROM ( SELECT SUM(sent_bytes + received_bytes) AS bytes, ROUND(SUM(tcp_c2s_rtx_pkts + tcp_s2c_rtx_pkts)/ SUM(sent_pkts + received_pkts),4) AS pkt_retrans_percent, toDateTime(toStartOfInterval(toDateTime(recv_time),INTERVAL 300 SECOND)) as granularity FROM session_record_cn WHERE recv_time >= ${startTime} - ${timeDiff} AND recv_time < ${endTime} - ${timeDiff} AND ip_protocol IN ('tcp') AND client_zone = 'internal' AND server_zone = 'external' AND (domain = ('${domain}')) GROUP BY granularity)", "type":"table", "valueAlias":"pre1stxxxServiceIn2outPacketRetrans" }, |