Netflow not working pmacct to Postgress

[rohanrajnv]

Description I am trying to install nfacctd and pfacctd to export the flow data to postgres. I am getting the following the error when I export the data. Config:

Command: nfacctd -P pgsql -d -l 2055 -f nfacctd.conf DEBUG ( default/core ): Processing NetFlow/IPFIX flowset [262] from [192.168.30.254:3976] seqno [20311401] DEBUG ( default/core ): Processing NetFlow/IPFIX flowset [258] from [192.168.30.254:3976] seqno [20311401] DEBUG ( default/core ): Processing NetFlow/IPFIX flowset [258] from [192.168.30.254:3976] seqno [20311401] DEBUG ( default/core ): Processing NetFlow/IPFIX flowset [262] from [192.168.30.254:3976] seqno [20311401] ^C( default_pgsql/pgsql ) Purging queries queue INFO ( default_pgsql/pgsql ): Purging cache - START (PID: 109609) DEBUG ( default_pgsql/pgsql ): FAILED query follows: UPDATE acct SET packets=packets+18, bytes=bytes+14098, flows=flows+0, stamp_updated=CURRENT_TIMESTAMP(0) WHERE to_timestamp(1687484580)::Timestamp without time zone = stamp_inserted AND vlan_in=0 AND ip_src='172.217.171.202' AND as_src=0 AND as_dst=0 AND port_src=0 AND port_dst=0 AND tos=0 AND ip_proto=0 AND tag=0 AND class_id='unknown' AND mac_src='0:0:0:0:0:0' AND mac_dst='0:0:0:0:0:0' AND ip_dst='0.0.0.0' ERROR ( default_pgsql/pgsql ): ERROR: column "vlan_in" does not exist LINE 1: ...:Timestamp without time zone = stamp_inserted AND vlan_in=0 ..

Version NetFlow Accounting Daemon, nfacctd 1.7.9-git [20230620-1 (f91700a9)] Arguments: '--enable-pgsql' '--enable-geoipv2' '--enable-debug' '--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) PostgreSQL 140008 MaxmindDB 1.7.1 Plugins: memory print nfprobe sfprobe tee postgresql System: Linux 5.15.0-72-generic #79-Ubuntu SMP Wed Apr 19 08:22:18 UTC 2023 x86_64 Compiler: gcc 11.3.0

[paololucente]

Hi @rohanrajnv ,

Thanks for reporting this issue, can you please share your config?

Paolo

[rohanrajnv]

Hi I created a custom table with all the column and it resolved the issue however I have got into a similar issue relayed to other column. This is the same of my NetFlow Packet: Extracted from pmacct using Json plugin. _{"event_type": "purge", "tag": 0, "tag2": 0, "label": "", "class": "unknown", "mac_src": "00:00:00:00:00:00", "mac_dst": "00:00:00:00:00:00", "vlan_in": 0, "cos": 0, "etype": "800", "as_src": 0, "as_dst": 0, "comms": "", "ecomms": "", "lcomms": "", "as_path": "", "local_pref": 0, "med": 0, "roa_dst": "u", "peer_as_src": 0, "peer_as_dst": 0, "peer_ip_src": "192.168.30.254", "peer_ip_dst": "", "comms_src": "", "ecomms_src": "", "lcomms_src": "", "as_path_src": "", "local_pref_src": 0, "med_src": 0, "roa_src": "u", "iface_in": 9, "iface_out": 19, "mpls_vpn_rd": "0:0:0", "mpls_pw_id": 0, "ip_src": "X.X.X.X", "net_src": "0.0.0.0", "ip_dst": "X.X.X.X", "net_dst": "0.0.0.0", "mask_src": 0, "mask_dst": 0, "port_src": 443, "port_dst": 47104, "country_ip_src": "SG", "country_ip_dst": "", "pocode_ip_src": "", "pocode_ip_dst": "", "lat_ip_src": 0.0, "lon_ip_src": 0.0, "lat_ip_dst": 0.0, "lon_ip_dst": 0.0, "tcp_flags": "0", "fwd_status": "64", "mpls_label_stack": "", "path_delay_avg_usec": 0, "path_delay_min_usec": 0, "path_delay_max_usec": 0, "ip_proto": "tcp", "tos": 0, "flow_label": 0, "sampling_rate": 1, "sampling_direction": "u", "post_nat_ip_src": "0.0.0.0", "post_nat_ip_dst": " X.X.X.X", "post_nat_port_src": 0, "post_nat_port_dst": 47104, "nat_event": 0, "fw_event": 0, "mpls_label_top": 0, "mpls_label_bottom": 0, "tunnel_ip_src": "", "tunnel_ip_dst": "", "tunnel_ip_proto": "0", "tunnel_tos": 0, "tunnel_flow_label": 0, "tunnel_port_src": 0, "tunnel_port_dst": 0, "tunnel_tcp_flags": "0", "vxlan": 0, "timestamp_start": "2023-06-28 21:21:12.000000", "timestamp_end": "2023-06-28 21:21:15.000000", "timestamp_arrival": "2023-06-28 21:21:14.512021", "export_proto_seqno": 35142671, "export_proto_version": 9, "export_proto_sysid": 1, "timestamp_export": "2023-06-28 21:21:16.000000", "stamp_inserted": "2023-06-28 21:21:00", "stampupdated": "2023-06-28 21:21:14", "flows": 1, "packets": 6, "bytes": 608}

