forum.lissyara.su

Код: Выделить всё

ng_ipacct=# select * from traffic LIMIT 3;
 id  | ifaces_id |          date_in           |    ip_from    | s_port |     ip_to     | d_port | proto | packets | bytes | date_packet  
-----+-----------+----------------------------+---------------+--------+---------------+--------+-------+---------+-------+---------------------
 457 |         1 | 2008-07-10 23:46:38.211667 | 87.230.97.237 |     80 | 192.168.1.2   |  53363 |     6 |       7 |  1254 | 2008-07-10 23:46:30
 458 |         1 | 2008-07-10 23:46:38.211667 | 192.168.1.2   |  64607 | 87.230.97.237 |     80 |     6 |       6 |  1040 | 2008-07-10 23:46:30
 459 |         1 | 2008-07-10 23:46:38.211667 | 192.168.1.2   |  58905 | 85.95.164.60  |     53 |    17 |       1 |    59 | 2008-07-10 23:46:30
(3 rows)

ng_ipacct=#

Код: Выделить всё

if ip_from in (192.168.0.0/16) and ip_to not in (192.168.0.0/16):
  traf_out += bytes
elif ip_from not in (192.168.0.0/16) and ip_to in (192.168.0.0/16):
  traf_in += bytes
else:
  traf_local +=bytes

Код: Выделить всё

CREATE OR REPLACE VIEW traffic_out AS SELECT ip_from, ip_to, date_packet::date, sum(bytes) AS bytes
 FROM traffic
 WHERE ip_from << '192.168.0.0/16' AND ip_to NOT IN (SELECT ip_to FROM traffic WHERE ip_to << '192.168.0.0/16')
 GROUP BY ip_from, ip_to, date_packet::date
 ORDER BY date_packet ASC, ip_from, ip_to;

CREATE OR REPLACE VIEW traffic_in AS SELECT ip_from, ip_to, date_packet::date, sum(bytes) AS bytes
 FROM traffic
 WHERE ip_to << '192.168.0.0/16' AND ip_from NOT IN (SELECT ip_from FROM traffic WHERE ip_from << '192.168.0.0/16')
 GROUP BY ip_from, ip_to, date_packet::date
 ORDER BY date_packet ASC, ip_from, ip_to;

CREATE OR REPLACE VIEW traffic_local AS SELECT ip_from, ip_to, date_packet::date, sum(bytes) AS bytes
 FROM traffic
 WHERE ip_to << '192.168.0.0/16' AND ip_from << '192.168.0.0/16'
 GROUP BY ip_from, ip_to, date_packet::date
 ORDER BY date_packet ASC, ip_from, ip_to;

Код: Выделить всё

#!/bin/sh
[ -z "$1" ] && exit 1
ipacctctl $1_ip_acct:$1 checkpoint
echo "CREATE TEMPORARY TABLE traffic_tmp (
 date_in timestamp NOT NULL DEFAULT now(),
 ip_from inet NOT NULL,
 s_port int NOT NULL,
 ip_to inet NOT NULL,
 d_port int NOT NULL,
 proto int NOT NULL,
 packets int NOT NULL,
 bytes int NOT NULL,
 unixtime int NOT NULL
);

COPY traffic_tmp (ip_from, s_port, ip_to, d_port, proto, packets, bytes, unixtime) FROM stdin;" >> $1.log
ipacctctl $1_ip_acct:$1 show >> $1.log
echo "\.

SELECT add_traffic('$1');" >> $1.log
psql -f $1.log ng_ipacct -U pgsql 2>/dev/null
ipacctctl $1_ip_acct:$1 clear
cat /dev/null > $1.log

Код: Выделить всё

CREATE TABLE ifaces ( -- таблица с интерфейсами
 id serial,
 title varchar(16) NOT NULL,	-- название интерфейса
CONSTRAINT ifaces_pk PRIMARY KEY (id),
CONSTRAINT ifaces_u1 UNIQUE (title)
);

CREATE TABLE traffic ( -- траффик по интерфейсам
 id serial,
 ifaces_id int NOT NULL,
 ip_from inet NOT NULL,
 s_port int NOT NULL,
 ip_to inet NOT NULL,
 d_port int NOT NULL,
 proto int NOT NULL,
 packets int NOT NULL,	-- количество прошедших пакетов
 bytes int NOT NULL,	-- количество прошедших байт
 date_packet timestamp NOT NULL,	-- время прохождеия пакетов
CONSTRAINT traffic_pk PRIMARY KEY (id),
CONSTRAINT traffic_f1 FOREIGN KEY (ifaces_id) REFERENCES ifaces (id)
);

CREATE OR REPLACE FUNCTION add_traffic(ifaces.title%TYPE) RETURNS int AS $_$
 DECLARE
  titleIN ALIAS FOR $1;
  ifacesID ifaces.id%TYPE;

 BEGIN

  SELECT id INTO ifacesID FROM ifaces WHERE title = titleIN;
  INSERT INTO traffic (ifaces_id, ip_from, s_port, ip_to, d_port, proto, date_packet, packets, bytes)
   SELECT ifacesID AS ifaces_id, ip_from, s_port, ip_to, d_port, proto, abstime(unixtime), sum(packets) AS packets, sum(bytes) AS bytes
   FROM traffic_tmp
   GROUP BY ip_from, s_port, ip_to, d_port, proto, unixtime;
  RETURN 0;

 END;
$_$ LANGUAGE plpgsql;