Re: [pmacct-discussion] NetFlow NAT
Hi Paolo, Nice! I'll see if I can make some tests here. Thank you. Cheers. 2010/10/31 Paolo Lucente pa...@pmacct.net Hi Sergio, all, To briefly say this is now implemented and appears to work in lab: http://www.mail-archive.com/pmacct-comm...@pmacct.net/msg00359.html Should anybody have a chance to give it a try, i'm looking forward to comments. Cheers, Paolo On Thu, Sep 09, 2010 at 10:10:37PM +, Paolo Lucente wrote: Hi Sergio, Good to read from you. The XLATE fields are currently not supported; only NF_F_FLOW_BYTES (ASA-specific bytes counter) is. Willing to support the XLATE fields. I was thinking as a per-plugin true/false switch, so as an alternative to the original L3/L4 fields. Let me know if this is in line with what you had in mind. Please send over privately a trace of some ASA NetFlow packets with the XLATE fields populated (and related templates). Cheers, Paolo On Thu, Sep 09, 2010 at 11:06:53AM -0300, Sergio Charpinel Jr. wrote: Hi, Paolo, how are you? I would like to know if pmacct has support to collect PAT data on NetFlow. I need the XLATE fields, to know the translation ports and IP addresses. http://www.cisco.com/en/US/docs/security/asa/asa81/netflow/netflow.html#wp1030245 Thanks in advance. -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
[pmacct-discussion] NetFlow NAT
Hi, Paolo, how are you? I would like to know if pmacct has support to collect PAT data on NetFlow. I need the XLATE fields, to know the translation ports and IP addresses. http://www.cisco.com/en/US/docs/security/asa/asa81/netflow/netflow.html#wp1030245 Thanks in advance. -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] PostgreSQL large database
Hi, Thanks for your supporting. Just to finish this thread, I have made some changes, and the problem stopped. I'll report them here: - I'm using postgresql.conf default from CentOS, and increased checkpoint_segments. - I'm using sql_cache_entries: 263167 - rebuild my index and pk ( still analyzing this, because I'm still a little restricted to some queries to have good performance) - I'm running analyze in every insert, within the create table function: CREATE OR REPLACE FUNCTION cria_tab_pmacct(text) RETURNS void AS $$ DECLARE myrec RECORD; BEGIN SELECT 1 INTO myrec FROM pg_catalog.pg_class WHERE relkind = 'r' AND relname = $1 AND pg_catalog.pg_table_is_visible(oid) LIMIT 1; IF NOT FOUND THEN EXECUTE 'CREATE TABLE ' || quote_ident($1) || ' ( . CONSTRAINT ' || $1 ||'_pk PRIMARY KEY (stamp_inserted, ip_src, ip_dst, port_src, port_dst, ip_proto) )'; EXECUTE 'CREATE INDEX ibytes_' || $1 || ' ON ' || quote_ident($1) || '(bytes)'; ELSE EXECUTE 'ANALYZE ' || $1; END IF; END; $$ LANGUAGE plpgsql; Cheers. 2010/5/20 Karl O. Pinc k...@meme.com On 05/14/2010 12:15:27 PM, Paolo Lucente wrote: Hi Sergio, On Fri, May 14, 2010 at 10:58:00AM -0300, Sergio Charpinel Jr. wrote: I couldnt get any useful information from this command. I get no erros in postgresql, nfacctd and pmacctd log files. I would expect you to see a Maximum number of SQL writer processes reached message in the logs. I will do some checks and let you know. Data is still being written in DB, but I'm suspecting that some are being lost. Because my database grew just 5G/day after that. It occurs to me that some of this may have to do with postgresql table vacuuming and the autovacuum process. I've had problems in the past when initially loading data into large tables before the first vaccuming/analyze was done. It would take some time before things stabilized. My technique was to pause data loading and do a vacuum analyze every now and then. This is probably not feasible. I can imagine there could be situations where data is loaded fast enough to trigger autovacuums that don't have enough time to finish so you get multiple processes running. If you are also deleting data this would explain the database size/growth mis-match, because the deleted data has not yet been recovered. It's a thought. Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
[pmacct-discussion] PostgreSQL large database
Hi, After less than one day running pmacct, my database grew from 1.5G to 15G. At this point I get a lot of postmaster and nfacctd process, and none of them seems to be using CPU and not writting in DB. 6774 root 15 0 160m 103m 916 S 0.0 0.9 0:04.04 nfacctd 6775 postgres 15 0 2210m 1.3g 1.3g S 0.0 11.8 0:31.50 postmaster 6784 root 15 0 160m 103m 916 S 0.0 0.9 0:04.19 nfacctd 6785 postgres 16 0 2210m 1.1g 1.1g S 0.0 9.9 0:23.77 postmaster 6794 root 15 0 160m 103m 916 S 0.0 0.9 0:02.81 nfacctd 6795 postgres 15 0 2210m 1.1g 1.1g S 0.0 9.4 0:20.57 postmaster 6804 root 15 0 160m 103m 916 S 0.0 0.9 0:02.04 nfacctd 6805 postgres 16 0 2210m 870m 867m S 0.0 7.4 0:14.03 postmaster 6819 root 15 0 160m 103m 916 S 0.0 0.9 0:02.64 nfacctd 6820 postgres 15 0 2210m 816m 813m S 0.0 7.0 0:13.14 postmaster 6829 root 15 0 160m 103m 916 S 0.0 0.9 0:01.17 nfacctd 6830 postgres 15 0 2210m 625m 622m S 0.0 5.3 0:09.63 postmaster 6861 root 15 0 160m 103m 916 S 0.0 0.9 0:01.28 nfacctd 6862 postgres 15 0 2210m 558m 556m S 0.0 4.8 0:08.16 postmaster 6889 root 15 0 160m 103m 916 S 0.0 0.9 0:00.33 nfacctd 6890 postgres 16 0 2210m 269m 267m D 0.0 2.3 0:03.16 postmaster I don't know who is causing this (nfacctd or postgresql) and which options I should tune. Here is my relevant options: nfacctd: sql_refresh_time: 300 sql_history: 5m sql_history_roundoff: m sql_dont_try_update: true sql_use_copy: true !sql_cache_entries: 103 sql_cache_entries: 263167 nfacctd_time_new: true plugin_buffer_size: 102400 plugin_pipe_size: 1024 Postgresql: shared_buffers = 262144 work_mem = 20971 checkpoint_segments = 50 Thanks for any help! -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Expiring Orphan fragment
Paolo, Yes, it is working, thanks. And I analyzed some flows related to expiring orphan, and most of them seems to be related to torrents, but I'm not sure. After some time (2hours+) running pmacctd and nfacctd, I start receiving a lot of WARN: expecting flow '817086981' but received '817086983' collector=127.0.0.1:2100 agent=127.0.0.1:0 INFO: unable to read next Flowset; incomplete NetFlow v9 packet: nfacctd=127.0.0.1:2100 agent=127.0.0.1:49586 in nfacctd log. I don't know with these 2 events are related. Do you know if there is something wrong? 2010/5/3 Paolo Lucente pa...@pmacct.net: Hi Sergio, Good point. I've just marked PGRES_TUPLES_OK as valid return code within the PG_create_dyn_table() function - and committed the change to the CVS. Can you please confirm it works OK for you? Cheers, Paolo On Mon, May 03, 2010 at 10:10:18AM -0300, Sergio Charpinel Jr. wrote: Paolo, I think the problem is in the SELECT. SELECT returns PGRES_TUPLES_OK, which means, a PGRES_COMMAND_OK plus result data. [ ... ] -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Expiring Orphan fragment
Paolo,
I think the problem is in the SELECT.
SELECT returns PGRES_TUPLES_OK, which means, a PGRES_COMMAND_OK plus
result data.
I dont know another way to circumvent this without recompiling pmacct.
The change here is in line
746 if (PQresultStatus(PGret) != PGRES_COMMAND_OK) {
I just want to fix it because it is filling my log too fast.
I saw people using dynamic tables. I'm curious how they are using it
with log enabled. Maybe they're creating a new table in every
insert... or they are not logging.
Cheers.
2010/5/1 Paolo Lucente pa...@pmacct.net:
Hi Sergio,
Thanks indeed for sharing the function.
It logs because the PostgreSQL PQexec() function is returning pmacct
something else than PGRES_COMMAND_OK. One suggestion might be to play
with the exit/return code of the PL/PGSQL function. The error string
ultimately is empty (and that is passed onto pmacct by the PostgreSQL
API) meaning it should be working fine.
Cheers,
Paolo
On Fri, Apr 30, 2010 at 02:40:50PM -0300, Sergio Charpinel Jr. wrote:
I did the function. But I'm calling it with select
function_name(table_name) , and I getting errors in nfacctd.log:
[ ... ]
Maybe because of the select. Just wanna know why it is still logging?
--
Sergio Roberto Charpinel Jr.
___
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Expiring Orphan fragment
I did the function. But I'm calling it with select
function_name(table_name) , and I getting errors in nfacctd.log:
ERROR ( default/pgsql ):
ERROR ( default/pgsql ):
Here is the function, if anyone wanna do something similar:
CREATE OR REPLACE FUNCTION cria_tab_pmacct(text)
RETURNS void AS $$
DECLARE
myrec RECORD;
BEGIN
SELECT relname INTO myrec FROM pg_class WHERE relname=$1;
IF NOT FOUND
THEN
EXECUTE 'PERFORM CREATE TABLE ' || quote_ident($1) || ' (
)';
EXECUTE 'CREATE INDEX ...';
END IF;
END;
$$ LANGUAGE plpgsql;
In table_schema file, I'm calling
select cria_tab_pmacct('acct_%Y_%m);
Maybe because of the select. Just wanna know why it is still logging?
2010/4/30 Paolo Lucente pa...@pmacct.net:
Hi Sergio,
On Thu, Apr 29, 2010 at 11:23:35AM -0300, Sergio Charpinel Jr. wrote:
Does anyone know what this mean? I getting a lot of them in pmacctd.log
Expiring orphan fragment: ip_src=210.197.202.84 ip_dst=200.137.66.1 proto=17
id=8885
It means some IP fragments have been staying too long into the pmacctd
fragment handler waiting for the missing pieces to come. If not in huge
amounts, this can be sympthom of white noise on the public internet
(scans, attacks, etc.).
Regarding the topic, when using dynamic tables with postgreSQL, How can I
verify if a table already exists before trying to create them, to avoid a
lot of 'Table already exists' messages in nfacctd.log with dynamic table?
I'm not sure there is something already cooked to circumvent that. It
should be possible though with a few lines of PL/PGSQL, ie.:
IF EXISTS (SELECT relname FROM pg_class WHERE relname='table')
THEN
...
ELSE
...
ENDIF
The simplest way should be to get the table name as parameter and embed
the actual SQL table schema within the function declaration.
Cheers,
Paolo
___
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
--
Sergio Roberto Charpinel Jr.
___
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
[pmacct-discussion] Expiring Orphan fragment
Hi, Does anyone know what this mean? I getting a lot of them in pmacctd.log Expiring orphan fragment: ip_src=210.197.202.84 ip_dst=200.137.66.1 proto=17 id=8885 Regarding the topic, when using dynamic tables with postgreSQL, How can I verify if a table already exists before trying to create them, to avoid a lot of 'Table already exists' messages in nfacctd.log with dynamic table? Thanks. -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Low performance query
Thanks Paolo, Creating index decreased query time from 1min to 5 sec hehe. The searchs are often by ip_src and ip_dst, between 2 dates (stamp_inserted). I think that indexes just for ip_src and ip_dst are fine. Cheers. 2010/4/28 Paolo Lucente pa...@pmacct.net Hi Sergio, The sql_history drives temporal aggregation by splitting traffic data into time-bins. You configured 5 minutes time-bins. See the behaviour of the 'stamp_inserted' field, ie. do SELECT DISTINCT(stamp_inserted) FROM table ORDER BY stamp_inserted, to get this more clear. The roundoff thing does some nice time alignment. So, increasing the size of the time-bins will be certainly beneficial but that's not the entire win: important thing is understanding what you are trying to achieve by putting data into PostgreSQL and choose a suitable spatial aggregation method (the aggregate directive) for your task. Suitable here means: build a data-set fitting your project requirements with the (nearly) least possible amount of records. Then indexing. It gets useful speeding up SQL queries with conditions, ie. WHERE ip_src = 'x.x.x.x'. If that's the kind of query you run the most, try inserting in the SQL table schema a INDEX (ip_src), after the PRIMARY KEY line. Perhaps you might want to do the same with the ip_dst field. Cheers, Paolo On Tue, Apr 27, 2010 at 10:37:28PM -0300, Sergio Charpinel Jr. wrote: I'm getting low perfomance while selecting from psql and from FloX. For example, selecting * WHERE ip_src = 'x.x.x.x' How can I do a more compact aggregation? You mean by getting samples ? Actually, I did not understand very well sql_history and roundoff concepts, but increasing history would do the trick? Thanks for answering. Cheers. 2010/4/27 Paolo Lucente pa...@pmacct.net Hi Sergio, I don't know FloX very well - hence would be good information to know which specific SQL queries are performing bad. Maybe there is room to improve indexing. Is it also your goal to store every micro-flow into the SQL database? Any chance a more compact aggregation method would fit the bill? Cheers, Paolo On Tue, Apr 27, 2010 at 11:36:50AM -0300, Sergio Charpinel Jr. wrote: Hi, I'm using pmacct 0.12.1 with PostgreSQL 8.4 and FloX. i'm running pmacctd and nfacctd daemons (probe and collector). After 1 day running, I'm getting 1+min to make a query. Is there any config in pmacct and PostgreSQL that I can do improve this? Here is my config in pmacct: pmacctd.conf: daemonize: true aggregate: src_host, src_port, dst_host, dst_port, proto, tcpflags plugins: nfprobe nfprobe_receiver: x.x.x.x nfprobe_version: 9 interface: eth1 nfacctd.conf: daemonize: true pidfile: /var/run/nfacctd.pid aggregate: src_host,dst_host, src_port, dst_port, proto, tcpflags plugins: pgsql sql_table_version: 7 sql_db: x sql_host: x.x.x.x sql_passwd: xxx sql_user: x sql_refresh_time: 300 sql_history: 5m sql_history_roundoff: m sql_dont_try_update: true nfacctd_port: nfacctd_ip: In PostgreSQL, I did not change the default config in CentOS 5.4. Thanks in advance! -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists -- Sergio Roberto Charpinel Jr. -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
[pmacct-discussion] Low performance query
Hi, I'm using pmacct 0.12.1 with PostgreSQL 8.4 and FloX. i'm running pmacctd and nfacctd daemons (probe and collector). After 1 day running, I'm getting 1+min to make a query. Is there any config in pmacct and PostgreSQL that I can do improve this? Here is my config in pmacct: pmacctd.conf: daemonize: true aggregate: src_host, src_port, dst_host, dst_port, proto, tcpflags plugins: nfprobe nfprobe_receiver: x.x.x.x nfprobe_version: 9 interface: eth1 nfacctd.conf: daemonize: true pidfile: /var/run/nfacctd.pid aggregate: src_host,dst_host, src_port, dst_port, proto, tcpflags plugins: pgsql sql_table_version: 7 sql_db: x sql_host: x.x.x.x sql_passwd: xxx sql_user: x sql_refresh_time: 300 sql_history: 5m sql_history_roundoff: m sql_dont_try_update: true nfacctd_port: nfacctd_ip: In PostgreSQL, I did not change the default config in CentOS 5.4. Thanks in advance! -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Low performance query
Hi Paolo, I'm getting low perfomance while selecting from psql and from FloX. For example, selecting * WHERE ip_src = 'x.x.x.x' How can I do a more compact aggregation? You mean by getting samples ? Actually, I did not understand very well sql_history and roundoff concepts, but increasing history would do the trick? Thanks for answering. Cheers. 2010/4/27 Paolo Lucente pa...@pmacct.net Hi Sergio, I don't know FloX very well - hence would be good information to know which specific SQL queries are performing bad. Maybe there is room to improve indexing. Is it also your goal to store every micro-flow into the SQL database? Any chance a more compact aggregation method would fit the bill? Cheers, Paolo On Tue, Apr 27, 2010 at 11:36:50AM -0300, Sergio Charpinel Jr. wrote: Hi, I'm using pmacct 0.12.1 with PostgreSQL 8.4 and FloX. i'm running pmacctd and nfacctd daemons (probe and collector). After 1 day running, I'm getting 1+min to make a query. Is there any config in pmacct and PostgreSQL that I can do improve this? Here is my config in pmacct: pmacctd.conf: daemonize: true aggregate: src_host, src_port, dst_host, dst_port, proto, tcpflags plugins: nfprobe nfprobe_receiver: x.x.x.x nfprobe_version: 9 interface: eth1 nfacctd.conf: daemonize: true pidfile: /var/run/nfacctd.pid aggregate: src_host,dst_host, src_port, dst_port, proto, tcpflags plugins: pgsql sql_table_version: 7 sql_db: x sql_host: x.x.x.x sql_passwd: xxx sql_user: x sql_refresh_time: 300 sql_history: 5m sql_history_roundoff: m sql_dont_try_update: true nfacctd_port: nfacctd_ip: In PostgreSQL, I did not change the default config in CentOS 5.4. Thanks in advance! -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
[pmacct-discussion] Analyzer for pmacct
Hi, I'm trying to find a good analyzer to use with pmacct. I was planning to use ntop ( I don't know yet if it is possible to use with pmacct ). But I saw some analyzers made for pmacct in pmacct site, some of them seems to be out of production. Is there any that You could recommend? Thanks in advance. -- Sergio Roberto Charpinel Jr. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
