-
-
Notifications
You must be signed in to change notification settings - Fork 53
/
Copy pathtables.sql
executable file
·158 lines (138 loc) · 6.84 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
CREATE TABLE IF NOT EXISTS DNS_LOG (
PacketTime DateTime64,
IndexTime DateTime64,
Server LowCardinality(String),
IPVersion UInt8,
SrcIP IPv6,
DstIP IPv6,
Protocol FixedString(3),
QR UInt8,
OpCode UInt8,
Class UInt16,
Type UInt16,
Edns0Present UInt8,
DoBit UInt8,
FullQuery String,
ResponseCode UInt8,
Question String CODEC(ZSTD(1)),
Size UInt16
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(PacketTime)
PRIMARY KEY (toStartOfHour(PacketTime), Server, reverse(Question), toUnixTimestamp(PacketTime))
ORDER BY (toStartOfHour(PacketTime), Server, reverse(Question), toUnixTimestamp(PacketTime))
SAMPLE BY toUnixTimestamp(PacketTime)
TTL toDate(PacketTime) + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192;
-- View for top queried domains
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_DOMAIN_COUNT
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (DnsDate, Server, QH)
ORDER BY (DnsDate, Server, QH)
SAMPLE BY QH
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, toStartOfMinute(PacketTime) as t, Server, Question, cityHash64(Question) as QH, count(*) as c FROM DNS_LOG WHERE QR=0 GROUP BY DnsDate, t, Server, Question;
-- View for unique domain count
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_DOMAIN_UNIQUE
ENGINE=AggregatingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (DnsDate, (timestamp, Server))
ORDER BY (DnsDate, (timestamp, Server))
TTL toDate(timestamp) + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, PacketTime as timestamp, Server, uniqState(Question) AS UniqueDnsCount FROM DNS_LOG WHERE QR=0 GROUP BY Server, DnsDate, timestamp;
-- View for count by protocol
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_PROTOCOL
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (DnsDate, (timestamp,Server))
ORDER BY (DnsDate, (timestamp,Server))
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, PacketTime as timestamp, Server, Protocol, count(*) as c FROM DNS_LOG GROUP BY Server, DnsDate, timestamp, Protocol;
-- View with packet sizes
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_GENERAL_AGGREGATIONS
ENGINE=AggregatingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (DnsDate, (timestamp, Server))
ORDER BY (DnsDate, (timestamp, Server))
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, PacketTime as timestamp, Server, sumState(Size) AS TotalSize, avgState(Size) AS AverageSize FROM DNS_LOG GROUP BY Server, DnsDate, timestamp;
-- View with edns information
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_EDNS
ENGINE=AggregatingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (DnsDate, (timestamp, Server))
ORDER BY (DnsDate, (timestamp, Server))
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, PacketTime as timestamp, Server, sumState(Edns0Present) as EdnsCount, sumState(DoBit) as DoBitCount FROM DNS_LOG WHERE QR=0 GROUP BY Server, DnsDate, timestamp;
-- View wih query OpCode
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_OPCODE
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (timestamp, Server, OpCode)
ORDER BY (timestamp, Server, OpCode)
SAMPLE BY OpCode
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, PacketTime as timestamp, Server, OpCode, count(*) as c FROM DNS_LOG WHERE QR=0 GROUP BY Server, DnsDate, timestamp, OpCode;
-- View with Query Types
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_TYPE
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (timestamp, Server, Type)
ORDER BY (timestamp, Server, Type)
SAMPLE BY Type
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, PacketTime as timestamp, Server, Type, count(*) as c FROM DNS_LOG WHERE QR=0 GROUP BY Server, DnsDate, timestamp, Type;
-- View with Query Class
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_CLASS
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (timestamp, Server, Class)
ORDER BY (timestamp, Server, Class)
SAMPLE BY Class
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, PacketTime as timestamp, Server, Class, count(*) as c FROM DNS_LOG WHERE QR=0 GROUP BY Server, DnsDate, timestamp, Class;
-- View with query responses
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_RESPONSECODE
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (timestamp, Server, ResponseCode)
ORDER BY (timestamp, Server, ResponseCode)
SAMPLE BY ResponseCode
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, PacketTime as timestamp, Server, ResponseCode, count(*) as c FROM DNS_LOG WHERE QR=1 GROUP BY Server, DnsDate, timestamp, ResponseCode;
-- View with Source IP Prefix
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_SRCIP_MASK
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (timestamp, Server, IPVersion, cityHash64(SrcIP))
ORDER BY (timestamp, Server, IPVersion, cityHash64(SrcIP))
SAMPLE BY cityHash64(SrcIP)
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, PacketTime as timestamp, Server, IPVersion, SrcIP, count(*) as c FROM DNS_LOG GROUP BY Server, DnsDate, timestamp, IPVersion, SrcIP ;
-- View with Destination IP Prefix
CREATE MATERIALIZED VIEW IF NOT EXISTS DNS_DSTIP_MASK
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(DnsDate)
PRIMARY KEY (timestamp, Server, IPVersion, cityHash64(DstIP))
ORDER BY (timestamp, Server, IPVersion, cityHash64(DstIP))
SAMPLE BY cityHash64(DstIP)
TTL DnsDate + INTERVAL 30 DAY -- DNS_TTL_VARIABLE
SETTINGS index_granularity = 8192
AS SELECT toDate(PacketTime) as DnsDate, PacketTime as timestamp, Server, IPVersion, DstIP, count(*) as c FROM DNS_LOG GROUP BY Server, DnsDate, timestamp, IPVersion, DstIP ;
-- sample queries
-- new domains over the past 24 hours
-- SELECT DISTINCT Question FROM (SELECT Question from DNS_LOG WHERE toStartOfDay(timestamp) > Now() - INTERVAL 1 DAY) AS dns1 LEFT ANTI JOIN (SELECT Question from DNS_LOG WHERE toStartOfDay(timestamp) < Now() - INTERVAL 1 DAY AND toStartOfDay(timestamp) > (Now() - toIntervalDay(10)) ) as dns2 ON dns1.Question = dns2.Question
-- timeline of request count every 5 minutes
-- SELECT toStartOfFiveMinute(timestamp) as t, count() from DNS_LOG GROUP BY t ORDER BY t
--