Re: [pmacct-discussion] Doubt about custom sql tables
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]: 100 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]: 100 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: aggregate: 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
Re: [pmacct-discussion] icmp6 netflow 9 not including type & code sometimes
Hi, Best would be for me to be able to reproduce the issue; can you make a brief capture in pcap format (ie. with tcpdump) of some of this icmp6 traffic and send it over via unicast email? If you could even compose two traces, one for the interface that is working, one for the one that is not working that would be awesome. Paolo On 27/9/22 01:27, fireballiso wrote: More information: pmacctd -V Promiscuous Mode Accounting Daemon, pmacctd 1.7.9-git [RELEASE] Arguments: '--enable-l2' '--enable-traffic-bins' '--enable-bgp-bins' '--enable-bmp-bins' '--enable-st-bins' Libs: cdada 0.4.0 libpcap version 1.10.1 (with TPACKET_V3) Plugins: memory print nfprobe sfprobe tee System: Linux 5.19.9-200.fc36.x86_64 #1 SMP PREEMPT_DYNAMIC Thu Sep 15 09:49:52 UTC 2022 x86_64 Compiler: gcc 12.2.1 === Config file (sending netflow to IPv6 loopback interface for capture with nfcapd): ! daemonize: true ! pcap_interface: eth0 aggregate: src_host, dst_host, src_port, dst_port, proto, tcpflags, tos plugins: nfprobe nfprobe_receiver: [::1]:9995 nfprobe_version: 9 = Still, the netflow captured with the config above doesn't have the icmp6 type and code values set correctly, but are always zeros. On 9/25/2022 10:21 PM, fireballiso wrote: Hi! I use pmacctd to generate netflow 9 for two interfaces on a physical (not virtual) Linux machine. The flows from one interface shows icmp and icmp6 protocols with the type and code as expected in the dst_port, and the other interface only shows icmp type and code correctly; the icmp6 type and code are always 0, regardless of the true values. Another machine (a VMWare virtual machine, running on ESXi 7) generates netflow 9 for an interface that only has IPv6 addresses; this also shows the icmp6 type and code as always 0. The interfaces on both machines have identical pmacctd configurations (except for the interface names), and the pmacctd versions are identical (cloned from github). What would cause the icmp6 type and code to not be set correctly for two interfaces, but correctly for another one? -Indy ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
[pmacct-discussion] Doubt about custom sql tables
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]: 100 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]: 100 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: aggregate: 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
