This repository has been archived by the owner on Feb 20, 2022. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Analysis.sql
161 lines (137 loc) · 7.42 KB
/
Analysis.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
/*1A. Are there unusual user agent strings?
Filter BRO_CONN and BRO_HTTP for connections originating from internal hosts. Show count of times each user agent is used
and the number of hosts using that user agent. Sort by number of hosts using the user agent*/
SELECT bro_http.HTTP_USERAGENT, COUNT(bro_http.HTTP_USERAGENT) as SumOfAgent, COUNT(DISTINCT(bro_conn.CONN_ORIGH)) as SumOfHosts
FROM bro_http
LEFT JOIN bro_conn
ON bro_http.HTTP_UID = bro_conn.CONN_UID
WHERE bro_conn.CONN_ORIGH >= INET6_ATON('10.0.0.0') AND bro_conn.CONN_ORIGH <= INET6_ATON('10.255.255.255')
GROUP BY bro_http.HTTP_USERAGENT
ORDER BY SumOfHosts DESC;
/*1B. Are there unusual user agent strings?
Filter BRO_CONN and BRO_HTTP for connections originating from internal hosts. Show count of times each user agent is used
and the number of hosts using that user agent. Sort by number of hosts using the user agent*/
SELECT bro_http.HTTP_USERAGENT, COUNT(bro_http.HTTP_USERAGENT) as SumOfAgent, COUNT(DISTINCT(bro_conn.CONN_ORIGH)) as SumOfHosts
FROM bro_http
LEFT JOIN bro_conn
ON bro_http.HTTP_UID = bro_conn.CONN_UID
WHERE bro_conn.CONN_ORIGH >= INET6_ATON('10.0.0.0') AND bro_conn.CONN_ORIGH <= INET6_ATON('10.255.255.255')
GROUP BY bro_http.HTTP_USERAGENT
ORDER BY SumOfAgent DESC;
/*2. Are there any SMB connections that don't go to/from a domain controller?
Filter BRO_CONN files with a destination port of 445 (SMB). Display source and destination IP address and destination port number and a count of the uniq grouping*/
SELECT INET6_NTOA(bro_conn.CONN_ORIGH) as src_ip, INET6_NTOA(bro_conn.CONN_RESPH) as dst_ip, bro_conn.CONN_RESPP, COUNT(bro_conn.CONN_RESPH) AS total_connections
FROM bro_conn
WHERE bro_conn.CONN_RESPP = 445
/*Add line about exclude domain controllers*/
GROUP BY bro_conn.CONN_ORIGH, bro_conn.CONN_RESPH
ORDER BY total_connections DESC
/*3. Are there any external originating connections to unexpected hosts?
Filter BRO_CONN for all connections originating from outside the network that complete (state of SF). Remove expected connections (DNS, web servers, SMTP, etc)*/
SELECT bro_conn.CONN_TS AS TIME, INET6_NTOA(bro_conn.CONN_ORIGH) AS SRC_IP, bro_conn.CONN_ORIGP AS SRC_PORT, INET6_NTOA(bro_conn.CONN_RESPH) AS DST_IP, bro_conn.CONN_RESPP AS DST_PORT, bro_conn.CONN_PROTO AS PROTOCOL, bro_conn.CONN_ORIGIPBYTES AS SRC_BYTES, bro_conn.CONN_RESPIPBYTES AS DST_BYTES, bro_conn.CONN_UID AS UID
FROM bro_conn
WHERE bro_conn.CONN_CONNSTATE = "SF" AND
(bro_conn.CONN_ORIGH < INET6_ATON('10.0.0.0') OR bro_conn.CONN_ORIGH > INET6_ATON('10.255.255.255'))
/*Add line about exclude DNS, web servers, and email*/
ORDER BY bro_conn.CONN_TS
/*4. Are there any connections from internal hosts to external hosts without a corresponding DNS request
CREATE TEMP TABLE WITH ALL IPS IDENTIFIED FROM PASSIVE_DNS AND EXEMPT_IPS*/
CREATE TABLE IF NOT EXISTS tmp_known_ips AS (SELECT distinct(PASSIVE_ANSWER) AS ip_address
FROM passive_dns);
/*FIND ALL DESINATION ADDRESSES THAT DIDN'T HAVE A CORRESPONDING DNS ANSWER
Filter out DNS traffic*/
SELECT DISTINCT(INET6_NTOA(bro_conn.CONN_RESPH)) AS missing_address
FROM bro_conn
LEFT JOIN tmp_known_ips ON tmp_known_ips.ip_address = bro_conn.CONN_RESPH
WHERE tmp_known_ips.ip_address IS NULL AND
(bro_conn.CONN_RESPH < INET6_ATON('10.3.0.0') OR bro_conn.CONN_RESPH > INET6_ATON('10.3.255.255')) AND NOT
(bro_conn.CONN_ORIGH = inet6_aton('10.3.58.4') AND bro_conn.CONN_RESPP = 53)
ORDER BY CONN_ORIGH, CONN_RESPH, CONN_RESPP
/*5A. How many total bytes were sent by a single host?
Create a table that displays all IPs seen on the network (as source or destination) and
figure out much each of them sent as a source or destination and total*/
SELECT IP AS SRC_IP,
SUM(CASE WHEN Type = 'SRC' THEN BYTES ELSE 0 END) AS SRC_BYTES,
SUM(CASE WHEN Type = 'DST' THEN BYTES ELSE 0 END) AS DST_BYTES,
SUM(BYTES) AS TOTAL_BYTES
FROM (
SELECT INET6_NTOA(bro_conn.CONN_ORIGH) as IP,
SUM(bro_conn.CONN_ORIGIPBYTES) AS BYTES,
'SRC' AS Type
FROM bro_conn
GROUP BY IP
UNION ALL
SELECT INET6_NTOA(bro_conn.CONN_RESPH) as IP,
SUM(bro_conn.CONN_RESPIPBYTES) AS BYTES,
'DST' AS Type
FROM bro_conn GROUP BY IP) AS t
GROUP BY IP
/*5B. How many total bytes were sent by a single host (internal host to internal host)?
Create a table that displays all IPs seen on the network (as source or destination) and
figure out much each of them sent as a source or destination and total
(Filter for Internal to Internal only)*/
SELECT IP AS SRC_IP,
SUM(CASE WHEN Type = 'SRC' THEN BYTES ELSE 0 END) AS SRC_BYTES,
SUM(CASE WHEN Type = 'DST' THEN BYTES ELSE 0 END) AS DST_BYTES,
SUM(BYTES) AS TOTAL_BYTES
FROM (
SELECT INET6_NTOA(bro_conn.CONN_ORIGH) as IP,
SUM(bro_conn.CONN_ORIGIPBYTES) AS BYTES,
'SRC' AS Type
FROM bro_conn
WHERE (bro_conn.CONN_ORIGH >= INET6_ATON('10.0.0.0') AND bro_conn.CONN_ORIGH <= INET6_ATON('10.255.255.255')) AND
(bro_conn.CONN_RESPH >= INET6_ATON('10.0.0.0') AND bro_conn.CONN_RESPH <= INET6_ATON('10.255.255.255'))
GROUP BY IP
UNION ALL
SELECT INET6_NTOA(bro_conn.CONN_RESPH) as IP,
SUM(bro_conn.CONN_RESPIPBYTES) AS BYTES,
'DST' AS Type
FROM bro_conn
WHERE (bro_conn.CONN_ORIGH >= INET6_ATON('10.0.0.0') AND bro_conn.CONN_ORIGH <= INET6_ATON('10.255.255.255')) AND
(bro_conn.CONN_RESPH >= INET6_ATON('10.0.0.0') AND bro_conn.CONN_RESPH <= INET6_ATON('10.255.255.255'))
GROUP BY IP) AS t
GROUP BY IP
/*6A. How many total bytes were recieved by a single host?
Create a table that displays all IPs seen on the network (as source or destination) and
figure out much each of them recieved as a source or destination and total*/
SELECT IP AS SRC_IP,
SUM(CASE WHEN Type = 'SRC' THEN BYTES ELSE 0 END) AS SRC_BYTES,
SUM(CASE WHEN Type = 'DST' THEN BYTES ELSE 0 END) AS DST_BYTES,
SUM(BYTES) AS TOTAL_BYTES
FROM (
SELECT INET6_NTOA(bro_conn.CONN_ORIGH) as IP,
SUM(bro_conn.CONN_RESPIPBYTES) AS BYTES,
'SRC' AS Type
FROM bro_conn
GROUP BY IP
UNION ALL
SELECT INET6_NTOA(bro_conn.CONN_RESPH) as IP,
SUM(bro_conn.CONN_ORIGIPBYTES) AS BYTES,
'DST' AS Type
FROM bro_conn GROUP BY IP) AS t
GROUP BY IP
/*6B. How many total bytes were recieved by a single host (internal host to internal host)?
Create a table that displays all IPs seen on the network (as source or destination) and
figure out much each of them recieved as a source or destination and total
(Filter for Internal to Internal only)*/
SELECT IP AS SRC_IP,
SUM(CASE WHEN Type = 'SRC' THEN BYTES ELSE 0 END) AS SRC_BYTES,
SUM(CASE WHEN Type = 'DST' THEN BYTES ELSE 0 END) AS DST_BYTES,
SUM(BYTES) AS TOTAL_BYTES
FROM (
SELECT INET6_NTOA(bro_conn.CONN_ORIGH) as IP,
SUM(bro_conn.CONN_RESPIPBYTES) AS BYTES,
'SRC' AS Type
FROM bro_conn
WHERE (bro_conn.CONN_ORIGH >= INET6_ATON('10.0.0.0') AND bro_conn.CONN_ORIGH <= INET6_ATON('10.255.255.255')) AND
(bro_conn.CONN_RESPH >= INET6_ATON('10.0.0.0') AND bro_conn.CONN_RESPH <= INET6_ATON('10.255.255.255'))
GROUP BY IP
UNION ALL
SELECT INET6_NTOA(bro_conn.CONN_RESPH) as IP,
SUM(bro_conn.CONN_ORIGIPBYTES) AS BYTES,
'DST' AS Type
FROM bro_conn
WHERE (bro_conn.CONN_ORIGH >= INET6_ATON('10.0.0.0') AND bro_conn.CONN_ORIGH <= INET6_ATON('10.255.255.255')) AND
(bro_conn.CONN_RESPH >= INET6_ATON('10.0.0.0') AND bro_conn.CONN_RESPH <= INET6_ATON('10.255.255.255'))
GROUP BY IP) AS t
GROUP BY IP