Re: [pmacct-discussion] create my own mysql table
Hi, On Mon, Nov 16, 2009 at 04:45:57PM -0600, fedora fedora wrote: DEBUG ( default/mysql ): INSERT INTO `test_1` (stamp_updated, stamp_inserted, ip_src, ip_dst, as_src, as_dst, src_port, dst_port, tcp_flags, ip_proto, packets, bytes, flows) VALUES (FROM_UNIXTIME(1258410661), FROM_UNIXTIME(1258410600), 'x.x.x.34', 'x.x.x.2', xx8, xx9, 443, 2608, 24, 'tcp', 1, 1353, 140733193388033) Thanks, for the output. So, only the 'flows' primitive is involved. Hopefully last bit of information i need is: which NetFlow version are your routers exporting to pmacct? If v8, which profile? If v9, you doing anything fancy with it (ie. aggregated NetFlow)? Roughly a week ago i committed to the CVS a minor patch to initialize some variables used at some stage to convert values as counters; can you please see if the version currently in the CVS behaves any better? So pmacct keeps tracking the traffic count and and the end of the given minutes(hours..etc) it calculates the summary and then writes it to the backend database, am I right? Yes pmacct SQL plugins feature a cache to accumulate counters. Then the scanner kicks in at regular intervals (by default 60 secs) and writes to the database. If sql_history matches sql_refresh_time (or is a multiple) then each aggregate is written with a SQL INSERT query; otherwise UPDATE queries are involved. If I am correct, how does pmacct treat netflow data? since all the data it gets already get aggregated by netflow protocol. Will pmacct do something extra? I guess for sflow, it will act differently and do the calculations. Basic thing to consider is pmacct is not a packet/sample/flow logger. This is partly highlighted by Q5 in FAQS. It performs data reduction, ie. temporal aggregation, spatial aggregation, filtering, sampling (or sub-sampling). Timestamps part of the export protocol, ie. NetFlow or sFlow, are used to assign data to a time-bin, when using a SQL plugin (as the in-memory table plugin doesn't have such a concept; you simply grabclean data at regular intervals). Cheers, Paolo ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] create my own mysql table
I still see all flow records having the same number 4294967295 in my mysql table, and debug does not seem to tell me why this happens. And I have another question about the sql_history, what exactly is this? I have read the configuration keys several time but I am still not sure. If I set it to 5m, does it mean pmacct will hold and count the data internally until the end of the 5 minutes then send the final statistic to mysql? One more question, how can i get pmacct to show the flow number in the database? I got all 0 ,and when i change to aggregate to aggregate:proto,src_host, dst_host, src_port, dst_port, src_as, dst_as, tcpflags,flows all the numbers are one same pretty big number MMM, sounds strange. Also, don't manage to reproduce either of the two scenarios by playing your configuration on a test box. The same pretty big number smells like dirty data? Enable debug and you should see on the screen which SQL queries are generated by pmacct and sent to the MySQL database. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] create my own mysql table
Hi, On Mon, Nov 16, 2009 at 11:58:14AM -0600, fedora fedora wrote: I still see all flow records having the same number 4294967295 in my mysql table, and debug does not seem to tell me why this happens. How do you mean? You see that number appearing in the debug? As writing to the database is done through a library (MySQL client library), I'm trying to establish what is generating the issue. Can you also say what version of MySQL you are using, which OS and which architecture you are running pmacct on? And I have another question about the sql_history, what exactly is this? I have read the configuration keys several time but I am still not sure. If I set it to 5m, does it mean pmacct will hold and count the data internally until the end of the 5 minutes then send the final statistic to mysql? It means you have 5 minutes traffic counters (temporal aggregation). The SQL cache scanner sets how often you write to the database. You can have a sql_history set to 5 minutes but have the SQL scanner to kick in every minute (sql_refresh_time: 60). There are more advanced considerations to do at this propo - which are useless to start with and that you can read of in CONFIG-KEYS, sql_dont_try_update feature. Overall, best is to start blank with relevant sections of the sql/README.mysql and EXAMPLES documents in the distribution tarball - and then complicate things bit by bit. Btw, sql_history_roundoff is also a very useful directive to be coupled almost always with sql_history, for example: sql_history: 5m sql_history_roundoff: h It nicely aligns the time bins to the hour boundary. Again, perhaps give it a try to see the effect. Cheers, Paolo ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] create my own mysql table
Thanks a ton for the reply! FYI, here is the debug output, DEBUG ( default/mysql ): INSERT INTO `test_1` (stamp_updated, stamp_inserted, ip_src, ip_dst, as_src, as_dst, src_port, dst_port, tcp_flags, ip_proto, packets, bytes, flows) VALUES (FROM_UNIXTIME(1258410661), FROM_UNIXTIME(1258410600), 'x.x.x.34', 'x.x.x.2', xx8, xx9, 443, 2608, 24, 'tcp', 1, 1353, 140733193388033) and here is what shows up in the mysql table for the corresponding record. | xx8 | xx9 | x.x.x.34 | x.x.x.2 | 443 | 2608 |24 | tcp | 0 | 1 | 1353 | 4294967295 | 2009-11-16 16:30:00 | 2009-11-16 16:31:01 | The flow number is 4294967295, and actually all the other records have same number with this one. I am using ubuntu 8.04 (64 bits), with mysql Server version: 5.0.67-0ubuntu6 (Ubuntu) Let me know If you need more info, thanks It means you have 5 minutes traffic counters (temporal aggregation). So pmacct keeps tracking the traffic count and and the end of the given minutes(hours..etc) it calculates the summary and then writes it to the backend database, am I right? If I am correct, how does pmacct treat netflow data? since all the data it gets already get aggregated by netflow protocol. Will pmacct do something extra? I guess for sflow, it will act differently and do the calculations. Here is my current config, debug: true sql_optimize_clauses: true sql_history: 5m sql_history_roundoff: m sql_table: test_%w sql_table_schema: /home/test.schema sql_table_version: 6 aggregate: proto, src_host, dst_host, src_port, dst_port, src_as, dst_as, tcpflags, flows interface: eth1 nfacctd_port: 1 plugins: mysql ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] create my own mysql table
Thanks for the reply, i disable the daemon option and here are the error messages, i cannot put src_ip and src_as together? INFO ( default/mysql ): 131070 bytes are available to address shared memory segment; buffer size is 128 bytes. INFO ( default/mysql ): Trying to allocate a shared memory segment of 2097024 bytes. INFO ( default/core ): waiting for NetFlow data on 0.0.0.0:1 ERROR ( default/mysql ): SQL tables v6 are unable to mix IP addresses and AS numbers (ie. src_ip, src_as). INFO: connection lost to 'default-mysql'; closing connection. On Fri, Nov 13, 2009 at 4:03 PM, Paolo Lucente pa...@pmacct.net wrote: Hi, It seems the daemon would like to complain about something but you send it to background (daemonize: true). Perhaps comment the daemonize line out and set debug to true (debug: true) and see what's the story. Put it like that, it could be a number of things. Cheers, Paolo On Fri, Nov 13, 2009 at 02:56:15PM -0600, fedora fedora wrote: Hello, I am trying to get pmacct to pass the received the netflow data to my customized mysql table, the table name is test and has the following structure, mysql desc test; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | as_src | int(4) unsigned | NO | PRI | NULL| | | as_dst | int(4) unsigned | NO | PRI | NULL| | | ip_src | char(15)| NO | PRI | NULL| | | ip_dst | char(15)| NO | PRI | NULL| | | src_port | int(2) unsigned | NO | PRI | NULL| | | dst_port | int(2) unsigned | NO | PRI | NULL| | | tcp_flags | int(4) unsigned | NO | | NULL| | | ip_proto | char(6) | NO | PRI | NULL| | | tos| int(4) unsigned | NO | PRI | NULL| | | packets| int(10) unsigned| NO | | NULL| | | bytes | bigint(20) unsigned | NO | | NULL| | | flows | int(10) unsigned| NO | | NULL| | | stamp_inserted | datetime| NO | PRI | NULL| | | stamp_updated | datetime| YES | | NULL| | ++-+--+-+-+---+ The pmacct was configued with mysql enabled, and my pmacct config file is like this: ++ daemonize: true interface: eth1 nfacctd_port: 1 #netflow are being sent on port 1 aggregate: src_host,dst_host,src_port,dst_port,tos,proto,src_as,dst_as,flows,tcpflags plugins: mysql sql_optimize_clauses: true sql_table: test ++ Then I ran nfacctd -f ./pm.conf but nothing happens, and a ps -ef | egrep nfacct shows nfacctd is not running. I noticed if i change aggregate: src_host,dst_host,src_port,dst_port,tos,proto,src_as,dst_as,flows,tcpflags to aggregate: src_host, tcpflags, i will get something in the test table, but apparently this is not what I wanted. Can someone tell me what could be wrong? ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] create my own mysql table
Hi, On Fri, Nov 13, 2009 at 04:21:26PM -0600, fedora fedora wrote: Thanks for the reply, i disable the daemon option and here are the error messages, i cannot put src_ip and src_as together? INFO ( default/mysql ): 131070 bytes are available to address shared memory segment; buffer size is 128 bytes. INFO ( default/mysql ): Trying to allocate a shared memory segment of 2097024 bytes. INFO ( default/core ): waiting for NetFlow data on 0.0.0.0:1 ERROR ( default/mysql ): SQL tables v6 are unable to mix IP addresses and AS numbers (ie. src_ip, src_as). INFO: connection lost to 'default-mysql'; closing connection. Simply add to your config a 'sql_table_version: 6' line. Then, the 'sql_optimize_clauses' directive ensures you can customize your table. Q16 in the FAQS document explains the concept of SQL table versioning in pmacct. Cheers, Paolo ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] create my own mysql table
sorry, one more question, when preparing the aggregate, does the order of the values matter? right now my aggregate is like the following aggregate: proto,src_host, dst_host, src_port, dst_port, src_as, dst_as, tcpflags,flows while the order is different in the db table, for example, protocol is not the first column in my table, it appears that pmacct knows if should put the proto under proto column, although i defined it as first value in the aggregate and defined it in the middle of the table. On Fri, Nov 13, 2009 at 4:58 PM, fedora fedora fedoraf...@gmail.com wrote: Paolo, Thanks a lot, really appreciate your help here, it seems to work now. I actually read all the documents I could find and did not see this get mentioned anywhere. I just double checked the Q16 part i printed out and did not see anything talking about v6. One more question, how can i get pmacct to show the flow number in the database? I got all 0 ,and when i change to aggregate to aggregate:proto,src_host, dst_host, src_port, dst_port, src_as, dst_as, tcpflags,flows all the numbers are one same pretty big number Also i will have to read what exactly does stamp_inserted , and stamp_update mean.. On Fri, Nov 13, 2009 at 4:44 PM, Paolo Lucente pa...@pmacct.net wrote: Hi, On Fri, Nov 13, 2009 at 04:21:26PM -0600, fedora fedora wrote: Thanks for the reply, i disable the daemon option and here are the error messages, i cannot put src_ip and src_as together? INFO ( default/mysql ): 131070 bytes are available to address shared memory segment; buffer size is 128 bytes. INFO ( default/mysql ): Trying to allocate a shared memory segment of 2097024 bytes. INFO ( default/core ): waiting for NetFlow data on 0.0.0.0:1 ERROR ( default/mysql ): SQL tables v6 are unable to mix IP addresses and AS numbers (ie. src_ip, src_as). INFO: connection lost to 'default-mysql'; closing connection. Simply add to your config a 'sql_table_version: 6' line. Then, the 'sql_optimize_clauses' directive ensures you can customize your table. Q16 in the FAQS document explains the concept of SQL table versioning in pmacct. Cheers, Paolo ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] create my own mysql table
Hi, On Fri, Nov 13, 2009 at 05:10:35PM -0600, fedora fedora wrote: sorry, one more question, when preparing the aggregate, does the order of the values matter? right now my aggregate is like the following No, doesn't matter. One more question, how can i get pmacct to show the flow number in the database? I got all 0 ,and when i change to aggregate to aggregate:proto,src_host, dst_host, src_port, dst_port, src_as, dst_as, tcpflags,flows all the numbers are one same pretty big number MMM, sounds strange. Also, don't manage to reproduce either of the two scenarios by playing your configuration on a test box. The same pretty big number smells like dirty data? Enable debug and you should see on the screen which SQL queries are generated by pmacct and sent to the MySQL database. Also i will have to read what exactly does stamp_inserted , and stamp_update mean.. pmacct has a concept of time bins - which you are currently not using in your config (but you can easily add the line sql_history: 5m and see). sql_inserted is the base time of the time bin; sql_history directive in the configuration sets the length of such time bins; stamp_updated says about when a specific SQL record was touched for the last time by pmacct. Useful for troubleshooting purposes and general reference - not to infer time bins' length. Cheers, Paolo ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