I wanted to dump all the data to the Postgres SQL and with all the fields supported by nfacctd My Config: _aggregate: cos, etype, vlan, out_vlan, src_host, dst_host, proto, tos, timestamp_start, timestamp_end, timestamp_arrival, timestamp_export, path_delay_avg_usec, path_delay_min_usec, path_delay_max_usec, peer_src_ip,sampling_rate, src_host_country,in_iface plugins: pgsql nfacctd_time_new: true !nfacctd_account_options: true nfacctd_port: 2055 sql_dont_try_update: true sql_max_writers: 40 sql_user: root sql_passwd: vivid123 sql_refresh_time: 5 !sql_history: 10m !sql_history_roundoff: mh sql_table_version: 9 sql_table: acct_netflow_v1 geoipv2_file: /usr/share/GeoIP/GeoLite2-Country.mmdb logfile: /home/vsrsadmin/nfacctd.log sql_optimizeclauses: true

The Config show the fields I have added the to the aggregate. The following is the database that I have created. _CREATE TABLE public.acct_netflow_v1 ( tag int8 NOT NULL DEFAULT 0, class_id bpchar(16) NOT NULL DEFAULT ' '::bpchar, mac_src macaddr NOT NULL DEFAULT '00:00:00:00:00:00'::macaddr, mac_dst macaddr NOT NULL DEFAULT '00:00:00:00:00:00'::macaddr, vlan int4 NOT NULL DEFAULT 0, as_src int8 NOT NULL DEFAULT 0, as_dst int8 NOT NULL DEFAULT 0, ip_src inet NOT NULL DEFAULT '0.0.0.0'::inet, ip_dst inet NOT NULL DEFAULT '0.0.0.0'::inet, port_src int4 NOT NULL DEFAULT 0, port_dst int4 NOT NULL DEFAULT 0, tcp_flags int2 NOT NULL DEFAULT 0, ip_proto int2 NOT NULL DEFAULT 0, tos int4 NOT NULL DEFAULT 0, packets int4 NOT NULL, bytes int8 NOT NULL, flows int4 NOT NULL DEFAULT 0, stamp_inserted timestamp NOT NULL DEFAULT '0001-01-01 00:00:00'::timestamp without time zone, stamp_updated timestamp NULL, vlan_in int4 NOT NULL DEFAULT 0, cos int4 NOT NULL DEFAULT 0, etype bpchar(16) NOT NULL DEFAULT ' '::bpchar, net_src inet NOT NULL DEFAULT '0.0.0.0'::inet, net_dst inet NOT NULL DEFAULT '0.0.0.0'::inet, mask_src int4 NOT NULL DEFAULT 0, mask_dst int4 NOT NULL DEFAULT 0, post_nat_ip_src text NOT NULL DEFAULT '0.0.0.0'::inet, post_nat_ip_dst text NOT NULL DEFAULT '0.0.0.0'::inet, post_nat_port_src int4 NOT NULL DEFAULT 0, post_nat_port_dst int4 NOT NULL DEFAULT 0, nat_event int8 NOT NULL DEFAULT 0, fw_event int8 NOT NULL DEFAULT 0, flow_label int8 NOT NULL DEFAULT 0, tunnel_ip_src text NOT NULL DEFAULT '0.0.0.0'::inet, tunnel_ip_dst text NOT NULL DEFAULT '0.0.0.0'::inet, tunnel_ip_proto int2 NOT NULL DEFAULT 0, tunnel_tos int8 NOT NULL DEFAULT 0, tunnel_port_dst int8 NOT NULL DEFAULT 0, tunnel_port_src int8 NOT NULL DEFAULT 0, tunnel_flow_label int8 NOT NULL DEFAULT 0, tunnel_tcp_flags int4 NOT NULL DEFAULT 0, iface_in int8 NOT NULL DEFAULT 0, comms bpchar(24) NOT NULL DEFAULT ' '::bpchar, peer_as_src int8 NOT NULL DEFAULT 0, peer_as_dst int8 NOT NULL DEFAULT 0, peer_ip_src inet NOT NULL DEFAULT '0.0.0.0'::inet, peer_ip_dst inet NOT NULL DEFAULT '0.0.0.0'::inet, tag2 int8 NOT NULL DEFAULT 0, iface_out int8 NOT NULL DEFAULT 0, ecomms bpchar(24) NOT NULL DEFAULT ' '::bpchar, lcomms bpchar(24) NOT NULL DEFAULT ' '::bpchar, comms_src bpchar(24) NOT NULL DEFAULT ' '::bpchar, ecomms_src bpchar(24) NOT NULL DEFAULT ' '::bpchar, lcomms_src bpchar(24) NOT NULL DEFAULT ' '::bpchar, as_path_src bpchar(21) NOT NULL DEFAULT ' '::bpchar, as_path bpchar(21) NOT NULL DEFAULT ' '::bpchar, local_pref int8 NOT NULL DEFAULT 0, local_pref_src int8 NOT NULL DEFAULT 0, med int8 NOT NULL DEFAULT 0, med_src int4 NOT NULL DEFAULT 0, roa_src bpchar(1) NOT NULL DEFAULT ' '::bpchar, mpls_vpn_rd bpchar(21) NOT NULL DEFAULT ' '::bpchar, mpls_pw_id int8 NOT NULL DEFAULT 0, country_ip_src bpchar(2) NOT NULL DEFAULT ',--'::bpchar country_ip_dst bpchar(2) NOT NULL DEFAULT ',--'::bpchar pocode_ip_src bpchar(12) NOT NULL DEFAULT ' '::bpchar, pocode_ip_dst bpchar(12) NOT NULL DEFAULT ' '::bpchar, timestamp_start timestamp NOT NULL DEFAULT '0001-01-01 00:00:00'::timestamp without time zone, timestamp_end timestamp NOT NULL DEFAULT '0001-01-01 00:00:00'::timestamp without time zone, timestamp_arrival timestamp NOT NULL DEFAULT '0001-01-01 00:00:00'::timestamp without time zone, timestamp_export timestamp NOT NULL DEFAULT '0001-01-01 00:00:00'::timestamp without time zone, timestamp_start_residual int8 NOT NULL DEFAULT 0, timestamp_end_residual int8 NOT NULL DEFAULT 0, timestamp_arrival_residual int8 NOT NULL DEFAULT 0, timestamp_export_residual int8 NOT NULL DEFAULT 0, fwd_status int8 NOT NULL DEFAULT 0, path_delay_avg_usec int8 NOT NULL DEFAULT 0, path_delay_min_usec int8 NOT NULL DEFAULT 0, path_delay_max_usec int8 NOT NULL DEFAULT 0, mpls_label_top int8 NOT NULL DEFAULT 0, mpls_label_bottom int8 NOT NULL DEFAULT 0, vxlan int4 NOT NULL DEFAULT 0, lat_ip_src float8 NOT NULL DEFAULT 0, lat_ip_dst float8 NOT NULL DEFAULT 0, lon_ip_src float8 NOT NULL DEFAULT 0, lon_ip_dst float8 NOT NULL DEFAULT 0, sampling_rate int8 NOT NULL DEFAULT 0, sampling_direction bpchar(1) NOT NULL DEFAULT ' '::bpchar, export_proto_seqno int8 NOT NULL DEFAULT 0, export_proto_version int4 NOT NULL DEFAULT 0, export_proto_sysid int8 NOT NULL DEFAULT 0, "label" varchar(255) NOT NULL DEFAULT ' '::character varying, vlan_out int8 NOT NULL DEFAULT 0, src_port int4 NOT NULL DEFAULT 0, dstport int4 NOT NULL DEFAULT 0, tcpflags int4 NOT NULL DEFAULT 0 );

