... | @@ -40,9 +40,9 @@ resp_arcount INT, |
... | @@ -40,9 +40,9 @@ resp_arcount INT, |
|
resp_nscount INT,
|
|
resp_nscount INT,
|
|
req_edns_ver INT,
|
|
req_edns_ver INT,
|
|
req_edns_udp INT,
|
|
req_edns_udp INT,
|
|
req_edns_do BOOLEAN,
|
|
req_edns_do INT, -- convert to boolean
|
|
resp_edns_rcode INT,
|
|
resp_edns_rcode INT,
|
|
req_edns_ping BOOLEAN,
|
|
req_edns_ping INT, -- convert to boolean
|
|
req_edns_dau STRING,
|
|
req_edns_dau STRING,
|
|
req_edns_dhu STRING,
|
|
req_edns_dhu STRING,
|
|
req_edns_n3u STRING,
|
|
req_edns_n3u STRING,
|
... | @@ -110,38 +110,38 @@ SELECT |
... | @@ -110,38 +110,38 @@ SELECT |
|
resp_ancount, -- Entrada: ancount INT
|
|
resp_ancount, -- Entrada: ancount INT
|
|
resp_arcount, -- Entrada: arcount INT
|
|
resp_arcount, -- Entrada: arcount INT
|
|
resp_nscount, -- Entrada: nscount INT
|
|
resp_nscount, -- Entrada: nscount INT
|
|
1, -- Entrada: qdcount INT
|
|
if(length(qname) > 0, 1, 0), -- Entrada: qdcount INT
|
|
opcode, -- Entrada: opcode INT
|
|
opcode, -- Entrada: opcode INT
|
|
rcode, -- Entrada: rcode INT
|
|
rcode, -- Entrada: rcode INT
|
|
qtype, -- Entrada: qtype INT
|
|
qtype, -- Entrada: qtype INT
|
|
qclass, -- Entrada: qclass INT
|
|
qclass, -- Entrada: qclass INT
|
|
NULL, -- Entrada: country STRING
|
|
NULL, -- Entrada: country STRING
|
|
NULL, -- Entrada: asn STRING
|
|
NULL, -- Entrada: asn STRING
|
|
-- Entrada: edns_udp INT
|
|
req_edns_udp, -- Entrada: edns_udp INT
|
|
-- Entrada: edns_version SMALLINT
|
|
req_edns_ver, -- Entrada: edns_version SMALLINT
|
|
-- Entrada: edns_do BOOLEAN
|
|
cast(req_edns_do as BOOLEAN), -- Entrada: edns_do BOOLEAN
|
|
-- Entrada: edns_ping BOOLEAN
|
|
cast(req_edns_ping as BOOLEAN), -- Entrada: edns_ping BOOLEAN
|
|
-- Entrada: edns_nsid STRING
|
|
resp_edns_nsid, -- Entrada: edns_nsid STRING
|
|
-- Entrada: edns_dnssec_dau STRING
|
|
req_edns_dau, -- Entrada: edns_dnssec_dau STRING
|
|
-- Entrada: edns_dnssec_dhu STRING
|
|
req_edns_dhu, -- Entrada: edns_dnssec_dhu STRING
|
|
-- Entrada: edns_dnssec_n3u STRING
|
|
req_edns_n3u, -- Entrada: edns_dnssec_n3u STRING
|
|
-- Entrada: edns_client_subnet STRING
|
|
edns_client_subnet, -- Entrada: edns_client_subnet STRING
|
|
-- Entrada: edns_other STRING
|
|
req_edns_other, -- Entrada: edns_other STRING
|
|
-- Entrada: edns_client_subnet_asn STRING
|
|
NULL, -- Entrada: edns_client_subnet_asn STRING
|
|
-- Entrada: edns_client_subnet_country STRING
|
|
NULL, -- Entrada: edns_client_subnet_country STRING
|
|
-- Entrada: labels INT
|
|
if(length(qname) > 1, length(regexp_replace(qname, "[^\\.]", "")), 0), -- Entrada: labels INT
|
|
-- Entrada: res_len INT
|
|
resp_pkt_len, -- Entrada: res_len INT
|
|
-- Entrada: time_micro BIGINT
|
|
delay_us, -- Entrada: time_micro BIGINT
|
|
-- Entrada: resp_frag INT
|
|
NULL, -- Entrada: resp_frag INT
|
|
-- Entrada: proc_time INT
|
|
NULL, -- Entrada: proc_time INT
|
|
-- Entrada: is_google BOOLEAN
|
|
find_in_set(client_addr, "8.8.8.8,8.8.4.4,2001:4860:4860::8888,2001:4860:4860::8844") > 0, -- Entrada: is_google BOOLEAN
|
|
-- Entrada: is_opendns BOOLEAN
|
|
find_in_set(client_addr, "208.67.222.222,208.67.220.220") > 0, -- Entrada: is_opendns BOOLEAN
|
|
-- Entrada: dns_res_len INT
|
|
resp_dns_len, -- Entrada: dns_res_len INT
|
|
-- Entrada: server_location STRING
|
|
NULL, -- Entrada: server_location STRING
|
|
-- Entrada: year INT
|
|
extract(year from cast(time as timestamp)), -- Entrada: year INT
|
|
-- Entrada: month INT
|
|
extract(month from cast(time as timestamp)), -- Entrada: month INT
|
|
-- Entrada: day INT
|
|
extract(day from cast(time as timestamp)), -- Entrada: day INT
|
|
-- Entrada: server STRING
|
|
server -- Entrada: server STRING
|
|
FROM dnscol.csv_import WHERE server="andraste" AND batchname="2016-08-24-tgimport1";
|
|
FROM dnscol.csv_import WHERE server="andraste" AND batchname="2016-08-24-tgimport1";
|
|
```
|
|
```
|
|
|
|
|