[pmacct-discussion] stamp_inserted
Hi, I use this scerario: sql_refresh_time: 120 sql_history: 2m sql_history_roundoff: m sql_dont_try_update: true nfacctd_pro_rating: true aggregate[router1.all.in]: src_host,dst_host,proto,src_port,dst_port,timestamp_start,timestamp_end aggregate[router1.all.out]: src_host,dst_host,proto,src_port,dst_port,timestamp_start,timestamp_end aggregate[router1.sums.in]: dst_host aggregate[router1.sums.out]: src_host plugins: mysql[router1.all.in],mysql[router1.all.out],mysql[router1.sums.in],mysql[router1.sums.out] sql_table[router1.all.in]: %Y%m%d_router1_in sql_table[router1.all.out]: %Y%m%d_router1_out sql_table[router1.sums.in]: %Y_router1_sums_in sql_table[router1.sums.out]: %Y_router1_sums_out sql_startup_delay[router1.all.in]: 240 sql_startup_delay[router1.all.out]: 240 sql_startup_delay[router1.sums.in]: 240 sql_startup_delay[router1.sums.out]: 240 in tables %Y%m%d_router1_in and %Y%m%d_router1_out I have columns stamp_inserted and stamp_updated, but I don´t need them, because I aggregate nothing. timestamp_start and timestamp_end is enough. In these tables I need to store everything for best accuracy when finding problems. In sums tables I don´t need column stamp_updated. Is there any way to not store these columns? Thank you, Jaroslav ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] configurafion
Dne 2016-05-25 09:32, Paolo Lucente napsal: Hi Jaroslav, To increase precision beyond historical accounting, ie. stamp_inserted and stamp_updated, you can use timestamp_start and timestamp_end keys in your aggregation method. But, as you will see, while you will increase precision, you will increase the amount of data - whether this is a better trade-off, it depends on your project. Yes, i do it this way now. I store timestamp_start and timestamp_end. Maybe if stamp_inserted could be starttime of first flow, which belongs to this time-bin and stamp_updated endtime of last flow of this time-bin ? I'm looking for ways to reduce my database size (I store 26GB per day now - with indexes). Is there way to not store timestamp_end but only flow time (timestamp_end - timestamp_start)? Thank you, Jaroslav The current 120 secs 'lag' makes sense to me: it takes the router 60 secs to export to pmacct, then it takes pmacct another 60 secs to write it to disk (sql_refresh_time: 60). Finally, sql_history_roundoff it's just a way to nicely align time bins; you basically want that always on. If you disable it, you configure 5 mins time bins and start pmacct at 9:32am then time-bins will be falling at 9:32, 9:37, 9:42, etc. with the feature on, it will nicely align to the hour and give you time bins like 9:30, 9:35, etc. Paolo On Wed, May 18, 2016 at 10:50:48PM +0200, Jaroslav Jirásek wrote: Hi, I am trying to store flows to mysql, I have this nfacctd config: nfacctd_time_new: true aggregate: src_host,dst_host,post_nat_src_host,post_nat_dst_host,proto,src_port,dst_port plugins: mysql sql_db: pmacct sql_user: pmacct sql_passwd: sql_optimize_clauses: true sql_table: acct_%Y%m%d_%H sql_table_schema: /etc/pmacct/acct.schema sql_refresh_time: 60 sql_history: 1m sql_history_roundoff: m sql_dont_try_update: true sql_multi_values: 1048576 sql_startup_delay: 60 file acct.schema: CREATE TABLE acct_%Y%m%d_%H ( ip_src CHAR(15) NOT NULL, ip_dst CHAR(15) NOT NULL, post_nat_ip_src CHAR(15) NOT NULL, post_nat_ip_dst CHAR(15) NOT NULL, src_port SMALLINT UNSIGNED NOT NULL DEFAULT 0, dst_port SMALLINT UNSIGNED NOT NULL DEFAULT 0, ip_proto SMALLINT UNSIGNED NOT NULL DEFAULT 0, packets INT UNSIGNED NOT NULL, bytes BIGINT UNSIGNED NOT NULL, stamp_inserted DATETIME NOT NULL DEFAULT '-00-00 00:00:00', stamp_updated DATETIME, KEY (ip_src, stamp_inserted), KEY (ip_dst, stamp_inserted) ); I have flow timeout 60 secs on flow exporter (mikrotik). I want one table per hour. I want best possible sql performance, so directive sql_dont_try_update is important for me (I thing). But I also need best possible time accurancy of flow start and end in database. Now every flow exported in database takes two minutes: (stamp_inserted: 2016-05-18 20:00:00 stamp_updated: 2016-05-18 20:02:01) although I make short data transfer (several seconds long) Is there any way to improve time accurancy? Last records in database has start time for example 2016-05-18 19:59:00 and end 2016-05-18 20:01:01, but last one minute belongs to next mysql table. Is there any way to have one hour table with flows which belongs in this hour? subtraction stamp_updated-stamp_inserted is always 121 seconds, i thing I dont need stamp_updated in database? Btw why is difference 121 secs ? It may be 120 secs... I try scenario without sql_history, with 'nfacctd_time_new: false' and with 'sql_dont_try_update: false', but in this setting I got table name in mysql acct_19700101_01, why right date is not used? i don't understand directive sql_history_roundoff, can you explain it to me? Thank you for help and suggestions Jaroslav ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
[pmacct-discussion] filtering flows
I'm receiving flows from mikrotik by nfacctd. I want split inbound and outboud traffic to different tables. I'm using directives aggregate_filter[local.out]: src net 10.0.0.0/8 aggregate_filter[local.in]: dst net 10.0.0.0/8 it works only for outbound traffic, because in flows is DstAddr ip address of NAT (100.100.100.1), look here: Flow 2 [Duration: 2.01000 seconds] StartTime: 12542.3 seconds EndTime: 12544.31000 seconds Packets: 5 Octets: 300 InputInt: 15 OutputInt: 2 SrcAddr: 10.0.124.51 (10.0.124.51) DstAddr: 8.8.8.8 (8.8.8.8) Protocol: 1 IP ToS: 0x00 SrcPort: 0 DstPort: 0 NextHop: 172.20.3.73 (172.20.3.73) DstMask: 0 SrcMask: 0 TCP Flags: 0x00 Destination Mac Address: Routerbo_45:49:79 (00:0c:42:45:49:79) Post Source Mac Address: Routerbo_45:49:78 (00:0c:42:45:49:78) Post NAT Source IPv4 Address: 100.100.100.1 (100.100.100.1) Post NAT Destination IPv4 Address: 8.8.8.8 (8.8.8.8) Post NAPT Source Transport Port: 0 Post NAPT Destination Transport Port: 0 Flow 3 [Duration: 3.01000 seconds] StartTime: 12542.3 seconds EndTime: 12545.31000 seconds Packets: 5 Octets: 300 InputInt: 2 OutputInt: 15 SrcAddr: 8.8.8.8 (8.8.8.8) DstAddr: 100.100.100.1 (100.100.100.1) Protocol: 1 IP ToS: 0x00 SrcPort: 0 DstPort: 0 NextHop: 10.0.124.51 (10.0.124.51) DstMask: 0 SrcMask: 0 TCP Flags: 0x00 Destination Mac Address: Routerbo_45:49:78 (00:0c:42:45:49:78) Post Source Mac Address: Routerbo_45:49:79 (00:0c:42:45:49:79) Post NAT Source IPv4 Address: 8.8.8.8 (8.8.8.8) Post NAT Destination IPv4 Address: 10.0.124.51 (10.0.124.51) Post NAPT Source Transport Port: 0 Post NAPT Destination Transport Port: 0 user 10.0.124.51 is behind nat, nat makes router which sends flows too Is it possible to make filter on "Post NAT Destination IPv4 Address" ? Thank you for help Jaroslav Jirasek ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
[pmacct-discussion] configurafion
Hi, I am trying to store flows to mysql, I have this nfacctd config: nfacctd_time_new: true aggregate: src_host,dst_host,post_nat_src_host,post_nat_dst_host,proto,src_port,dst_port plugins: mysql sql_db: pmacct sql_user: pmacct sql_passwd: sql_optimize_clauses: true sql_table: acct_%Y%m%d_%H sql_table_schema: /etc/pmacct/acct.schema sql_refresh_time: 60 sql_history: 1m sql_history_roundoff: m sql_dont_try_update: true sql_multi_values: 1048576 sql_startup_delay: 60 file acct.schema: CREATE TABLE acct_%Y%m%d_%H ( ip_src CHAR(15) NOT NULL, ip_dst CHAR(15) NOT NULL, post_nat_ip_src CHAR(15) NOT NULL, post_nat_ip_dst CHAR(15) NOT NULL, src_port SMALLINT UNSIGNED NOT NULL DEFAULT 0, dst_port SMALLINT UNSIGNED NOT NULL DEFAULT 0, ip_proto SMALLINT UNSIGNED NOT NULL DEFAULT 0, packets INT UNSIGNED NOT NULL, bytes BIGINT UNSIGNED NOT NULL, stamp_inserted DATETIME NOT NULL DEFAULT '-00-00 00:00:00', stamp_updated DATETIME, KEY (ip_src, stamp_inserted), KEY (ip_dst, stamp_inserted) ); I have flow timeout 60 secs on flow exporter (mikrotik). I want one table per hour. I want best possible sql performance, so directive sql_dont_try_update is important for me (I thing). But I also need best possible time accurancy of flow start and end in database. Now every flow exported in database takes two minutes: (stamp_inserted: 2016-05-18 20:00:00 stamp_updated: 2016-05-18 20:02:01) although I make short data transfer (several seconds long) Is there any way to improve time accurancy? Last records in database has start time for example 2016-05-18 19:59:00 and end 2016-05-18 20:01:01, but last one minute belongs to next mysql table. Is there any way to have one hour table with flows which belongs in this hour? subtraction stamp_updated-stamp_inserted is always 121 seconds, i thing I dont need stamp_updated in database? Btw why is difference 121 secs ? It may be 120 secs... I try scenario without sql_history, with 'nfacctd_time_new: false' and with 'sql_dont_try_update: false', but in this setting I got table name in mysql acct_19700101_01, why right date is not used? i don't understand directive sql_history_roundoff, can you explain it to me? Thank you for help and suggestions Jaroslav ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
