Re: [pmacct-discussion] sql_dont_try_update causes key collisions?
Hi Ed, Yes, indeed sql_preprocess can affect that too, thanks for the note. Paolo On Mon, Mar 06, 2017 at 05:48:24PM -0600, Edward Henigin wrote: > Paolo, > > I assume also that the "aggregates to the backend" could be much smaller > than the "aggregates in memory" when there is a sql_preprocess in place to > filter (e.g. minb) inserts? > > And as far as updating docs, I might suggest adding to the > sql_dont_try_update key the fact that the sql_cache_entries needs to be > large enough to prevent multiple purges per update cycle :-) > > > Ed > > On Mon, Mar 6, 2017 at 4:53 PM, Paolo Lucente wrote: > > > > > Hi Ed, > > > > I should maybe add some documentation in this sense. QN reads as the > > Query Number, the amount of queries (read aggregates) to be processed. > > The two numbers you see are respectively: the amount of aggregates that > > actually made it to the backend and the total amount of aggregates in > > memory at this point. In the typical chase where sql_history matches > > sql_refresh_time these numbers should coincide; another case for these > > not coincide is the box running pmacct or the routers (or both) are not > > ntp'ed or not set to the same timezone or so (as Q18 of FAQS says, the > > recommendation in this sense is to run all as UTC); a simple counter- > > measure is to set 'nfacctd_time_new: true' so to assign flows to time- > > bins basing on the arrival time to pmacct rather than the start time of > > the flow (you loose a bit of accuracy in favor of simplicity, depending > > on the use-case this could be allright). ET is the Elapsed Time, the > > time (in seconds) it took to write all to the backend. PID, intuitively > > and only for completeness, is the Process ID of the writer process. > > > > Paolo > > > > On Sun, Mar 05, 2017 at 01:55:54PM -0600, Edward Henigin wrote: > > > I should have said that I know why Sqlite3 generates the error, I just > > > didn't know why nfacctd was performing duplicate inserts :-) > > > > > > Thank you Paolo, increasing sql_cache_entries makes a big difference. > > > Running at 524287 and the problem seems to be gone. > > > > > > Can you tell me (or point me to the documentation) regarding how to read > > > the 'purging' log line? > > > > > > e.g. > > > > > > Mar 5 13:47:04 server nfacctd[28824]: INFO ( ip_dst/sqlite3 ): *** > > Purging > > > cache - END (PID: 28824, QN: 577/284209, ET: 2) *** > > > > > > I'm curious what the QN: 577/284209 part means. > > > > > > > > > > > > On Fri, Mar 3, 2017 at 8:14 PM, Paolo Lucente wrote: > > > > > > > > > > > +1 on Tristan's feedback. Ed, you can check at this propo also: > > > > > > > > https://github.com/pmacct/pmacct/wiki/RDBMS:- > > Customising-the-SQL-database- > > > > indexes > > > > > > > > If commenting out sql_dont_try_update makes things work well then it > > > > means the setup is making use of UPDATE queries. Maybe you need a > > larger > > > > sql_cache_entries value if you reckon from the logs it is purging more > > > > often than once per minute? > > > > > > > > Paolo > > > > > > > > On Fri, Mar 03, 2017 at 11:55:07PM +, Tristan Bendall wrote: > > > > > Hi Edward > > > > > > > > > > I think what is happening here, in database speak, is that the > > primary > > > > key for the new record isn't unique, and that's breaking DB rules. > > > > > > > > > > Basically the DB is trying to add a new record that already exists, > > and > > > > with update turned off, it can't either update the matching record or > > add > > > > another non unique record. > > > > > > > > > > I think you'll need to add a unique field (such as an auto > > incrementing > > > > ID field) then include that in the primary key contstraint in the DB. > > > > > > > > > > > > > > > Tristan > > > > > > > > > > On 3 Mar 2017, at 17:49, Edward Henigin mailto:ed@ > > > > eaohana.com>> wrote: > > > > > > > > > > Hi Paolo, > > > > > > > > > > When enabling sql_dont_try_update: true, I get these errors fairly > > > > continuously: > > > > > > > > > > Mar 3 11:33:30 server nfacctd[10661]: ERROR ( ip_dst/sqlite3 ): > > columns > > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > Mar 3 11:33:33 server nfacctd[10662]: ERROR ( ip_dst/sqlite3 ): > > columns > > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > Mar 3 11:33:37 server nfacctd[10663]: ERROR ( ip_dst/sqlite3 ): > > columns > > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > Mar 3 11:33:44 server nfacctd[10667]: ERROR ( ip_dst/sqlite3 ): > > columns > > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > Mar 3 11:33:47 server nfacctd[10668]: ERROR ( ip_dst/sqlite3 ): > > columns > > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > > > > > > Any suggestions? Per the instructions, we do have these configured: > > > > > > > > > > sql_refresh_time: 60 > > > > > sql_history: 1m > > > > > sql_history_roundoff: m > > > > >
Re: [pmacct-discussion] aggregate_filter for non pcap elements
Hi Timo, 'ip' is to match the IP address of the sender of the NetFlow/IPFIX packet. Paolo On Tue, Mar 07, 2017 at 01:29:11PM +0100, Timo Lindhorst wrote: > Hi Paolo, > > > I would make one exception > > which did not raise from your email: the potential need for IP addresses > > (in the IPFIX flow) to be masked with a given netmask for comparison > > against a prefix in the map - > > >From browsing the documentation, I assumed that 'ip' as pretag match might > be a template for matching IP addresses against a net prefix. > > > > If we are in-sync, i guess we can pass to private email for two things: > > 1) get from you some sample data that i can replay in lab and 2) agree > > some timelines. Or alternatively, should you feel to be in the position > > of contribute some code, that would be fantastic. > > In principle, I would like to contribute myself. However, if I face any > trouble, I will address to you privately. > > Thanks for now for your kind support! > > Best > Timo > > > > > > > Paolo > > > > On Wed, Mar 01, 2017 at 04:25:26PM +0100, Timo Lindhorst wrote: > >> Hi Paolo, > >> > >> thanks for your response. > >> > >> > All of that should fall in the pre_tag_filter domain but I can > >> > anticipate you none of the fields you are mentioning (ie. > >> > post_nat_src_host, src_host_country or nat_event) are currently > >> > supported. What would be your use-case? Feel free to say here or via > >> > unicast email. > >> > >> We are collecting IPFIX NAT event records from a gateway device > >> containing the 5 tupel, post_nat_src_host, post_nat_src_port, > >> nat_event, and some counters. In this setup, I have two use cases: > >> > >> 1) While we are receiving both create events (nat_event=4) and destroy > >> (nat_event=5), I only want to persist the destroy events in DB, as > >> those carry all the information required. Hence, my intention was > >> to somehow filter for nat_event=5. > >> > >> 2) In addition to that, depending on the NAT IP address, different > >> retention policies apply. Hence, I want to filter for post_nat_src_host > >> to be in a given a.b.c.d/e network and pass to a dedicated plugin, > >> e.g. to persist in dedicated DB table. > >> > >> If I got you right, this is supposed to be done by using a pre_tag_map > >> to tag the records and do the filtering by pre_tag_filter on the given > >> tag. However, support for the particular attributes (post_nat_src_host, > >> nat_event) to be used as match in pre_tag_map needs to be implemented. > >> I guess, this would be straight forward, as it would be similar to the > >> existing matches (dst_as, dst_mac, ...), right? > >> > >> Thanks in advance > >> Timo > >> > >> > >> > >> > >> > On Tue, Feb 28, 2017 at 01:54:14PM +0100, Timo Lindhorst wrote: > >> >> Hi, > >> >> > >> >> we're currently digging into pmacct and friends and I'm quite happy > >> >> about its feature set and extensibility. > >> >> > >> >> Now, I face a probably not so uncommon use case, which I'm not able > >> >> to find a simple solution for: > >> >> > >> >> The `aggregate_filter` key allows to filter flows before passing them > >> >> to a plugin. The filter syntax is supposed to be a pcap-filter: > >> >> aggregate_filter[inbound]: dst net 192.168.0.0/16 > >> >> > >> >> However, is there a way to filter for non-pcap attributes, like > >> >> src_host_country or nat_event? In my present case, I like to filter > >> >> the post_nat_src_host to be in a given network, but I don't see > >> >> a way to do this with aggregate_filter and pcap syntax. > >> >> > >> >> The documentation for aggregate_filter says: > >> >> > This directive can be used in conjunction with 'pre_tag_filter' > >> >> > (which, in turn, allows to filter tags). > >> >> Hence, I guess it should be possible to filter for tags. However, > >> >> trying `tag 1` as filter results in a "syntax error". > >> >> > >> >> I appreciate any help or hint how to approach this. > >> >> > >> >> Thanks in advance > >> >> Timo > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> -- > >> >> Dipl.-Ing.-Inf. Timo Lindhorst > >> >> > >> >> email: timo.lindho...@travelping.com > >> >> phone: +49-391-819099-236 > >> >> > >> >> --- enabling your networks -- > >> >> > >> >> Travelping GmbH phone: +49-391-81 90 99 0 > >> >> Roentgenstr. 13, 39108 Magdeburg fax:+49-391-81 90 99 299 > >> >> Koernerstr. 7-10, 10785 Berlin email: i...@travelping.com > >> >> GERMANY web:http://www.travelping.com > >> >> > >> >> Company Registration: Amtsgericht StendalReg No.: HRB 10578 > >> >> Geschaeftsfuehrer: Holger Winkelmann VAT ID No.: DE236673780 > > > > > - ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] aggregate_filter for non pcap elements
Hi Paolo, > I would make one exception > which did not raise from your email: the potential need for IP addresses > (in the IPFIX flow) to be masked with a given netmask for comparison > against a prefix in the map - >From browsing the documentation, I assumed that 'ip' as pretag match might be a template for matching IP addresses against a net prefix. > If we are in-sync, i guess we can pass to private email for two things: > 1) get from you some sample data that i can replay in lab and 2) agree > some timelines. Or alternatively, should you feel to be in the position > of contribute some code, that would be fantastic. In principle, I would like to contribute myself. However, if I face any trouble, I will address to you privately. Thanks for now for your kind support! Best Timo > > Paolo > > On Wed, Mar 01, 2017 at 04:25:26PM +0100, Timo Lindhorst wrote: >> Hi Paolo, >> >> thanks for your response. >> >> > All of that should fall in the pre_tag_filter domain but I can >> > anticipate you none of the fields you are mentioning (ie. >> > post_nat_src_host, src_host_country or nat_event) are currently >> > supported. What would be your use-case? Feel free to say here or via >> > unicast email. >> >> We are collecting IPFIX NAT event records from a gateway device >> containing the 5 tupel, post_nat_src_host, post_nat_src_port, >> nat_event, and some counters. In this setup, I have two use cases: >> >> 1) While we are receiving both create events (nat_event=4) and destroy >> (nat_event=5), I only want to persist the destroy events in DB, as >> those carry all the information required. Hence, my intention was >> to somehow filter for nat_event=5. >> >> 2) In addition to that, depending on the NAT IP address, different >> retention policies apply. Hence, I want to filter for post_nat_src_host >> to be in a given a.b.c.d/e network and pass to a dedicated plugin, >> e.g. to persist in dedicated DB table. >> >> If I got you right, this is supposed to be done by using a pre_tag_map >> to tag the records and do the filtering by pre_tag_filter on the given >> tag. However, support for the particular attributes (post_nat_src_host, >> nat_event) to be used as match in pre_tag_map needs to be implemented. >> I guess, this would be straight forward, as it would be similar to the >> existing matches (dst_as, dst_mac, ...), right? >> >> Thanks in advance >> Timo >> >> >> >> >> > On Tue, Feb 28, 2017 at 01:54:14PM +0100, Timo Lindhorst wrote: >> >> Hi, >> >> >> >> we're currently digging into pmacct and friends and I'm quite happy >> >> about its feature set and extensibility. >> >> >> >> Now, I face a probably not so uncommon use case, which I'm not able >> >> to find a simple solution for: >> >> >> >> The `aggregate_filter` key allows to filter flows before passing them >> >> to a plugin. The filter syntax is supposed to be a pcap-filter: >> >> aggregate_filter[inbound]: dst net 192.168.0.0/16 >> >> >> >> However, is there a way to filter for non-pcap attributes, like >> >> src_host_country or nat_event? In my present case, I like to filter >> >> the post_nat_src_host to be in a given network, but I don't see >> >> a way to do this with aggregate_filter and pcap syntax. >> >> >> >> The documentation for aggregate_filter says: >> >> > This directive can be used in conjunction with 'pre_tag_filter' >> >> > (which, in turn, allows to filter tags). >> >> Hence, I guess it should be possible to filter for tags. However, >> >> trying `tag 1` as filter results in a "syntax error". >> >> >> >> I appreciate any help or hint how to approach this. >> >> >> >> Thanks in advance >> >> Timo >> >> >> >> >> >> >> >> >> >> >> >> -- >> >> Dipl.-Ing.-Inf. Timo Lindhorst >> >> >> >> email: timo.lindho...@travelping.com >> >> phone: +49-391-819099-236 >> >> >> >> --- enabling your networks -- >> >> >> >> Travelping GmbH phone: +49-391-81 90 99 0 >> >> Roentgenstr. 13, 39108 Magdeburg fax:+49-391-81 90 99 299 >> >> Koernerstr. 7-10, 10785 Berlin email: i...@travelping.com >> >> GERMANY web:http://www.travelping.com >> >> >> >> Company Registration: Amtsgericht StendalReg No.: HRB 10578 >> >> Geschaeftsfuehrer: Holger Winkelmann VAT ID No.: DE236673780 > > > > - ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
