Hi Federico,
Thanks for getting in touch and bringing this up. More than a bug, you
are running in an aspect about SQL tables that is poorly documented (i
will try to improve that as a follow-up). The only vague mentioning of
what you are running into is here:
https://github.com/pmacct/pmacct/blob/master/sql/README.mysql#L50
Essentially, before fixed schema v6 both IP addresses and ASNs were
written in the same field, that is ip_src and ip_dst. What the
intersection of "sql_table_schema", "sql_optimize_clauses: true" and
"sql_table_version: 9" does is to enable writing to custom schemas
(sql_table_schema and sql_optimize_clauses) using the v9 style rather
than the default, v1 (sql_table_version), hence using the as_src /
as_dst fields for storing ASNs.
Paolo
On 3/10/22 19:20, Federico Urtizberea wrote:
Hello everyone, and thanks for reading this.
I work at a small ISP, and am trying to use PMACCT to get some metrics
from our network and get a better understanding of how our traffic is
flowing.
Our network is quite simple, we do not transit, all incoming and
outgoing traffic is generated by our clients and is almost IPv4 (or that
is what we think, one of the reason to try to deploy PMACCT). So my
first attempt was to compare the metrics collected with PMACCT to well
known data such as the metrics collected by SNMP, by comparing the
incoming and outgoing traffic from our ASN.
The collector is configured like this (nfacctd 1.7.7-git (RELEASE)):
daemonize: false
debug: true
nfacctd_port: 2100
nfacctd_pro_rating: true
nfacctd_renormalize: true
nfacctd_time_new: true
plugin_buffer_size: 102400
plugin_pipe_size: 8519680
propagate_signals: true
timestamps_secs: true
plugins: mysql[in],mysql[out]
aggregate[in]: dst_as
sql_db[in]: pmacct
sql_dont_try_update[in]: true
sql_history[in]: 1m
sql_history_roundoff[in]: m
sql_host[in]: 127.0.0.1
sql_multi_values[in]: 1000000
sql_optimize_clauses[in]: true
sql_passwd[in]: arealsmartpwd
sql_port[in]: 3306
sql_preprocess[in]: minp=1,adjb=30
sql_refresh_time[in]: 60
sql_table[in]: asn_in_%Y%m%d
sql_table_schema[in]: /etc/pmacct/asn_in.schema
sql_table_version[in]: 9
sql_user[in]: pmacct
aggregate[out]: src_as
sql_db[out]: pmacct
sql_dont_try_update[out]: true
sql_history[out]: 1m
sql_history_roundoff[out]: m
sql_host[out]: 127.0.0.1
sql_multi_values[out]: 1000000
sql_optimize_clauses[out]: true
sql_passwd[out]: arealsmartpwd
sql_port[out]: 3306
sql_preprocess[out]: minp=1,adjb=30
sql_refresh_time[out]: 60
sql_table[out]: asn_out_%Y%m%d
sql_table_schema[out]: /etc/pmacct/asn_out.schema
sql_table_version[out]: 9
sql_user[out]: pmacct
The custom schema for the sql tables are:
* /etc/pmacct/asn_in.schema
CREATE TABLE asn_in_%Y%m%d (
`as_dst` int(4) unsigned NOT NULL,
`packets` int(10) unsigned NOT NULL,
`bytes` bigint(20) unsigned NOT NULL,
`stamp_inserted` datetime NOT NULL,
`stamp_updated` datetime DEFAULT NULL,
PRIMARY KEY (`stamp_inserted`,`stamp_updated`,`as_dst`),
INDEX a (as_dst)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
* /etc/pmacct/asn_out.schema
CREATE TABLE asn_out_%Y%m%d (
`as_src` int(4) unsigned NOT NULL,
`packets` int(10) unsigned NOT NULL,
`bytes` bigint(20) unsigned NOT NULL,
`stamp_inserted` datetime NOT NULL,
`stamp_updated` datetime DEFAULT NULL,
PRIMARY KEY (`stamp_inserted`,`stamp_updated`,`as_src`),
INDEX a (as_src)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
So far in the PMACCT documentation and the threads I read, to use custom
sql tables, the only settings I understood needed to be set in the
PMACCT config were
(https://github.com/pmacct/pmacct/blob/41f7ef4d1e156873361ebd772ccb07ed7efd0238/QUICKSTART#L341):
sql_optimize_clauses: true
sql_table: <table name>
aggregate: <aggregation primitives list>
But if I just do that, and use the sql schemas detailed above, I get the
following error:
INFO ( in/mysql ): *** Purging cache - START (PID: 84) ***
INFO ( out/mysql ): *** Purging cache - START (PID: 85) ***
DEBUG ( in/mysql ): 5071 VALUES statements sent to the MySQL server.
ERROR ( in/mysql ): Unknown column 'ip_dst' in 'field list'
INFO ( in/mysql ): *** Purging cache - END (PID: 84, QN: 5070/5071, ET:
0) ***
DEBUG ( out/mysql ): 5199 VALUES statements sent to the MySQL server.
ERROR ( out/mysql ): Unknown column 'ip_src' in 'field list'
INFO ( out/mysql ): *** Purging cache - END (PID: 85, QN: 5198/5199, ET:
0) ***
Because of that, i need to use one of these two directives to avoid this
error, sql_table_version with version 9 (is the only I have tested) or
sql_table_type with table type bgp.
Is it a bug o i have missed anything in the docs and i need to configure
one of these directives to make it work?
Regards,
Federico
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