But the moment I add any field I get the following error in the log; it does not let me add any more fields to the aggregate _2023-06-28T21:25:46Z INFO ( default_pgsql/pgsql ): Purging cache - START (PID: 21218) 2023-06-28T21:25:46Z ERROR ( default_pgsql/pgsql ): ERROR: syntax error at or near "," LINE 1: , packets, bytes) VALUES (0, 0, 0, '800', 'X.X.X.X', '... ^ 2023-06-28T21:25:46Z INFO ( default_pgsql/pgsql ): Purging cache - END (PID: 21218, QN: 0/859, ET: 0) 2023-06-28T21:25:51Z INFO ( default_pgsql/pgsql ): Purging cache - START (PID: 21225) 2023-06-28T21:25:51Z ERROR ( default_pgsql/pgsql ): ERROR: syntax error at or near "," LINE 1: , packets, bytes) VALUES (0, 0, 0, '800', 'X.X.X.X', '5... ^ 2023-06-28T21:25:51Z INFO ( default_pgsql/pgsql ): Purging cache - END (PID: 21225, QN: 0/2204, ET: 0) 2023-06-28T21:25:54Z INFO ( default_pgsql/pgsql ): Purging cache - START (PID: 21215) 2023-06-28T21:25:54Z ERROR ( defaultpgsql/pgsql ): ERROR: syntax error at or near "," LINE 1: , packets, bytes) VALUES (0, 0, 0, '800', 'X.X.X.X', ... ^ Any Suggestions why I am unable to the add any other fields?

[paololucente]

Hi @rohanrajnv ,

This may be due to the fact that buffer length is fixed. While there is no buffer overflow, you are not warned of the condition. Can you enable debug (debug: true in the config)? It should return you the SQL string being sent over, it should be easy to reckon whether it gets cut at some point.

Paolo