126 lines
32 KiB
SQL
126 lines
32 KiB
SQL
--Q01.CK DateTime
|
||
select toDateTime(common_recv_time) as common_recv_time from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) limit 20
|
||
--Q02.Standard DateTime
|
||
select FROM_UNIXTIME(common_recv_time) as common_recv_time from session_record where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) limit 20
|
||
--Q03.count(1)
|
||
select count(1) from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)
|
||
--Q04.count(*)
|
||
select count(*) from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end)
|
||
--Q05.UDF APPROX_COUNT_DISTINCT_DS_HLL
|
||
SELECT policy_id, APPROX_COUNT_DISTINCT_DS_HLL(isp) as num FROM proxy_event_hits_log where __time >= @start and __time < @end and policy_id=0 group by policy_id
|
||
--Q06.UDF TIME_FLOOR_WITH_FILL
|
||
select TIME_FLOOR_WITH_FILL(common_recv_time,'PT5M','previous') as stat_time from session_record where common_recv_time > toDateTime(@start) and common_recv_time < toDateTime(@end) group by stat_time
|
||
--Q07.UDF GEO IP
|
||
select IP_TO_GEO(common_client_ip) as geo,IP_TO_CITY(common_server_ip) as city,IP_TO_COUNTRY(common_server_ip) as country from session_record limit 10
|
||
--Q08.Special characters
|
||
select * from session_record where (common_protocol_label ='/$' or common_client_ip like'%') limit 10
|
||
--Q09.Federation Query
|
||
select * from (select FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(common_recv_time,'PT5M','zero')) as stat_time from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) group by stat_time order by stat_time asc)
|
||
--Q10.Closed session Record Logs
|
||
select * from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) AND @common_filter order by common_recv_time desc limit 20
|
||
--Q11.Interim Session Record Logs
|
||
select * from interim_session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) AND @common_filter order by common_recv_time desc limit 20
|
||
--Q12.Transaction Record Logs
|
||
select * from transaction_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) order by common_recv_time desc limit 20
|
||
--Q13.Security Event Logs
|
||
select * from security_event where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) AND @common_filter order by common_recv_time desc limit 0,20
|
||
--Q14.Proxy Event Logs
|
||
select * from proxy_event where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) order by common_recv_time desc limit 0,20
|
||
--Q15.Radius Record Logs
|
||
select * from radius_record where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) order by common_recv_time desc limit 0,20
|
||
--Q16.GTPC Record Logs
|
||
select * from gtpc_record where common_recv_time >= UNIX_TIMESTAMP(@start) and common_recv_time< UNIX_TIMESTAMP(@end) order by common_recv_time desc limit 0,20
|
||
--Q17.Closed session record with fields
|
||
select toDateTime(common_recv_time) AS common_recv_time, common_log_id, common_subscriber_id, common_imei, common_imsi, common_phone_number, common_client_ip, common_internal_ip, common_client_port, common_l4_protocol, common_address_type, common_server_ip, common_server_port, common_external_ip, common_direction, common_sled_ip, common_client_location, common_client_asn, common_server_location, common_server_asn, common_sessions, common_c2s_pkt_num, common_s2c_pkt_num, common_c2s_byte_num, common_s2c_byte_num, common_schema_type, common_device_id, common_device_group, common_app_behavior, common_app_label, common_tunnels, common_protocol_label, common_userdefine_app_name, common_l7_protocol, common_service_category, toDateTime(common_start_time) AS common_start_time, toDateTime(common_end_time) AS common_end_time, common_establish_latency_ms, common_con_duration_ms, common_stream_dir, common_stream_trace_id, common_c2s_ipfrag_num, common_s2c_ipfrag_num, common_c2s_tcp_lostlen, common_s2c_tcp_lostlen, common_c2s_tcp_unorder_num, common_s2c_tcp_unorder_num, common_c2s_pkt_retrans, common_s2c_pkt_retrans, common_c2s_byte_retrans, common_s2c_byte_retrans, common_tcp_client_isn, common_tcp_server_isn, toDateTime(common_processing_time) AS common_processing_time, toDateTime(common_ingestion_time) AS common_ingestion_time, http_url, http_host, http_domain, http_request_line, http_response_line, http_request_content_length, http_request_content_type, http_response_content_length, http_response_content_type, http_request_body, http_response_body, http_cookie, http_referer, http_user_agent, http_set_cookie, http_version, http_response_latency_ms, http_session_duration_ms, http_action_file_size, mail_protocol_type, mail_account, mail_from_cmd, mail_to_cmd, mail_from, mail_to, mail_cc, mail_bcc, mail_subject, mail_attachment_name, mail_eml_file, dns_message_id, dns_qr, dns_opcode, dns_aa, dns_tc, dns_rd, dns_ra, dns_rcode, dns_qdcount, dns_ancount, dns_nscount, dns_arcount, dns_qname, dns_qtype, dns_qclass, dns_sub, dns_response_latency_ms, ssl_sni, ssl_cn, ssl_pinningst, ssl_intercept_state, ssl_server_side_latency, ssl_client_side_latency, ssl_server_side_version, ssl_client_side_version, ssl_cert_verify, ssl_error, ssl_con_latency_ms, ssl_ja3_hash, ssl_cert_issuer, ssl_cert_subject, quic_version, quic_sni, quic_user_agent, ftp_account, ftp_url, ftp_content, ftp_link_type, app_extra_info, sip_call_id, sip_originator_description, sip_responder_description, sip_user_agent, sip_server, sip_originator_sdp_connect_ip, sip_originator_sdp_media_port, sip_originator_sdp_media_type, sip_originator_sdp_content, sip_responder_sdp_connect_ip, sip_responder_sdp_media_port, sip_responder_sdp_media_type, sip_responder_sdp_content, sip_duration_s, sip_bye, rtp_payload_type_c2s, rtp_payload_type_s2c, rtp_pcap_path, ssh_version, ssh_auth_success, ssh_client_version, ssh_server_version, ssh_cipher_alg, ssh_mac_alg, ssh_compression_alg, ssh_kex_alg, ssh_host_key_alg, ssh_host_key, ssh_hassh, stratum_cryptocurrency, stratum_mining_pools, stratum_mining_program from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) limit 20
|
||
--Q18.Interim session record with fields
|
||
SELECT toDateTime(common_recv_time) AS common_recv_time, common_log_id, common_subscriber_id, common_imei, common_imsi, common_phone_number, common_client_ip, common_internal_ip, common_client_port, common_l4_protocol, common_address_type, common_server_ip, common_server_port, common_external_ip, common_direction, common_sled_ip, common_client_location, common_client_asn, common_server_location, common_server_asn, common_sessions, common_c2s_pkt_num, common_s2c_pkt_num, common_c2s_byte_num, common_s2c_byte_num, common_c2s_pkt_diff, common_s2c_pkt_diff, common_c2s_byte_diff, common_s2c_byte_diff, common_schema_type, common_device_id, common_device_group, common_app_behavior, common_app_label, common_tunnels, common_protocol_label, common_l7_protocol, common_service_category, toDateTime(common_start_time) AS common_start_time, toDateTime(common_end_time) AS common_end_time, common_establish_latency_ms, common_con_duration_ms, common_stream_dir, common_stream_trace_id, common_c2s_ipfrag_num, common_s2c_ipfrag_num, common_c2s_tcp_lostlen, common_s2c_tcp_lostlen, common_c2s_tcp_unorder_num, common_s2c_tcp_unorder_num, common_c2s_pkt_retrans, common_s2c_pkt_retrans, common_c2s_byte_retrans, common_s2c_byte_retrans, common_tcp_client_isn, common_tcp_server_isn, toDateTime(common_processing_time) AS common_processing_time, toDateTime(common_ingestion_time) AS common_ingestion_time, http_url, http_host, http_domain, http_request_line, http_response_line, http_request_content_length, http_request_content_type, http_response_content_length, http_response_content_type, http_request_body, http_response_body, http_cookie, http_referer, http_user_agent, http_set_cookie, http_version, http_response_latency_ms, http_session_duration_ms, http_action_file_size, mail_protocol_type, mail_account, mail_from_cmd, mail_to_cmd, mail_from, mail_to, mail_cc, mail_bcc, mail_subject, mail_attachment_name, mail_eml_file, dns_message_id, dns_qr, dns_opcode, dns_aa, dns_tc, dns_rd, dns_ra, dns_rcode, dns_qdcount, dns_ancount, dns_nscount, dns_arcount, dns_qname, dns_qtype, dns_qclass, dns_sub, dns_response_latency_ms, ssl_sni, ssl_cn, ssl_pinningst, ssl_intercept_state, ssl_server_side_latency, ssl_client_side_latency, ssl_server_side_version, ssl_client_side_version, ssl_cert_verify, ssl_error, ssl_con_latency_ms, ssl_ja3_hash, ssl_cert_issuer, ssl_cert_subject, quic_version, quic_sni, quic_user_agent, ftp_account, ftp_url, ftp_content, ftp_link_type, app_extra_info, sip_call_id, sip_originator_description, sip_responder_description, sip_user_agent, sip_server, sip_originator_sdp_connect_ip, sip_originator_sdp_media_port, sip_originator_sdp_media_type, sip_originator_sdp_content, sip_responder_sdp_connect_ip, sip_responder_sdp_media_port, sip_responder_sdp_media_type, sip_responder_sdp_content, sip_duration_s, sip_bye, rtp_payload_type_c2s, rtp_payload_type_s2c, rtp_pcap_path, rtp_originator_dir, ssh_version, ssh_auth_success, ssh_client_version, ssh_server_version, ssh_cipher_alg, ssh_mac_alg, ssh_compression_alg, ssh_kex_alg, ssh_host_key_alg, ssh_host_key, ssh_hassh, stratum_cryptocurrency, stratum_mining_pools, stratum_mining_program FROM interim_session_record where common_recv_time >= @start and common_recv_time < @end order by common_recv_time desc limit 100000
|
||
--Q19.Security Event Logs with fields
|
||
SELECT toDateTime(common_recv_time) AS common_recv_time, common_log_id, common_policy_id, common_subscriber_id, common_imei, common_imsi, common_phone_number, common_client_ip, common_internal_ip, common_client_port, common_l4_protocol, common_address_type, common_server_ip, common_server_port, common_external_ip, common_action, common_direction, common_sled_ip, common_client_location, common_client_asn, common_server_location, common_server_asn, common_sessions, common_c2s_pkt_num, common_s2c_pkt_num, common_c2s_byte_num, common_s2c_byte_num, common_schema_type, common_device_id, common_device_group, common_app_behavior, common_app_label, common_tunnels, common_protocol_label, common_userdefine_app_name, common_l7_protocol, common_service_category, toDateTime(common_start_time) AS common_start_time, toDateTime(common_end_time) AS common_end_time, common_establish_latency_ms, common_con_duration_ms, common_stream_dir, common_stream_error, common_stream_trace_id, common_packet_capture_file, common_tcp_client_isn, common_tcp_server_isn, toDateTime(common_processing_time) AS common_processing_time, toDateTime(common_ingestion_time) AS common_ingestion_time, common_mirrored_pkts, common_mirrored_bytes, http_url, http_host, http_domain, http_request_line, http_response_line, http_request_content_length, http_request_content_type, http_response_content_length, http_response_content_type, http_request_body, http_response_body, http_cookie, http_referer, http_user_agent, http_set_cookie, http_version, http_response_latency_ms, http_action_file_size, http_session_duration_ms, mail_protocol_type, mail_account, mail_from_cmd, mail_to_cmd, mail_from, mail_to, mail_cc, mail_bcc, mail_subject, mail_attachment_name, dns_message_id, dns_qr, dns_opcode, dns_aa, dns_tc, dns_rd, dns_ra, dns_rcode, dns_qdcount, dns_ancount, dns_nscount, dns_arcount, dns_qname, dns_qtype, dns_qclass, dns_sub, ssl_sni, ssl_san, ssl_cn, ssl_pinningst, ssl_intercept_state, ssl_passthrough_reason, ssl_server_side_latency, ssl_client_side_latency, ssl_server_side_version, ssl_client_side_version, ssl_cert_verify, ssl_error, ssl_con_latency_ms, ssl_ja3_hash, ssl_cert_issuer, ssl_cert_subject, quic_version, quic_sni, quic_user_agent, ftp_account, ftp_url, ftp_content, ftp_link_type, app_extra_info, sip_call_id, sip_originator_description, sip_responder_description, sip_user_agent, sip_server, sip_originator_sdp_connect_ip, sip_originator_sdp_media_port, sip_originator_sdp_media_type, sip_originator_sdp_content, sip_responder_sdp_connect_ip, sip_responder_sdp_media_port, sip_responder_sdp_media_type, sip_responder_sdp_content, sip_duration_s, sip_bye, rtp_payload_type_c2s, rtp_payload_type_s2c, rtp_pcap_path, ssh_version, ssh_auth_success, ssh_client_version, ssh_server_version, ssh_cipher_alg, ssh_mac_alg, ssh_compression_alg, ssh_kex_alg, ssh_host_key_alg, ssh_host_key, ssh_hassh, stratum_cryptocurrency, stratum_mining_pools, stratum_mining_program from security_event where common_recv_time >= @start and common_recv_time < @end order by common_recv_time desc limit 100000
|
||
--Q20.Radius ON/OFF Logs For Frame IP
|
||
select framed_ip, arraySlice(groupUniqArray(concat(toString(event_timestamp),':', if(acct_status_type=1,'start','stop'))),1,100000) as timeseries from radius_onff_log where event_timestamp >=toDateTime(@start) and event_timestamp <toDateTime(@end) group by framed_ip limit 20
|
||
--Q21.Radius ON/OFF Logs For Account
|
||
select account, arraySlice(groupUniqArray(concat(toString(event_timestamp),':', if(acct_status_type=1,'start','stop'))),1,100000) as timeseries from radius_onff_log where event_timestamp >= toDateTime(@start) and event_timestamp < toDateTime(@end) group by account
|
||
--Q22.Radius ON/OFF Logs total Account number
|
||
select count(distinct(framed_ip)) as active_ip_num , sum(acct_session_time) as online_duration from (select any(framed_ip) as framed_ip ,max(acct_session_time) as acct_session_time from radius_onff_log where account='000jS' and event_timestamp >= toDateTime(@start) and event_timestamp < toDateTime(@end) group by acct_session_id)
|
||
--Q23.Radius ON/OFF Logs Account Access Detail
|
||
select max(if(acct_status_type=1,event_timestamp,0)) as start_time,max(if(acct_status_type=2,event_timestamp,0)) as end_time, any(framed_ip) as ip,max(acct_session_time) as online_duration from radius_onff_log where event_timestamp >= toDateTime(@start) and event_timestamp < toDateTime(@end) group by acct_session_id order by start_time desc limit 200
|
||
--Q24.Report for Client IP
|
||
select common_client_ip, count(*) as sessions from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@end)) group by common_client_ip order by sessions desc limit 0,100
|
||
--Q25.Report for Server IP
|
||
select common_server_ip, count(*) as sessions from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start)) group by common_server_ip order by sessions desc limit 0,100
|
||
--Q26.Report for SSL SNI
|
||
select ssl_sni, count(*) as sessions from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start)) group by ssl_sni order by sessions desc limit 0,100
|
||
--Q27.Report for SSL APP
|
||
select common_app_label as applicaiton, count(*) as sessions from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start)) group by applicaiton order by sessions desc limit 0,100
|
||
--Q28.Report for Domains
|
||
select http_domain AS domain,SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes,SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes,SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes FROM session_record WHERE common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and notEmpty(domain) GROUP BY domain ORDER BY bytes DESC LIMIT 100
|
||
--Q29.Report for Domains with unique Client IP
|
||
select toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300) as stat_time, http_domain, uniq (common_client_ip) as nums from session_record where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and http_domain in (select http_domain from session_record where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and notEmpty(http_domain) group by http_domain order by SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) desc limit 10 ) group by toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300), http_domain order by stat_time asc limit 500
|
||
--Q30. Report for HTTP Host
|
||
SELECT http_host as host, SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes,SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes,SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes FROM session_record WHERE common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start)) and notEmpty(http_host) GROUP BY host ORDER BY bytes DESC limit 100 union all SELECT 'totals' as host, SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes, SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes, SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start)) and notEmpty(http_host)
|
||
--Q31.Report for HTTP/HTTPS URLS with Sessions
|
||
SELECT http_url AS url,count(*) AS sessions FROM proxy_event WHERE common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and notEmpty(http_url) GROUP BY url ORDER BY sessions DESC LIMIT 100
|
||
--Q32.Report for HTTP/HTTPS URLS with UNIQUE Client IP
|
||
select toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300) as stat_time, http_url, count(distinct(common_client_ip)) as nums from proxy_event where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and http_url IN (select http_url from proxy_event where common_recv_time >= toStartOfDay(toDateTime(@start))-86400 AND common_recv_time < toStartOfDay(toDateTime(@start)) and notEmpty(http_url) group by http_url order by count(*) desc limit 10 ) group by toDateTime(intDiv(toUInt32(toDateTime(toDateTime(common_recv_time))), 300)*300), http_url order by stat_time asc limit 500
|
||
--Q33.Report for Subscriber ID with Sessions
|
||
select common_subscriber_id as user, count(*) as sessions from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start)) and notEmpty(user) group by common_subscriber_id order by sessions desc limit 0,100
|
||
--Q34.Report for Subscriber ID with Bandwidth
|
||
SELECT common_subscriber_id as user,SUM(coalesce(common_c2s_byte_num, 0)) AS sent_bytes,SUM(coalesce(common_s2c_byte_num, 0)) AS received_bytes,SUM(coalesce(common_c2s_byte_num, 0)+coalesce(common_s2c_byte_num, 0)) AS bytes FROM session_record WHERE common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start)) and notEmpty(user) GROUP BY user ORDER BY bytes DESC LIMIT 100
|
||
--Q35.Report Unique Endpoints
|
||
select uniq(common_client_ip) as "Client IP",uniq(common_server_ip) as "Server IP",uniq(common_internal_ip) as "Internal IP",uniq(common_external_ip) as "External IP",uniq(http_domain) as "Domain",uniq(ssl_sni) as "SNI" from session_record where common_recv_time>= toStartOfDay(toDateTime(@start))-604800 and common_recv_time< toStartOfDay(toDateTime(@start))
|
||
--Q36.TopN Optimizer
|
||
SELECT http_url AS url, SUM(common_sessions) AS sessions FROM session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND notEmpty(http_url) GROUP BY http_url ORDER BY sessions DESC limit 10
|
||
--Q37.All Security Event Hits Trend by 5min B
|
||
select DATE_FORMAT(FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(__time)/300)*300),'%Y-%m-%d %H:%i:%s') as start_time, sum(hits) as hits from security_event_hits_log where __time >= @start and __time < @end group by DATE_FORMAT(FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(__time)/300)*300),'%Y-%m-%d %H:%i:%s') limit 10000
|
||
--Q38.Security Event Hit Time（first and last time) B
|
||
select policy_id, DATE_FORMAT(min(__time) ,'%Y-%m-%d %H:%i:%s') as first_used, DATE_FORMAT(max(__time) ,'%Y-%m-%d %H:%i:%s') as last_used from security_event_hits_log where policy_id in (0) group by policy_id
|
||
--Q39.All Proxy Event Hits Trend by 5min B
|
||
select FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(__time)/300)*300) as start_time, sum(hits) as hits from proxy_event_hits_log where __time >= @start and __time < @end group by FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(__time)/300)*300) limit 10000
|
||
--Q40.Proxy Event Hit Time（first and last time) B
|
||
select policy_id, DATE_FORMAT(min(__time) ,'%Y-%m-%d %H:%i:%s') as first_used, DATE_FORMAT(max(__time) ,'%Y-%m-%d %H:%i:%s') as last_used from proxy_event_hits_log where policy_id in (0) group by policy_id
|
||
--Q41.Traffic Composition Protocol Tree Trend
|
||
(SELECT TIME_FORMAT(MILLIS_TO_TIMESTAMP( 1000 * TIME_FLOOR_WITH_FILL(TIMESTAMP_TO_MILLIS(__time)/1000, 'PT30S', 'zero')), 'yyyy-MM-dd HH:mm:ss') as stat_time, protocol_id as type, sum(c2s_byte_num + s2c_byte_num) as bytes from traffic_protocol_stat_log where __time >= @start AND __time < @end and protocol_id = 'ETHERNET' group by TIME_FORMAT(MILLIS_TO_TIMESTAMP( 1000 * TIME_FLOOR_WITH_FILL(TIMESTAMP_TO_MILLIS(__time)/1000, 'PT30S', 'zero')), 'yyyy-MM-dd HH:mm:ss'), protocol_id order by stat_time asc) union all (SELECT TIME_FORMAT(MILLIS_TO_TIMESTAMP( 1000 * TIME_FLOOR_WITH_FILL(TIMESTAMP_TO_MILLIS(__time)/1000, 'PT30S', 'zero')), 'yyyy-MM-dd HH:mm:ss') as stat_time, protocol_id as type, sum(c2s_byte_num + s2c_byte_num) as bytes from traffic_protocol_stat_log where __time >= @start AND __time < @end and protocol_id like CONCAT('ETHERNET','.%') and LENGTH(protocol_id) = LENGTH(REPLACE(protocol_id,'.','')) + 1 + 0 group by TIME_FORMAT(MILLIS_TO_TIMESTAMP( 1000 * TIME_FLOOR_WITH_FILL(TIMESTAMP_TO_MILLIS(__time)/1000, 'PT30S', 'zero')), 'yyyy-MM-dd HH:mm:ss'), protocol_id order by stat_time asc)
|
||
--Q42.Traffic Metrics Security Action Hits Trend
|
||
select FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'PT1800S','zero')) as statisticTime, sum(default_in_bytes + default_out_bytes) as default_bytes, sum(default_in_packets + default_out_packets) as default_packets, sum(default_conn_num) as default_sessions, sum(allow_in_bytes + allow_out_bytes) as allow_bytes, sum(allow_in_packets + allow_out_packets) as allow_packets, sum(allow_conn_num) as allow_sessions, sum(deny_in_bytes + deny_out_bytes) as deny_bytes, sum(deny_in_packets + deny_out_packets) as deny_packets, sum(deny_conn_num) as deny_sessions, sum(monitor_in_bytes + monitor_out_bytes) as monitor_bytes, sum(monitor_in_packets + monitor_out_packets) as monitor_packets, sum(monitor_conn_num) as monitor_sessions, sum(intercept_in_bytes + intercept_out_bytes) as intercept_bytes, sum(intercept_in_packets + intercept_out_packets) as intercept_packets, sum(intercept_conn_num) as intercept_sessions from traffic_metrics_log where __time >= @start and __time < @end group by FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'PT1800S','zero')) limit 100000
|
||
--Q43.Traffic Metrics Proxy Action Hits Trend
|
||
SELECT FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'PT1800S','zero')) AS statisticTime,SUM(intcp_allow_num) AS intercept_allow_conn_num,SUM(intcp_mon_num) AS intercept_monitor_conn_num,SUM(intcp_deny_num) AS intercept_deny_conn_num,SUM(intcp_rdirt_num) AS intercept_redirect_conn_num,SUM(intcp_repl_num) AS intercept_replace_conn_num,SUM(intcp_hijk_num) AS intercept_hijack_conn_num,SUM(intcp_ins_num) AS intercept_insert_conn_num FROM traffic_metrics_log WHERE __time >= @start AND __time < @end GROUP BY FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), 'PT1800S', 'zero')) LIMIT 100000
|
||
--Q44.Traffic Statistics(Metrics02)
|
||
select FROM_UNIXTIME(stat_time) as max_active_date_by_sessions, total_live_sessions as max_live_sessions from ( select stat_time, sum(live_sessions) as total_live_sessions from ( select TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), 'P1D') as stat_time, device_id, avg(established_conn_num) as live_sessions from traffic_metrics_log where __time >= @start and __time<@end group by TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), 'P1D'), device_id) group by stat_time order by total_live_sessions desc limit 1 )
|
||
--Q45.Traffic Summary(Bandwidth Trend)
|
||
select * from ( select DATE_FORMAT(FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'PT1h','zero')),'%Y-%m-%d %H:%i:%s') as stat_time,'traffic_in_bytes' as type, sum(total_in_bytes) as bytes from traffic_metrics_log where __time >= @start and __time < @end group by DATE_FORMAT(FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'PT1h','zero')),'%Y-%m-%d %H:%i:%s'), 'traffic_in_bytes' union all select DATE_FORMAT(FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'PT1h','zero')),'%Y-%m-%d %H:%i:%s') as stat_time,'traffic_out_bytes' as type,sum(total_out_bytes) as bytes from traffic_metrics_log where __time >= @start and __time < @end group by DATE_FORMAT(FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'PT1h','zero')),'%Y-%m-%d %H:%i:%s'),'traffic_out_bytes' ) order by stat_time asc limit 100000
|
||
--Q46.Traffic Summary(Sessions Trend)
|
||
select DATE_FORMAT(FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'PT1h','zero')),'%Y-%m-%d %H:%i:%s') as stat_time, 'total_conn_num' as type, sum(new_conn_num) as sessions from traffic_metrics_log where __time >= @start and __time < @end group by DATE_FORMAT(FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'PT1h','zero')),'%Y-%m-%d %H:%i:%s'), 'total_conn_num' order by stat_time asc limit 10000
|
||
--Q47.Domain Baidu.com Metrics
|
||
select FROM_UNIXTIME(min(common_recv_time)) as "First Seen" , FROM_UNIXTIME(max(common_recv_time)) as "Last Seen" , median(http_response_latency_ms) as "Server Processing Time Median(ms)", count(1) as Responses,any(common_server_location) as Location from session_record WHERE common_recv_time >= toDateTime(@start) AND common_recv_time < toDateTime(@end) AND http_domain='baidu.com'
|
||
--Q48.TIME_FLOOR_WITH_FILL 01
|
||
select "Device Group" as "Device Group" ,"Data Center" as "Data Center" ,FROM_UNIXTIME("End Time") as "End Time" , sum("counter") as "counter" from (select common_device_group as "Device Group" ,common_data_center as "Data Center" ,TIME_FLOOR_WITH_FILL (common_end_time,'PT1H','zero') as "End Time" ,count(common_log_id) as "counter" from session_record where common_recv_time >= toDateTime(@start) and common_recv_time< toDateTime(@end) group by "Device Group","Data Center","End Time") group by "Device Group" ,"Data Center" ,"End Time" order by "End Time" asc limit 5
|
||
--Q49.TIME_FLOOR_WITH_FILL 02
|
||
select FROM_UNIXTIME("End Time") as "End Time" , sum("counter") as "counter" from (select common_device_group as "Device Group" ,common_data_center as "Data Center" ,TIME_FLOOR_WITH_FILL (common_end_time,'PT1H','zero') as "End Time" ,count(common_log_id) as "counter" ,count(http_domain) as "HTTP.Domain" from security_event where ((common_recv_time >= toDateTime('2021-10-19 00:00:00') and common_recv_time < toDateTime('2021-10-20 00:00:00')) ) AND ( ( common_action = 2 ) ) group by "Device Group","Data Center","End Time") group by "End Time" order by "End Time" asc
|
||
--Q50.CONVERT_TZ (Druid) 01
|
||
SELECT CONVERT_TZ('2019-09-09 09:09:09','GMT','MET') as test_time from proxy_event_hits_log limit 1
|
||
--Q51.CONVERT_TZ (Druid) 02
|
||
SELECT CONVERT_TZ('2019-09-09 09:09:09','Europe/London','America/New_York') as test_time from proxy_event_hits_log limit 1
|
||
--Q52.CONVERT_TZ (Druid) 03
|
||
SELECT CONVERT_TZ(now(),'GMT','America/New_York') as test_time from proxy_event_hits_log limit 1
|
||
--Q53.CONVERT_TZ (clickhouse) 01
|
||
SELECT CONVERT_TZ('2019-09-09 09:09:09','GMT','MET') as test_time from session_record limit 1
|
||
--Q54.CONVERT_TZ (clickhouse) 02
|
||
SELECT CONVERT_TZ('2019-09-09 09:09:09','Europe/London','America/New_York') as test_time from session_record limit 1
|
||
--Q55.CONVERT_TZ (clickhouse) 03
|
||
SELECT CONVERT_TZ(now(),'GMT','America/New_York') as test_time from session_record limit 1
|
||
--Q56.CONVERT_TZ (hbase) 01
|
||
SELECT CONVERT_TZ('2019-09-09 09:09:09','GMT','MET') as test_time from report_result limit 1
|
||
--Q57.CONVERT_TZ (hbase) 02
|
||
SELECT CONVERT_TZ('2019-09-09 09:09:09','Europe/London','America/New_York') as test_time from report_result limit 1
|
||
--Q58.CONVERT_TZ (hbase) 03
|
||
SELECT CONVERT_TZ(now(),'GMT','America/New_York') as test_time from report_result limit 1
|
||
--Q59.CONVERT_TZ (elasticsearch)
|
||
SELECT CONVERT_TZ('2019-09-09 09:09:09','Europe/London','America/New_York') as time from report_result limit 1
|
||
--Q60.Authentication failed(code 516)
|
||
SELECT toDateTime(common_recv_time) AS common_recv_time, common_log_id, common_subscriber_id, common_imei, common_imsi, common_phone_number, common_client_ip, common_internal_ip, common_client_port, common_l4_protocol, common_address_type, common_server_ip, common_server_port, common_external_ip, common_direction, common_sled_ip, common_client_location, common_client_asn, common_server_location, common_server_asn, common_sessions, common_c2s_pkt_num, common_s2c_pkt_num, common_c2s_byte_num, common_s2c_byte_num, common_c2s_pkt_diff, common_s2c_pkt_diff, common_c2s_byte_diff, common_s2c_byte_diff, common_schema_type, common_device_id, common_device_group, common_app_behavior, common_app_label, common_tunnels, common_protocol_label, common_l7_protocol, common_service_category, toDateTime(common_start_time) AS common_start_time, toDateTime(common_end_time) AS common_end_time, common_establish_latency_ms, common_con_duration_ms, common_stream_dir, common_stream_trace_id, common_c2s_ipfrag_num, common_s2c_ipfrag_num, common_c2s_tcp_lostlen, common_s2c_tcp_lostlen, common_c2s_tcp_unorder_num, common_s2c_tcp_unorder_num, common_c2s_pkt_retrans, common_s2c_pkt_retrans, common_c2s_byte_retrans, common_s2c_byte_retrans, common_tcp_client_isn, common_tcp_server_isn, toDateTime(common_processing_time) AS common_processing_time, http_url, http_host, http_domain, http_request_content_length, http_request_content_type, http_response_content_length, http_response_content_type, http_request_body, http_response_body, http_cookie, http_referer, http_user_agent, http_set_cookie, http_version, http_response_latency_ms, http_session_duration_ms, http_action_file_size, mail_protocol_type, mail_account, mail_from_cmd, mail_to_cmd, mail_from, mail_to, mail_cc, mail_bcc, mail_subject, mail_attachment_name, mail_eml_file, dns_message_id, dns_qr, dns_opcode, dns_aa, dns_tc, dns_rd, dns_ra, dns_rcode, dns_qdcount, dns_ancount, dns_nscount, dns_arcount, dns_qname, dns_qtype, dns_qclass, dns_sub, ssl_sni, ssl_cn, ssl_pinningst, ssl_intercept_state, ssl_server_side_latency, ssl_client_side_latency, ssl_server_side_version, ssl_client_side_version, ssl_cert_verify, ssl_error, ssl_con_latency_ms, ssl_ja3_hash, ssl_cert_issuer, ssl_cert_subject, quic_version, quic_sni, quic_user_agent, ftp_account, ftp_url, ftp_content, ftp_link_type, app_extra_info, sip_call_id, sip_originator_description, sip_responder_description, sip_user_agent, sip_server, sip_originator_sdp_connect_ip, sip_originator_sdp_media_port, sip_originator_sdp_media_type, sip_originator_sdp_content, sip_responder_sdp_connect_ip, sip_responder_sdp_media_port, sip_responder_sdp_media_type, sip_responder_sdp_content, sip_duration_s, sip_bye, rtp_payload_type_c2s, rtp_payload_type_s2c, rtp_pcap_path, rtp_originator_dir, ssh_version, ssh_auth_success, ssh_client_version, ssh_server_version, ssh_cipher_alg, ssh_mac_alg, ssh_compression_alg, ssh_kex_alg, ssh_host_key_alg, ssh_host_key, ssh_hassh, stratum_cryptocurrency, stratum_mining_pools, stratum_mining_program FROM interim_session_record AS interim_session_record WHERE common_recv_time >= toUnixTimestamp(@start) AND common_recv_time < toUnixTimestamp(@end) ORDER BY common_recv_time DESC LIMIT 43233, 20
|
||
--Q61.Function MAX_DURATION
|
||
SELECT destination_ip, IP_TO_GEO(destination_ip) AS destination_geo, MAX_DURATION(end_time,600) AS max_duration, any(destination_country) AS destination_country, groupUniqArray(arrayJoin(splitByString(',',source_country_list))) AS source_coutries,max(bit_rate) AS max_bit_rate,max(packet_rate) AS max_packet_rate,max(session_rate) AS max_session_rate,min(start_time) AS first_active_time,max(end_time) AS last_active_time,groupUniqArray(attack_type) AS attack_type,count(*) AS count from dos_event where start_time >= toUnixTimestamp(@start) AND start_time < toUnixTimestamp(@end) GROUP BY destination_ip ORDER BY count desc
|
||
--Q62.notEmpty(druid)
|
||
SELECT device_id from traffic_metrics_log where __time >= @start and __time < @end AND notEmpty(device_id) limit 10
|
||
--Q63.empty(druid)
|
||
SELECT device_id from traffic_metrics_log where __time >= @start and __time < @end AND empty(device_id) limit 10 |