03 MySQL FAQ

NTOPNG - MySQL FAQ

You can instruct ntopng to save flow information to a MySQL Database. This enables you to:

• Visualize historical information using ntopng
• Create your own reports using data stored in MySQL

---

Q: How can I enable MySQL Historical interface in ntopng?

A: To enable this feature you need to start ntopng with the -F flag:

Example:
   $ ntopng -F "mysql;localhost;ntopng;flows;root;1234"
Format:
   $ ntopng -F "mysql;{host|socket};{dbname};{table name};{user};{pw}"

Before executing, you should create a database (and optionally user /w rights):

CREATE DATABASE IF NOT EXISTS ntopng;

---

Q: How can configure data retention / rotation?

A: You can specify the retention period in the preferences menu (UI)

---

Q: How do I display Historical data from MySQL in ntopng?

A: You can by clicking on the graph icon inside the host view

Example:
http://localhost:3000/lua/host_details.lua?ifname=0&host=192.168.1.92&page=historical

---

Q: How is the data structured in MySQL?

A: Flows are split between V4 and V6 tables:

flowsv4
flowsv6

A: Columns are as follows:

| idx | VLAN_ID | L7_PROTO | IP_SRC_ADDR | L4_SRC_PORT | IP_DST_ADDR | L4_DST_PORT | PROTOCOL | BYTES | PACKETS | FIRST_SWITCHED | LAST_SWITCHED | INFO | JSON    

A sample INSERT follows for illustration. Note data in the JSON column is compressed:

INSERT INTO `flowsv4_0` (VLAN_ID,L7_PROTO,IP_SRC_ADDR,L4_SRC_PORT,IP_DST_ADDR,L4_DST_PORT,PROTOCOL,BYTES,PACKETS,FIRST_SWITCHED,LAST_SWITCHED,INFO,JSON) VALUES ('0','5','3232235777','53','3232235799','13562','17','198','1','1443380163','1443380163','[email protected]',COMPRESS('{ "8": "192.168.1.1", "7": 53, "12": "192.168.1.23", "11": 13562, "4": 17, "57590": 5, "57591": "DNS", "2": 1, "1": 198, "24": 0, "23": 0, "22": 1443380163, "21": 1443380163, "DNS_QUERY": "[email protected]" }'))

---

---

Q: I get error "WARNING: Discarding -F mysql:... value out of range"

A: You need are missing mysqlclient libraries - install them and recompile ntopng.

Debian:
apt-get install libmysqlclient-dev

CentOS:
yum install mysql-devel