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
