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
|
package db
// DCSO FEVER
// Copyright (c) 2017, DCSO GmbH
// SQLTrigramFunction is a plpgsql function to pull out indexable content from event JSON
const SQLTrigramFunction = `CREATE OR REPLACE FUNCTION trigram_string(payload jsonb)
RETURNS text
AS $$
DECLARE
buffer varchar := '';
BEGIN
-- trying in typical order of frequency
IF payload->>'event_type' = 'dns'
THEN
RETURN payload->'dns'->>'rdata';
END IF;
IF payload->>'event_type' = 'http'
THEN
RETURN (payload->'http'->>'hostname') || '|' || (payload->'http'->>'url') || '|' || (payload->'http'->>'http_user_agent');
END IF;
IF payload->>'event_type' = 'tls'
THEN
RETURN (payload->'tls'->>'subject') ||'|' || (payload->'tls'->>'issuerdn') || '|' || (payload->'tls'->>'fingerprint');
END IF;
IF payload->>'event_type' = 'alert'
THEN
RETURN (payload->'alert'->>'payload_printable') || '|' || (payload->'alert'->>'payload');
END IF;
IF payload->>'event_type' = 'smtp'
THEN
RETURN (payload->'smtp'->>'helo') || '|' || (payload->'smtp'->>'mail_from') || '|' || (payload->'smtp'->>'rcpt_to') || '|' || (payload->'email'->>'from') || '|' || (payload->'email'->>'to') || '|' || (payload->'email'->>'attachment');
END IF;
IF payload->>'event_type' = 'fileinfo'
THEN
RETURN (payload->'fileinfo'->>'filename') || '|' || (payload->'fileinfo'->>'md5');
END IF;
RETURN buffer;
END;
$$
LANGUAGE plpgsql
IMMUTABLE;`
// SQLCheckForTrigramExtension is an SQL query to check whether the trigram extension is available.
const SQLCheckForTrigramExtension = `SELECT COUNT(*) FROM pg_available_extensions WHERE name = 'pg_trgm';`
// SQLCreate is an SQL/DDL clause to create a new event table
const SQLCreate = `CREATE UNLOGGED TABLE IF NOT EXISTS "%s"
(ts timestamp without time zone default now(),
payload jsonb);
GRANT ALL PRIVILEGES ON TABLE "%s" to %s;`
// SQLCopy is an SQL/DDL clause to bulk insert a chunk of JSON into the database
const SQLCopy = `COPY "%s" (ts, payload) FROM STDIN WITH CSV DELIMITER E'\t' QUOTE E'\b'`
// SQLIndex is an SQL/DDL clause to create indexes on event tables
const SQLIndex = `CREATE INDEX ON "%s" (((payload->>'src_ip')::INET), ((payload->>'src_port')::INT));
CREATE INDEX ON "%s" (ts);
CREATE INDEX ON "%s" (((payload->>'dest_ip')::INET), ((payload->>'dest_port')::INT));
CREATE INDEX ON "%s" ((payload->>'event_type'));
CREATE INDEX ON "%s" using GIN (trigram_string(payload) gin_trgm_ops)`
// SQLGetTableSizes is an SQL query to obtain the names of tables in the current schema and their size in bytes.
const SQLGetTableSizes = `SELECT relname as table,
pg_total_relation_size(relid) as size
FROM pg_catalog.pg_statio_user_tables
ORDER BY 1 DESC;`
// SQLGenericQuery is the main kind of query used to pull out event metadata.
const SQLGenericQuery = `SELECT * FROM all_events_query($1::text, $2::timestamp, $3::timestamp, $4::text[], $5::inet, $6::int, $7::inet, $8::int, $9::int);`
// SQLQueryAllEvents is a plpgsql function to enable queries over all hourly tables
// Example: SELECT COUNT(*) FROM all_events_query('WHERE trigram_string(payload) LIKE ''%%foo%%''');
const SQLQueryAllEvents = `CREATE OR REPLACE FUNCTION all_events_query(keyword text,
start_time timestamp with time zone,
end_time timestamp with time zone,
event_type text[],
ipsrc inet, portsrc int,
ipdest inet, portdest int,
mlimit int)
RETURNS TABLE (ts timestamp, payload jsonb)
AS $$
DECLARE
clause text;
t RECORD;
tables CURSOR FOR
SELECT * FROM information_schema.tables
WHERE table_name LIKE 'event%';
BEGIN
clause := '';
OPEN tables;
LOOP
FETCH tables INTO t;
EXIT WHEN NOT FOUND;
IF clause != '' THEN
clause := clause || ' UNION ALL ';
END IF;
clause := clause
|| 'SELECT * FROM ' || quote_ident(t.table_name)
|| ' WHERE ts BETWEEN ' || quote_literal(start_time)
|| ' AND ' || quote_literal(end_time);
IF keyword IS NOT NULL THEN
clause := clause
|| ' AND trigram_string(payload) LIKE ' || quote_literal(keyword);
END IF;
IF event_type IS NOT NULL THEN
clause := clause
|| ' AND payload->>''event_type'' = ANY(' || quote_literal(event_type) || ')';
END IF;
IF ipsrc IS NOT NULL THEN
clause := clause
|| ' AND (payload->>''src_ip'')::inet <<= inet ' || quote_literal(ipsrc);
END IF;
IF portsrc IS NOT NULL THEN
clause := clause
|| ' AND payload->>''src_port'' = ' || quote_literal(portsrc);
END IF;
IF ipdest IS NOT NULL THEN
clause := clause
|| ' AND (payload->>''dest_ip'')::inet <<= inet ' || quote_literal(ipdest);
END IF;
IF portdest IS NOT NULL THEN
clause := clause
|| ' AND payload->>''dest_port'' = ' || quote_literal(portdest);
END IF;
END LOOP;
IF mlimit IS NOT NULL THEN
clause := clause || ' LIMIT ' || quote_literal(mlimit);
END IF;
RAISE NOTICE '%', clause;
CLOSE tables;
RETURN QUERY EXECUTE clause;
END;
$$
LANGUAGE plpgsql
STABLE;
`
|
