Re: [pmacct-discussion] MySQL plugin and dynamic table names
Hi Paolo, >> Last question so far: Why am I not seeing data in the database when >> using "sql_history: 1" (or "10")? I have both "sql_history" and >> "sql_refresh_time" set to the same amount of seconds. > > In super short, you can't set time-bins below 1 minute, anything below > that will be rounded up to 1 minute (and you should get a log message > about that). Also, you need a time unit: no '60' but so '60s' or '1m'. > This all said, reviewing the code handling time units in secs (a more > recent introduction compared to time units in minutes and above) i have > found a bug, just fixed by: > > https://github.com/pmacct/pmacct/commit/1db48c5566bcfcd1d34a7ed7804b3105bbd35bb2 Nice :) > Do you really need time-bins of less than 1 minute? If yes, can you No, I just need to understand the boundaries and the implications. 1 minute time bins are just fine. It just means that if *nfacctd* goes down, it takes a whole minute of information (at worst, given "sql_refresh_time" is aligned with "sql_history") with it. So something like a load balancer (as outlined in one of the other threads) could come in handy. So, thanks again for all the clarifications and keep up the good work. Cheers, -- Mathias Gumz Email: mathias.g...@travelping.com Phone: +49-391-819099-228 --- enabling your networks -- Travelping GmbH Phone: +49-391-81 90 99 0 Roentgenstr. 13 Fax:+49-391-81 90 99 299 39108 Magdeburg 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] MySQL plugin and dynamic table names
Hi Mathias, Inline: On Tue, Sep 05, 2017 at 03:05:55PM +0200, Mathias Gumz wrote: > Exactly that is what I switched to and it does its job. :) Great :) > Last question so far: Why am I not seeing data in the database when > using "sql_history: 1" (or "10")? I have both "sql_history" and > "sql_refresh_time" set to the same amount of seconds. In super short, you can't set time-bins below 1 minute, anything below that will be rounded up to 1 minute (and you should get a log message about that). Also, you need a time unit: no '60' but so '60s' or '1m'. This all said, reviewing the code handling time units in secs (a more recent introduction compared to time units in minutes and above) i have found a bug, just fixed by: https://github.com/pmacct/pmacct/commit/1db48c5566bcfcd1d34a7ed7804b3105bbd35bb2 Do you really need time-bins of less than 1 minute? If yes, can you comment (here or via unicast email) your use-case for that? Consider, i guess i'm stating the obvious, time-bins are different than purging interval (sql_refresh_time) which minimum value is 1 (sec). Paolo ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] MySQL plugin and dynamic table names
> I see now. Can you try with 'nfacctd_time_new: true' It will cause > time-binning to use arrival time of the flow to the collector (that > time should be reasonably close to flow end time and stamp_updated). Exactly that is what I switched to and it does its job. :) Since I don't use it for real accounting, I per se don't have any use for "sql_history". But since I need "sql_history" to trigger the working of the dynmic tables, I initially went with 1s resolution. For whatever reason: This did not work or better: I don't see any data in any bin. I since then increased the "sql_history" config option to "60s" (and "sql_refresh_time" is also set to "60s"). Last question so far: Why am I not seeing data in the database when using "sql_history: 1" (or "10")? I have both "sql_history" and "sql_refresh_time" set to the same amount of seconds. Thanks in advance, -- Mathias Gumz Email: mathias.g...@travelping.com Phone: +49-391-819099-228 --- enabling your networks -- Travelping GmbH Phone: +49-391-81 90 99 0 Roentgenstr. 13 Fax:+49-391-81 90 99 299 39108 Magdeburg 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] MySQL plugin and dynamic table names
Hi Mathias, I see now. Can you try with 'nfacctd_time_new: true' It will cause time-binning to use arrival time of the flow to the collector (that time should be reasonably close to flow end time and stamp_updated). Let me know if any luck; if not, we can switch to unicast email and you could send me a sample of your flow data so to get a better idea of what could be possible. Paolo On Wed, Aug 30, 2017 at 11:10:50AM +0200, Mathias Gumz wrote: > Hi Paolo, > > > On Tue, Aug 29, 2017 at 05:00:34PM +0200, Mathias Gumz wrote: > >> > Currently I have set the "sql_history" and "sql_refresh_time" to 60s. I > >> > wonder, > >> > how the algorithm works. "sql_refresh_time" seems to scan the cache and, > >> > if > >> > needed, writes/updates an entry in the current bin. But what exactly is > >> > "sql_history" doing? Will there be only "one" entry of a certain flow > >> > which is > > > > Essentially sql_history makes stamp_inserted being added to the key. So, > > yes, this will ensure only "one" entry for a certain aggregate during > > the time-bin. > > > >> Also: currently I am trying to write to a new table every hour. So, I have > >> tables events0, events1, events2 etc. I have established a SSH-session > >> which > >> crosses the full hour (which started 00:55 and ends 01:05). I received the > >> NAT > >> event 4 for the created TCP connection in events0. The closing event (NAT > >> event > >> 5) for the session is also stored in events0. My expectation is events1. > >> Why is > >> that? > > > > You have tables events0, events1, events2, etc.: is that the actual name > > of the tables or just an example where 1, 2, etc. should be replaced by > > a timestamp filled in by pmacct? Also, basing on what assumption would > > you expect one event to go in events0, the other going in events1? Based > > on time? > > sql_table[natev]: events_%H > > I started the SSH-session at 0:55, the event is stored in events_0. The > SSH-session ends at 1:05. The hour part now would be "1" (events_1) but the > events_0 table is used. As you outlined above, the reason for this is that > the "stamp_inserted" field is used (at least that is my understanding). > > How can I enforce the desired behaviour to store the events in (effectively) > "timestamp_end" / "stamp_updated" based tables? Do I need something like > "sql_history_roundoff" or "nfacctd_pro_rating" (which would split the > transmitted bytes over all involved bins evenly? > > Thanks in advance, > -- > Mathias Gumz > > Email: mathias.g...@travelping.com > Phone: +49-391-819099-228 > > --- enabling your networks -- > > Travelping GmbH Phone: +49-391-81 90 99 0 > Roentgenstr. 13 Fax:+49-391-81 90 99 299 > 39108 Magdeburg 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 ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] MySQL plugin and dynamic table names
Hi Paolo, > On Tue, Aug 29, 2017 at 05:00:34PM +0200, Mathias Gumz wrote: >> > Currently I have set the "sql_history" and "sql_refresh_time" to 60s. I >> > wonder, >> > how the algorithm works. "sql_refresh_time" seems to scan the cache and, if >> > needed, writes/updates an entry in the current bin. But what exactly is >> > "sql_history" doing? Will there be only "one" entry of a certain flow >> > which is > > Essentially sql_history makes stamp_inserted being added to the key. So, > yes, this will ensure only "one" entry for a certain aggregate during > the time-bin. > >> Also: currently I am trying to write to a new table every hour. So, I have >> tables events0, events1, events2 etc. I have established a SSH-session which >> crosses the full hour (which started 00:55 and ends 01:05). I received the >> NAT >> event 4 for the created TCP connection in events0. The closing event (NAT >> event >> 5) for the session is also stored in events0. My expectation is events1. Why >> is >> that? > > You have tables events0, events1, events2, etc.: is that the actual name > of the tables or just an example where 1, 2, etc. should be replaced by > a timestamp filled in by pmacct? Also, basing on what assumption would > you expect one event to go in events0, the other going in events1? Based > on time? sql_table[natev]: events_%H I started the SSH-session at 0:55, the event is stored in events_0. The SSH-session ends at 1:05. The hour part now would be "1" (events_1) but the events_0 table is used. As you outlined above, the reason for this is that the "stamp_inserted" field is used (at least that is my understanding). How can I enforce the desired behaviour to store the events in (effectively) "timestamp_end" / "stamp_updated" based tables? Do I need something like "sql_history_roundoff" or "nfacctd_pro_rating" (which would split the transmitted bytes over all involved bins evenly? Thanks in advance, -- Mathias Gumz Email: mathias.g...@travelping.com Phone: +49-391-819099-228 --- enabling your networks -- Travelping GmbH Phone: +49-391-81 90 99 0 Roentgenstr. 13 Fax:+49-391-81 90 99 299 39108 Magdeburg 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] MySQL plugin and dynamic table names
Hi Mathias, Inline: On Tue, Aug 29, 2017 at 05:00:34PM +0200, Mathias Gumz wrote: > > Currently I have set the "sql_history" and "sql_refresh_time" to 60s. I > > wonder, > > how the algorithm works. "sql_refresh_time" seems to scan the cache and, if > > needed, writes/updates an entry in the current bin. But what exactly is > > "sql_history" doing? Will there be only "one" entry of a certain flow which > > is Essentially sql_history makes stamp_inserted being added to the key. So, yes, this will ensure only "one" entry for a certain aggregate during the time-bin. > Also: currently I am trying to write to a new table every hour. So, I have > tables events0, events1, events2 etc. I have established a SSH-session which > crosses the full hour (which started 00:55 and ends 01:05). I received the > NAT event 4 for the created TCP connection in events0. The closing event (NAT > event 5) for the session is also stored in events0. My expectation is > events1. Why is that? You have tables events0, events1, events2, etc.: is that the actual name of the tables or just an example where 1, 2, etc. should be replaced by a timestamp filled in by pmacct? Also, basing on what assumption would you expect one event to go in events0, the other going in events1? Based on time? Paolo ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] MySQL plugin and dynamic table names
> Currently I have set the "sql_history" and "sql_refresh_time" to 60s. I > wonder, > how the algorithm works. "sql_refresh_time" seems to scan the cache and, if > needed, writes/updates an entry in the current bin. But what exactly is > "sql_history" doing? Will there be only "one" entry of a certain flow which is Also: currently I am trying to write to a new table every hour. So, I have tables events0, events1, events2 etc. I have established a SSH-session which crosses the full hour (which started 00:55 and ends 01:05). I received the NAT event 4 for the created TCP connection in events0. The closing event (NAT event 5) for the session is also stored in events0. My expectation is events1. Why is that? Cheers, -- Mathias Gumz Email: mathias.g...@travelping.com Phone: +49-391-819099-228 --- enabling your networks -- Travelping GmbH Phone: +49-391-81 90 99 0 Roentgenstr. 13 Fax:+49-391-81 90 99 299 39108 Magdeburg 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] MySQL plugin and dynamic table names
Hi Paolo, > Can you please post your config? Gut feeling says you may be missing the > sql_history directive (essentially indicate what is the time-binning > period). Yes, adding "sql_history" (and "sql_refresh_time") + having "stamp_inserted" and "stamp_updated" columns in the schema helps. Since I (from my perspective) did not want to do "accounting" but just storing the 2 events into the right table (based upon the "timestamp_start" or "timestamp_end"). My fault to ignore the documentation. Currently I have set the "sql_history" and "sql_refresh_time" to 60s. I wonder, how the algorithm works. "sql_refresh_time" seems to scan the cache and, if needed, writes/updates an entry in the current bin. But what exactly is "sql_history" doing? Will there be only "one" entry of a certain flow which is updated when new data is received and then stored frequently into (possible) multiple bins? Is nfacctd postponing for "sql_history" time the writing of the of first entries? Thanks in advance, -- Mathias Gumz Email: mathias.g...@travelping.com Phone: +49-391-819099-228 --- enabling your networks -- Travelping GmbH Phone: +49-391-81 90 99 0 Roentgenstr. 13 Fax:+49-391-81 90 99 299 39108 Magdeburg 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] MySQL plugin and dynamic table names
Hi Mathias, Can you please post your config? Gut feeling says you may be missing the sql_history directive (essentially indicate what is the time-binning period). Paolo On Mon, Aug 28, 2017 at 03:36:02PM +0200, Mathias Gumz wrote: > Hi, > > we are using nfacctd to collect NAT events (event types 4 and 5, > https://www.iana.org/assignments/ipfix/ipfix.xml#ipfix-nat-event-type) into a > MySQL instance. We also want to rotate the table on a daily basis: > > sql_table[natev]: natev_%Y_%m_%d > sql_table_schema[natev]: /path/to/schema.sql > > schema.sql looks like this: > > create table natev_%Y_%m_%d ( > ... > ); > > Currently, the generated table name is somehow named like "natev_1970_01_01", > allthough the timestamp_start and timestart_end fields of the collected NAT > events are correctly set. > > I toyed around with the table-creation code in src/sql_common.c to enforce a > table-name with the right name, but the events are not stored in that created > table: "natev_1970_01_01" is used all the time. > > Can you point me into the right direction on how to configure nfacctd to > store the NAT events into the right tables? > > Thank you in advance, > -- > Mathias Gumz > > Email: mathias.g...@travelping.com > Phone: +49-391-819099-228 > > --- enabling your networks -- > > Travelping GmbH Phone: +49-391-81 90 99 0 > Roentgenstr. 13 Fax:+49-391-81 90 99 299 > 39108 Magdeburg 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 ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
