Peter Nixon wrote:
> For my purposes the number of bytes for "in" and "out" could be summed (which
> would fit with this system just fine" but it could also be usefull to have
> "in_bytes" and "out_bytes" in separate columns...
>
> Does anyone have any comments on this? Is there an easy way to do this
> currently?
>
Here's a late reply on your message. I'm not exactly what your goal is,
but I'm doing something similar by storing counters for each direction
in the same PostgreSQL table. Here's the config and SQL queries. No
extra columns are required, and yet it's easy to differentiate "in" vs
"out" traffic.
Config is something like this:
aggregate[in]:dst_host
aggregate[out]:src_host
aggregate_filter[in]: dst net 10.11.0.0/16 and not src net 10.11.0.0/16
aggregate_filter[out]: src net 10.11.0.0/16 and not dst net 10.11.0.0/16
plugins:pgsql[in], pgsql[out]
sql_data:frontend
sql_table:acct_monthly
sql_history:1M
sql_history_roundoff:h
Query to show summary by period (in this case, a month which is passed
in via DBI parameters):
SELECT
coalesce(ip_dst, ip_src) as "IP",
bytes2string(sum(bytes)) as "Total MB" ,
bytes2string(sum(bytes(ip_dst, bytes))) as "MB In",
bytes2string(sum(bytes(ip_src, bytes))) as "MB Out"
from acct_monthly
WHERE
date_trunc('month', stamp_inserted)::timestamp = to_date(?,'Month
YYYY')::timestamp
group by
coalesce(ip_dst, ip_src)
order by
coalesce(ip_dst, ip_src) asc;
-- custom SQL functions
create or replace function bytes2string(bigint) returns varchar as '
select to_char(round($1/1024/1024, 2), ''999,999,999,999.99\') as
result;
' LANGUAGE SQL;
create or replace function bytes(inet, bigint) returns bigint as '
DECLARE
in_1 alias for $1;
in_2 alias for $2;
BEGIN
if in_1 is not null then
return in_2;
else
return 0;
end if;
END;
' LANGUAGE plpgsql;
-- end custom SQL functions
Wim
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
