-
Notifications
You must be signed in to change notification settings - Fork 1
/
mapping.yaml
63 lines (63 loc) · 3.65 KB
/
mapping.yaml
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
mappings:
- name: passwd
tables:
- CREATE TABLE user(gid INTEGER, shell VARCHAR) WITH FIELDS IDENTIFIED BY '(?P<gid>[0-9]+):.*:(?P<shell>.[^:]*)$'
LINES TERMINATED BY '\n';
queries:
- name: sumgid
query: SELECT shell, COUNT(*), SUM(gid) FROM user GROUP BY shell;
- name: combinedlog
tables:
- CREATE TABLE log(ip VARCHAR, col2 VARCHAR, col3 VARCHAR, dt VARCHAR, method VARCHAR,
url VARCHAR, version VARCHAR, response INTEGER, size INTEGER, col10 VARCHAR, useragent
VARCHAR) WITH FIELDS IDENTIFIED BY '^(?P<ip>\\S+)\\s(?P<col2>\\S+)\\s(?P<col3>\\S+)\\s\\[(?P<dt>[\\w:\\/]+\\s[+\\-]\\d{4})\\]\\s"(?P<method>\\S+)\\s?(?P<url>\\S+)?\\s?(?P<version>\\S+)?"\\s(?P<response>\\d{3}|-)\\s(?P<size>\\d+|-)\\s?"?(?P<col10>[^"]*)"?\\s?"?(?P<useragent>[^"]*)?"?$'
LINES TERMINATED BY '\n';
queries:
- name: responsebyurl
query: SELECT URLIFY(url) as url, COUNT(*) as count, RATE(*) as rate, SUM(size)
as sum, size, MAX(response) FROM log GROUP BY url, size ORDER BY count ASC,
size DESC LIMIT 20;
- name: likegroup
query: SELECT url, COUNT(*) as count, SUM(size) as sum, AVG(size), MAX(response)
FROM log WHERE url LIKE '/surveys/%' AND response > 200 GROUP BY url ORDER BY
count LIMIT 5;
- name: apitimedlog
tables:
- CREATE TABLE log(ip VARCHAR, dt VARCHAR, method VARCHAR, url VARCHAR, version
VARCHAR, response INTEGER, size INTEGER, time_seconds INTEGER, time_miliseconds
INTEGER) WITH FIELDS IDENTIFIED BY '^(?P<ip>\\S+)\\s\\[(?P<dt>[\\w:\\/]+\\s[+\\-]\\d{4})\\]\\s"(?P<method>\\S+)\\s?(?P<url>\\S+)?\\s?(?P<version>\\S+)?"\\s(?P<response>\\d{3}|-)\\s(?P<size>\\d+|-)\\s\\|\\s\\d+\\s\\|\\s(?P<time_seconds>\\d+)\\ss\\s\\|\\s(?P<time_miliseconds>\\d+)\\sus$'
LINES TERMINATED BY '\n';
queries:
- name: responsebyurl
query: SELECT URLIFY(url) as url, COUNT(*) as count, SUM(size) as sum, size, MAX(response)
FROM log GROUP BY url, size ORDER BY count ASC, size DESC LIMIT 20;
- name: likegroup
query: SELECT url, COUNT(*) as count, SUM(size) as sum, AVG(size), MAX(response)
FROM log WHERE url LIKE '/surveys/%' AND response > 200 GROUP BY url ORDER BY
count LIMIT 5;
- name: timedlog
tables:
- CREATE TABLE log(ip VARCHAR, dt VARCHAR, method VARCHAR, url VARCHAR, version
VARCHAR, response INTEGER, size INTEGER, time_seconds INTEGER, time_miliseconds
INTEGER) WITH FIELDS IDENTIFIED BY '^(?P<ip>\\S+)\\s\\[(?P<dt>[\\w:\\/]+\\s[+\\-]\\d{4})\\]\\s"(?P<method>\\S+)\\s?(?P<url>\\S+)?\\s?(?P<version>\\S+)?"\\s(?P<response>\\d{3}|-)\\s(?P<size>\\d+|-)\\s\\|\\s(?P<time_seconds>\\d+)\\ss\\s\\|\\s(?P<time_miliseconds>\\d+)\\sus$'
LINES TERMINATED BY '\n';
queries:
- name: responsebyurl
query: SELECT URLIFY(url) as url, COUNT(*) as count, SUM(size) as sum, size, MAX(response)
FROM log GROUP BY url, size ORDER BY count ASC, size DESC LIMIT 20;
- name: likegroup
query: SELECT url, COUNT(*) as count, SUM(size) as sum, AVG(size), MAX(response)
FROM log WHERE url LIKE '/surveys/%' AND response > 200 GROUP BY url ORDER BY
count LIMIT 5;
- name: internalLog
tables:
- 'CREATE TABLE nonAuth(reason VARCHAR, uri VARCHAR, referer VARCHAR, clientIp VARCHAR,
userAgent VARCHAR) WITH FIELDS IDENTIFIED BY ''^{\\Dreason\\D: \\D(?P<reason>[^\\\'']*)\\D,
\\Duri\\D: \\D(?P<uri>[^\\\'']*)\\D, \\Dreferer\\D: \\D(?P<referer>[^\\\'']*)\\D,
\\DclientIp\\D: \\D(?P<clientIp>[^\\\'']*)\\D, \\Duser_agent\\D: \\D(?P<userAgent>[^\\\'']*)\\D}$''
LINES TERMINATED BY ''\n'';'
queries:
- name: basic
query: SELECT reason, URLIFY(uri) as url, COUNT(*) as count, URLIFY(referer),
clientIp, userAgent from nonAuth GROUP BY url, reason, clientIp ORDER BY count
ASC;