forked from mosajjal/dnsmonster
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathtables.sql
executable file
·187 lines (175 loc) · 7.21 KB
/
tables.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
CREATE TABLE IF NOT EXISTS DNS_LOG (
DnsDate Date,
timestamp DateTime,
Server LowCardinality(String),
NodeQualifier UInt8,
ClusterName FixedString(64),
IPVersion UInt8,
SrcIP UInt32,
DstIP UInt32,
Protocol LowCardinality(String),
QR UInt8,
OpCode UInt8,
Class UInt16,
Type UInt16,
Edns0Present UInt8,
DoBit UInt8,
FullQuery String,
ResponseCode UInt8,
Question LowCardinality(String),
EtldPlusOne LowCardinality(String),
Size UInt16,
ID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (timestamp , ClusterName, Server, NodeQualifier, cityHash64(ID))
ORDER BY (timestamp, ClusterName, Server, NodeQualifier, cityHash64(ID))
SAMPLE BY cityHash64(ID)
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192,min_bytes_for_wide_part = '10M';
CREATE TABLE IF NOT EXISTS DNS_LOG_BUFFER (
DnsDate Date,
timestamp DateTime,
Server LowCardinality(String),
NodeQualifier UInt8,
ClusterName FixedString(64),
IPVersion UInt8,
SrcIP UInt32,
DstIP UInt32,
Protocol LowCardinality(String),
QR UInt8,
OpCode UInt8,
Class UInt16,
Type UInt16,
Edns0Present UInt8,
DoBit UInt8,
FullQuery String,
ResponseCode UInt8,
Question LowCardinality(String),
EtldPlusOne LowCardinality(String),
Size UInt16,
ID UUID
MATERIALIZED toDate(timestamp)
)
ENGINE = Buffer('default', 'DNS_LOG', 16, 5, 30, 10000, 1000000, 10000000, 100000000);
-- View 1min DNS Metrics per Cluster, Server Subscriber/Internet Request/Response
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_METRICS_1M
ENGINE=SummingMergeTree()
PARTITION BY toYYYYMM(DnsDate)
PRIMARY KEY (DnsDate, timestamp , ClusterName, Server, NodeQualifier, QR)
ORDER BY (DnsDate, timestamp, ClusterName, Server, NodeQualifier, QR)
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS
SELECT DnsDate, toStartOfMinute(timestamp) as timestamp, ClusterName, Server, NodeQualifier, QR,
count(*) as Total, countIf(Protocol='udp') as udp, countIf(Protocol='tcp') as tcp, countIf(DoBit=1) as DoBit, countIf(Edns0Present=1) as Edns0,
countIf(IPVersion=4) as ipV4,countIf(IPVersion=6) as ipV6
FROM DNS_LOG
GROUP BY DnsDate, timestamp, ClusterName, Server, NodeQualifier, QR;
-- View for top queried Top Level + 1 domains
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_ETLDPLUSONE_1M
ENGINE=SummingMergeTree()
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (DnsDate, timestamp , ClusterName, Server, NodeQualifier)
ORDER BY (DnsDate, timestamp, ClusterName, Server, NodeQualifier, EtldPlusOne)
TTL DnsDate + INTERVAL 5 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS
SELECT DnsDate, toStartOfMinute(timestamp) as timestamp, ClusterName, Server, NodeQualifier, EtldPlusOne, count(*) as Requests, sum(Size) as TotalRequestBytes
FROM DNS_LOG
WHERE QR=0
GROUP BY DnsDate, timestamp, ClusterName, Server, NodeQualifier, EtldPlusOne;
-- View for top queried domains
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_DOMAIN_1M
ENGINE=SummingMergeTree()
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (DnsDate, timestamp , ClusterName, Server, NodeQualifier)
ORDER BY (DnsDate, timestamp, ClusterName, Server, NodeQualifier, Question)
TTL DnsDate + INTERVAL 5 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS
SELECT DnsDate, toStartOfMinute(timestamp) as timestamp, ClusterName, Server, NodeQualifier, Question, count(*) as Requests, sum(Size) as TotalRequestBytes
FROM DNS_LOG
WHERE QR=0
GROUP BY DnsDate, timestamp, ClusterName, Server, NodeQualifier, Question;
-- View for unique domain count
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_DOMAIN_UNIQ_1M
ENGINE=AggregatingMergeTree()
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (DnsDate, timestamp , ClusterName, Server, NodeQualifier)
ORDER BY (DnsDate, timestamp, ClusterName, Server, NodeQualifier)
TTL DnsDate + INTERVAL 5 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS
SELECT DnsDate, toStartOfMinute(timestamp) as timestamp, ClusterName, Server, NodeQualifier, uniqState(Question) AS UniqueDnsCount
FROM DNS_LOG
WHERE QR=0
GROUP BY DnsDate, timestamp, ClusterName, Server, NodeQualifier;
-- View wih query OpCode
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_OPCODE_1M
ENGINE=SummingMergeTree()
PARTITION BY toYYYYMM(DnsDate)
PRIMARY KEY (DnsDate, timestamp , ClusterName, Server, NodeQualifier, QR, OpCode)
ORDER BY (DnsDate, timestamp, ClusterName, Server, NodeQualifier, QR, OpCode)
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS
SELECT DnsDate, toStartOfMinute(timestamp) as timestamp, ClusterName, Server, NodeQualifier, QR, OpCode,
count(*) as Total
FROM DNS_LOG
GROUP BY DnsDate, timestamp, ClusterName, Server, NodeQualifier, QR, OpCode;
-- View with Query Class
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_CLASS_1M
ENGINE=SummingMergeTree()
PARTITION BY toYYYYMM(DnsDate)
PRIMARY KEY (DnsDate, timestamp , ClusterName, Server, NodeQualifier, Class)
ORDER BY (DnsDate, timestamp, ClusterName, Server, NodeQualifier, Class)
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS
SELECT DnsDate, toStartOfMinute(timestamp) as timestamp, ClusterName, Server, NodeQualifier, Class,
count(*) as Total
FROM DNS_LOG
WHERE QR=0
GROUP BY DnsDate, timestamp, ClusterName, Server, NodeQualifier, Class;
-- View with Query Types
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_TYPE_1M
ENGINE=SummingMergeTree()
PARTITION BY toYYYYMM(DnsDate)
PRIMARY KEY (DnsDate, timestamp , ClusterName, Server, NodeQualifier, Type)
ORDER BY (DnsDate, timestamp, ClusterName, Server, NodeQualifier, Type)
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS
SELECT DnsDate, toStartOfMinute(timestamp) as timestamp, ClusterName, Server, NodeQualifier, Type,
count(*) as Total
FROM DNS_LOG
WHERE QR=0
GROUP BY DnsDate, timestamp, ClusterName, Server, NodeQualifier, Type;
-- View with query responses
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_RESPONSECODE_1M
ENGINE=SummingMergeTree()
PARTITION BY toYYYYMM(DnsDate)
PRIMARY KEY (DnsDate, timestamp , ClusterName, Server, NodeQualifier, ResponseCode)
ORDER BY (DnsDate, timestamp, ClusterName, Server, NodeQualifier, ResponseCode)
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS
SELECT DnsDate, toStartOfMinute(timestamp) as timestamp, ClusterName, Server, NodeQualifier, ResponseCode,
count(*) as Total
FROM DNS_LOG
WHERE QR=1
GROUP BY DnsDate, timestamp, ClusterName, Server, NodeQualifier, ResponseCode;
-- View with packet sizes
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_PACKET_SIZES_1M
ENGINE=AggregatingMergeTree()
PARTITION BY toYYYYMM(DnsDate)
PRIMARY KEY (DnsDate, timestamp , ClusterName, Server, NodeQualifier, QR)
ORDER BY (DnsDate, timestamp, ClusterName, Server, NodeQualifier, QR)
TTL DnsDate + INTERVAL 6 MONTH -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS
SELECT DnsDate, toStartOfMinute(timestamp) as timestamp, ClusterName, Server, NodeQualifier, QR, sumState(toUInt64(Size)) AS TotalSize, avgState(Size) AS AverageSize
FROM DNS_LOG
GROUP BY DnsDate, timestamp, ClusterName, Server, NodeQualifier, QR;