BGPmon-Analytics-db-1
view release on metacpan or search on metacpan
bin/bgpmon_analytics_db_0_pphTables.psql view on Meta::CPAN
collector VARCHAR(30) NOT NULL,
UNIQUE(addr,collector),
PRIMARY KEY (dbid)
);
create index peers_n1 on pph.peers (addr,collector);
CREATE TABLE pph.ppms (
dbid SERIAL NOT NULL,
peer_dbid INTEGER NOT NULL,
prefix_dbid INTEGER NOT NULL,
last_timerange_dbid INTEGER NULL,
prefix_safi INTEGER,
PRIMARY KEY (dbid),
UNIQUE(prefix_dbid,peer_dbid),
FOREIGN KEY(peer_dbid) REFERENCES pph.peers (dbid),
FOREIGN KEY(prefix_dbid) REFERENCES pph.prefixes (dbid)
);
CREATE TABLE pph.timeranges (
dbid SERIAL NOT NULL,
ppm_dbid INTEGER NOT NULL,
start_time TIMESTAMP WITHOUT TIME ZONE,
end_time TIMESTAMP WITHOUT TIME ZONE,
last_hop VARCHAR,
origin_as VARCHAR,
as_path VARCHAR ARRAY,
PRIMARY KEY (dbid),
FOREIGN KEY (ppm_dbid) REFERENCES pph.ppms (dbid)
);
bin/bgpmon_analytics_db_1_pphFunctions.psql view on Meta::CPAN
/*Script to redefine the functions used for the 6watch database
run like so: " > psql -f 1_import_functions dbname dbuser"*/
/*Helper function to dynamically generate a peer's table name*/
DROP FUNCTION IF EXISTS pph.generate_peer_table_name(INET,VARCHAR);
DROP FUNCTION IF EXISTS pph.add_or_lookup_peer(INET,VARCHAR);
DROP FUNCTION IF EXISTS pph.add_or_lookup_prefix(CIDR);
DROP FUNCTION IF EXISTS pph.add_or_lookup_ppm(INET,VARCHAR,CIDR);
DROP FUNCTION IF EXISTS pph.add_new_timerange(INET,VARCHAR,CIDR,BOOL,TIMESTAMP,
VARCHAR,VARCHAR,VARCHAR ARRAY);
/******************************************************************************
* generate_peer_table_name
* input: peer and collector
* output: the name of an individual peer's timerange
* effects:
******************************************************************************/
CREATE OR REPLACE FUNCTION pph.generate_peer_table_name(INET,VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
RETURN 'pph.' || $2 || '_' || host($1);
END;
$$ LANGUAGE plpgsql;
/******************************************************************************
bin/bgpmon_analytics_db_1_pphFunctions.psql view on Meta::CPAN
DECLARE
peer_id INTEGER;
peer_table VARCHAR;
BEGIN
SELECT dbid INTO peer_id FROM pph.peers WHERE addr = $1 AND collector = $2;
IF NOT FOUND THEN
INSERT INTO pph.peers (addr,collector) VALUES ($1,$2)
RETURNING dbid INTO peer_id;
peer_table := pph.generate_peer_table_name($1,$2);
EXECUTE 'CREATE TABLE '||quote_ident(peer_table)||
' () INHERITS (pph.timeranges)';
EXECUTE 'CREATE INDEX "'||peer_table||'_start_time_index" ON '||
quote_ident(peer_table)||' (start_time)';
EXECUTE 'CREATE INDEX "'||peer_table||'_end_time_index" ON '||
quote_ident(peer_table)||' (end_time)';
EXECUTE 'CREATE INDEX "'||peer_table||'_ppm_dbid_index" ON '||
quote_ident(peer_table)||' (ppm_dbid)';
EXECUTE 'CREATE INDEX "'||peer_table||'_dbid_index" ON '||
quote_ident(peer_table)||' (dbid)';
END IF;
RETURN peer_id;
bin/bgpmon_analytics_db_1_pphFunctions.psql view on Meta::CPAN
(peer_id,prefix_id);
IF NOT FOUND THEN
INSERT INTO pph.ppms (peer_dbid,prefix_dbid) VALUES (peer_id,prefix_id)
RETURNING dbid INTO ppm_id;
END IF;
RETURN ppm_id;
END;
$$ LANGUAGE plpgsql;
/******************************************************************************
* add_new_timerange
* input: peer address, collector name, prefix, ann=true|with=false, timestamp
* origin ASN, second to last hop ASN
* output: dbid of timerange record
* effects: will add the peer, prefix and ppm if it is not found
$1 INET peer addr
$2 VARHAR peer collector
$3 CIDR prefix
$4 BOOL announce (true) withdraw (false)
$5 TIMESTAMP
$6 INTEGER origin ASN
$7 INTEGER last hop
$8 INTEGER ARRAY as path
******************************************************************************/
CREATE OR REPLACE FUNCTION pph.add_new_timerange(INET,VARCHAR,CIDR,BOOL,
TIMESTAMP, VARCHAR,VARCHAR,
VARCHAR ARRAY)
RETURNS VOID AS $$
DECLARE
ppm_id INTEGER;
peer_table VARCHAR;
current_timerange INTEGER;
current_origin_as VARCHAR;
current_start_time TIMESTAMP;
current_end_time TIMESTAMP;
current_last_hop VARCHAR;
current_as_path VARCHAR ARRAY;
BEGIN
/*First, check/add the peer, prefix, and ppm into the database*/
ppm_id := pph.add_or_lookup_ppm($1,$2,$3);
/*Since the ppm is now guaranteed to be there, check if there are any
existing timerange records for the ppm and get the peer table's name.*/
SELECT last_timerange_dbid INTO current_timerange FROM pph.ppms WHERE dbid=ppm_id;
peer_table := pph.generate_peer_table_name($1,$2);
/* If there are no existing timeranges,
then add a new one with slightly different
information depending on whether the message is an annoucement or
withdrawl*/
IF current_timerange IS NULL THEN
IF $4 = 'TRUE' THEN
EXECUTE 'INSERT INTO '||quote_ident(peer_table)||
' (ppm_dbid,start_time,origin_as,last_hop,as_path) VALUES ('
||ppm_id||','''||$5||''','''||$6||''','''||$7||''','||
quote_literal($8)||')
RETURNING dbid' INTO current_timerange;
UPDATE pph.ppms SET last_timerange_dbid = current_timerange WHERE dbid=ppm_id;
RETURN;
ELSE
RAISE WARNING
'Prefix withdrawn without prior announcement: collector:% peer:% prefix:%'
, $2,$1,$3;
EXECUTE 'INSERT INTO '||quote_ident(peer_table)||
' (ppm_dbid,end_time) VALUES ('||ppm_id||','''||$5||''')
RETURNING dbid' INTO current_timerange;
UPDATE pph.ppms SET last_timerange_dbid = current_timerange WHERE dbid=ppm_id;
RETURN;
END IF;
ELSE
/* Otherwise, check the end_time of the most recent timerange record.*/
EXECUTE 'SELECT end_time FROM '||quote_ident(peer_table)||
' WHERE dbid = '||current_timerange||'' INTO current_end_time;
EXECUTE 'SELECT start_time FROM '||quote_ident(peer_table)||
' WHERE dbid = '||current_timerange||'' INTO current_start_time;
/*If the end_time is not populated, then the prefix is still active and so
either the prefix is being withdrawn, the origin AS is changing,
or the announcement is a duplicate.*/
/* check for time travelers*/
IF $5 < current_end_time OR (current_end_time IS NULL AND
$5 < current_start_time) THEN
IF current_end_time IS NULL THEN
RAISE WARNING 'Update received out of order: collector:% peer:% prefix:% old_time:% new_time:%',
bin/bgpmon_analytics_db_1_pphFunctions.psql view on Meta::CPAN
$2, $1, $3, current_end_time, $5;
END IF;
RETURN;
END IF;
IF current_end_time IS NULL THEN
/*If the prefix is being withdrawn, then set the end_time and return.*/
IF $4 = 'FALSE' THEN
EXECUTE 'UPDATE '||quote_ident(peer_table)||' SET end_time = '''||$5||
''' WHERE dbid = '||current_timerange||'';
RETURN;
ELSE
/*If the message is an announcement, then we have to check the origin
ASN and next hop. If either changes, then we have to close out the old
timerange and create a new one.*/
/*If the ASN and Next Hop are the same, then this is a duplicate
announcement, and we can ignore it.*/
EXECUTE 'SELECT as_path FROM '||quote_ident(peer_table)||
' WHERE dbid = ' ||current_timerange||'' INTO current_as_path;
IF $8 = current_as_path THEN
RETURN;
ELSE
EXECUTE 'UPDATE '||quote_ident(peer_table)||' SET end_time = '''||$5||
''' WHERE dbid = '||current_timerange||'';
EXECUTE 'INSERT INTO '||quote_ident(peer_table)||
' (ppm_dbid,start_time,origin_as,last_hop,as_path) VALUES ('
||ppm_id||','''||$5||''','''||$6||''','''||$7||''','||
quote_literal($8)||') RETURNING dbid' INTO current_timerange;
UPDATE pph.ppms SET last_timerange_dbid = current_timerange
WHERE dbid=ppm_id;
RETURN;
END IF;
END IF;
ELSE
/* If the end-time field IS filled, then either the prefix is being
re-announced or this message is a duplicate withdrawal.*/
IF $4 = 'TRUE' THEN
EXECUTE 'INSERT INTO '||quote_ident(peer_table)||
' (ppm_dbid,start_time,origin_as,last_hop,as_path) VALUES ('
||ppm_id||','''||$5||''','''||$6||''','''||$7||''','||
quote_literal($8)||') RETURNING dbid' INTO current_timerange;
UPDATE pph.ppms SET last_timerange_dbid = current_timerange
WHERE dbid = ppm_id;
RETURN;
ELSE
RETURN;
END IF;
END IF;
END IF;
END;
$$ LANGUAGE plpgsql;
bin/bgpmon_analytics_db_1_pphFunctions.psql view on Meta::CPAN
* input:
* output: dbid
* effects: will add the peer, prefix and ppm if it is not found
******************************************************************************/
CREATE OR REPLACE FUNCTION pph.inject_updates() RETURNS INTEGER AS $$
DECLARE
c_updates CURSOR FOR SELECT * FROM pph.update_import;
BEGIN
FOR update_rec IN c_updates LOOP
PERFORM pph.add_new_timerange(update_rec.peer,update_rec.collector,
update_rec.prefix,update_rec.update,update_rec.ts,
update_rec.origin,update_rec.lasthop,update_rec.aspath);
END LOOP;
truncate pph.update_import;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
bin/bgpmon_analytics_db_2_pphQueryFuncs.psql view on Meta::CPAN
DROP FUNCTION IF EXISTS pph.get_unique_prefix_growth(INET,VARCHAR,TIMESTAMP,
TIMESTAMP,INTERVAL);
DROP FUNCTION IF EXISTS pph.get_unique_prefixes_at_time(INET,VARCHAR,TIMESTAMP);
/******************************************************************************
* find_most_active_prefix
* input:
* output: returns prefix
* effects: find the prefix withthe most timerange entries (across all peers)
******************************************************************************/
CREATE OR REPLACE FUNCTION pph.find_most_active_prefix() RETURNS CIDR AS $$
DECLARE
p CURSOR FOR SELECT * FROM pph.prefixes;
top_pref CIDR;
top_count INTEGER = 0;
curr_count INTEGER = 0;
BEGIN
FOR prefix IN p LOOP
EXECUTE 'SELECT COUNT(1)
FROM pph.timeranges AS t, pph.ppms AS p1, pph.prefixes AS p2
WHERE p2.pref = '''||prefix.pref||''' AND
p2.dbid = p1.prefix_dbid AND
t.ppm_dbid = p1.dbid' INTO curr_count;
IF curr_count > top_count THEN
top_count = curr_count;
top_pref = prefix.pref;
END IF;
END LOOP;
RETURN top_pref;
END;
bin/bgpmon_analytics_db_2_pphQueryFuncs.psql view on Meta::CPAN
/******************************************************************************
* get_prefixes_per_as
* input: ASN
* output: set of prefixes
* effects:
******************************************************************************/
CREATE OR REPLACE FUNCTION pph.get_prefixes_per_as(INTEGER) RETURNS SETOF CIDR AS $$
BEGIN
RETURN QUERY
SELECT DISTINCT pref
FROM pph.prefixes AS p1,pph.ppms AS p2,pph.timeranges AS t
WHERE origin_as = $1 AND
t.ppm_dbid = p2.dbid AND
p2.prefix_dbid = p1.dbid;
END;
$$ LANGUAGE plpgsql;
/******************************************************************************
* get_as_per_prefix
* input: prefix
* output: set of ASNs
* effects:
******************************************************************************/
CREATE OR REPLACE FUNCTION pph.get_as_per_prefix(CIDR) RETURNS SETOF INTEGER AS $$
BEGIN
RETURN QUERY
SELECT DISTINCT origin_as
FROM pph.prefixes AS p1,pph.ppms AS p2,pph.timeranges AS t
WHERE pref = $1 AND
t.ppm_dbid = p2.dbid AND
p2.prefix_dbid = p1.dbid;
END;
$$ LANGUAGE plpgsql;
/******************************************************************************
* get_table_growth
* input: peer address, collector, start, end, timestep
* output: set of ASNs
( run in 0.233 second using v1.01-cache-2.11-cpan-87723dcf8b7 )