DNS Pattern Requests Violation

dns_pattern_requests_violation.sql

SET autocreate=true;

INSERT INTO dns_pattern_requests_violation
WITH log_regex as (
  SELECT STREAM
      dns,
      ipv4,
      udp,
      regexp(dns.dns_question[0].name, '(\d.*?){5,}') as rule_numbers,
      regexp(dns.dns_question[0].name, '\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}') as rule_is_ip
  FROM `DNS_DHCP_TRAFFIC`
)
SELECT STREAM 
    dns,
    ipv4,
    udp 
FROM log_regex
WHERE (rule_numbers is not null AND ARRAY_SIZE(rule_numbers) > 0)
AND (rule_is_ip is null AND ARRAY_SIZE(rule_is_ip) = 0)