Re: [pmacct-discussion] MySQL plugin and dynamic table names

2017-09-07 Thread Mathias Gumz
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

2017-09-06 Thread Paolo Lucente

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

2017-08-30 Thread Mathias Gumz
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

2017-08-29 Thread Paolo Lucente

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

2017-08-28 Thread Paolo Lucente

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