Name
The database name is myracanyon. It can be easily accessed using the usual psql command-line tool like this:
> which psql
/usr/bin/psql
> sudo --user=postgres psql --dbname=myracanyon
Table: flows
The central table in the myracanyon database is named flows and it is used to track the basic network flow information:
# select * from flows limit 8;
hash | parent | ethertype | protocol | addr_1 | addr_2 | port_1 | port_2 | last_type | type
----------------------+---------------------+-----------+----------+----------+--------------+--------+--------+-----------+----------------------------------
11147961031836640206 | | 2048 | 17 | 10.0.1.3 | 8.8.8.8 | 54203 | 53 | DNS |
IP+
IPv4+
UDP+DNS
8047142775062260162 | | 2048 | 1 | 10.0.1.3 | 192.168.1.3 | | | Ping |
IP+
IPv4+
ICMP+Ping
1646080684073545118 | | 2048 | 17 | 10.0.1.3 | 8.8.8.8 | 39019 | 53 | DNS |
IP+
IPv4+
UDP+DNS
5041954811050855339 | | 2048 | 17 | 10.0.1.3 | 8.8.8.8 | 52781 | 53 | DNS |
IP+
IPv4+
UDP+DNS
(8 rows)
- See JSON: flows.
- Not all columns shown in this example.
- This table is updated every second. See Myra::Server::start_flow_rollup_timer(), Myra::Server::flow_rollup_thread(), and Myra::DB::record_flow() for more details.
- Every network flow is assigned a unique
"network hash" which is the basis for how Myra tracks network traffic, and how everything is tied together in the database. For more information on how network hashes are generated, see Myra::hash().
- The
parent column indicates a relationship between certain flows. In this example, there are 2 FTP-Data flows which are linked back to the FTP control flow.
- The
ethertype column describes layer 3, where 2048 == 0x0800 == ETHERTYPE_IP. See Myra::L2::Ethertype for more details.
- The
protocol column describes layer 4 (when layer 3 is IPv4 or IPv6). See Myra::L3_IP::Protocol for more details.
- The
last_type column contains a single relevant keyword from the set used used to describe a flow. See Table: flow_type for more details.
- The
type column is a textual representation of all the information contained in Table: flow_type. It is provided in the flow table in text form for debugging purposes.
Table: flow_stats
Each active flow has statistics recorded every second. The statistics are stored in flow_stats:
# select * from flow_stats limit 3;
id |
hash | packets_1 | packets_2 | bytes_1 | bytes_2 | last_seen
----+---------------------+-----------+-----------+---------+---------+-------------------------------
51 | 811067209836777060 | 64 | 0 | 16298 | 0 | 2015-01-31 11:38:19.098332-08
52 | 1693080864289198242 | 150 | 0 | 91918 | 0 | 2015-01-31 11:38:19.103605-08
53 | 2239718918368063409 | 6 | 0 | 328 | 0 | 2015-01-31 11:38:19.109000-08
(3 rows)
- See JSON: flow_stats.
- The fields
packets_1, packets_2, bytes_1, and bytes_2 are used to record information on both the incoming and outgoing sides of all network flows.
To get a full count of all packets/bytes in a flow, all relevant statistics rows need to be retrieved:
# select * from flow_stats where hash=1094802619955088632;
id |
hash | packets_1 | packets_2 | bytes_1 | bytes_2 | last_seen
-----+---------------------+-----------+-----------+---------+---------+-------------------------------
12 | 1094802619955088632 | 168 | 0 | 52959 | 0 | 2015-01-31 22:00:03.045393-08
203 | 1094802619955088632 | 2 | 0 | 104 | 0 | 2015-01-31 22:01:04.568638-08
240 | 1094802619955088632 | 4 | 0 | 208 | 0 | 2015-01-31 22:02:04.669402-08
272 | 1094802619955088632 | 2 | 0 | 104 | 0 | 2015-01-31 22:03:04.748431-08
305 | 1094802619955088632 | 3 | 0 | 156 | 0 | 2015-01-31 22:04:04.812498-08
(5 rows)
...or in a single SQL statement:
# select sum(packets_1+packets_2) as packets, sum(bytes_1+bytes_2) as bytes from flow_stats where hash=1094802619955088632;
packets | bytes
---------+-------
179 | 53531
(1 row)
Table: flow_type
Once traffic examination has identified the type of network traffic in a flow, it is also stored in the database. Some simple textual representations are written to Table: flows, while the full details are stored in a table named flow_type:
# select * from flow_type where hash=811067209836777060;
-----+--------------------+---------
805 | 811067209836777060 | 2
806 | 811067209836777060 | 3
807 | 811067209836777060 | 71
808 | 811067209836777060 | 72
809 | 811067209836777060 | 100
810 | 811067209836777060 | 1002
(6 rows)
Table: traffic_type
The type_id column in Table: flow_type can be looked up in the traffic_type table:
# select * from traffic_type order by name limit 8;
id | name
------+-------------
1000 | DNS
(8 rows)
To combine Table: flow_type and Table: traffic_type :
# select flow.id, flow.hash, flow.type_id, type.name from flow_type as flow join traffic_type as type on flow.type_id=type.id where hash=811067209836777060;
id |
hash | type_id | name
-----+--------------------+---------+-----------
807 | 811067209836777060 | 71 |
IP 808 | 811067209836777060 | 72 |
IPv4 809 | 811067209836777060 | 100 |
TCP 810 | 811067209836777060 | 1002 |
SPDY(6 rows)
- It is important to note that every flow can have multiple traffic types assigned to it.
1.8.8