Skip to content

03 MySQL FAQ

Jump to bottom
pash! edited this page Sep 4, 2018 · 9 revisions

NTOP

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
Q: How can i generate data sctructure for MySQL database?
A: You are not need any manual creation (exept CREATE DATABASE). All tables will be created at ntopng startup. They are two tables (one for IPv4 and one for IPv6) partitioned in sub-tables.
Q: Which is the column structure in MySQL?
A: Columns are as follows:
| idx | VLAN_ID | L7_PROTO | IP_SRC_ADDR | L4_SRC_PORT | IP_DST_ADDR | L4_DST_PORT | PROTOCOL | IN_BYTES | OUT_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','blog.ntop.org@46.101.157.119',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": "blog.ntop.org@46.101.157.119" }'))
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
Clone this wiki locally