3.1.0.sql

监控大型网络的软件。能够自动发现拓扑结构

/*
 *
 * This SQL script is designed to upgrade your NAV database from
 * version 3.0 to 3.1
 *
 * Connect to PostgreSQL as the postgres superuser or the nav database user
 * like this:
 *
 *  psql -f 3.1.0.sql manage <username>
 *
 * The new subsystem Arnold also needs a new database, while some vendors,
 * types and snmpoids have been added and/or updated.  Therefore you must read
 * doc/sql/README over again and make sure to follow its instructions on how
 * to create the arnold database and run the following scripts:
 *
 *   - arnold.sql
 *   - types.sql
 *   - snmpoid.sql
 *
*/

\c manage

BEGIN;
\echo Changing manage schema

ALTER TABLE snmpoid ADD COLUMN defaultfreq INT4;
ALTER TABLE snmpoid ALTER COLUMN defaultfreq SET DEFAULT 21600;
UPDATE snmpoid SET defaultfreq=21600;
ALTER TABLE snmpoid ALTER COLUMN defaultfreq SET NOT NULL;

-- This view gives the allowed vlan for a given hexstring i swportallowedvlan
CREATE TABLE range (
  num INT NOT NULL PRIMARY KEY
);
INSERT INTO range VALUES (0);
INSERT INTO range (SELECT num+(SELECT COUNT(*) FROM range) FROM range);
INSERT INTO range (SELECT num+(SELECT COUNT(*) FROM range) FROM range);
INSERT INTO range (SELECT num+(SELECT COUNT(*) FROM range) FROM range);
INSERT INTO range (SELECT num+(SELECT COUNT(*) FROM range) FROM range);
INSERT INTO range (SELECT num+(SELECT COUNT(*) FROM range) FROM range);
INSERT INTO range (SELECT num+(SELECT COUNT(*) FROM range) FROM range);
INSERT INTO range (SELECT num+(SELECT COUNT(*) FROM range) FROM range);
INSERT INTO range (SELECT num+(SELECT COUNT(*) FROM range) FROM range);
INSERT INTO range (SELECT num+(SELECT COUNT(*) FROM range) FROM range);
INSERT INTO range (SELECT num+(SELECT COUNT(*) FROM range) FROM range);
DELETE FROM range WHERE num >= 1000;

CREATE VIEW allowedvlan AS
  (SELECT swportid,num AS allowedvlan FROM swportallowedvlan CROSS JOIN range
    WHERE num < length(decode(hexstring,'hex'))*8 AND (CASE WHEN length(hexstring)=256
    THEN get_bit(decode(hexstring,'hex'),(num/8)*8+7-(num%8))
    ELSE get_bit(decode(hexstring,'hex'),(length(decode(hexstring,'hex'))*8-num+7>>3<<3)-8+(num%8))
    END)=1);

CREATE VIEW allowedvlan_both AS
  (select swportid,swportid as swportid2,allowedvlan from allowedvlan ORDER BY allowedvlan) union
  (select  swport.swportid,to_swportid as swportid2,allowedvlan from swport join allowedvlan
    on (swport.to_swportid=allowedvlan.swportid) ORDER BY allowedvlan);

\echo Adding devBrowse as a subsystem to the event system
INSERT INTO subsystem (name) VALUES ('devBrowse');

\echo Updating existing equipment types in your database
UPDATE "type" SET vendorid='cisco', typename='cisco12416', cdp='1', tftp='1', cs_at_vlan='1', chassis='1', frequency='3600', descr='Cisco 12416 (GSR) Router' WHERE sysobjectid='1.3.6.1.4.1.9.1.385';
UPDATE "type" SET vendorid='cisco', typename='cisco1751', cdp='1', tftp='1', cs_at_vlan='1', chassis='0', frequency='3600', descr='Cisco 1751 Router' WHERE sysobjectid='1.3.6.1.4.1.9.1.326';
UPDATE "type" SET vendorid='3com', typename='PS40', cdp='0', tftp='0', cs_at_vlan='0', chassis='1', frequency='3600', descr='Portstack 40 hub' WHERE sysobjectid='1.3.6.1.4.1.43.10.27.4.1';
UPDATE "type" SET vendorid='3com', typename='SW1100', cdp=NULL, tftp=NULL, cs_at_vlan='0', chassis='1', frequency='3600', descr='Portswitch 1100' WHERE sysobjectid='1.3.6.1.4.1.43.10.27.4.1.2.1';
UPDATE "type" SET vendorid='3com', typename='SW3300', cdp=NULL, tftp=NULL, cs_at_vlan='0', chassis='1', frequency='3600', descr='Portswitch 3300' WHERE sysobjectid='1.3.6.1.4.1.43.10.27.4.1.2.2';
UPDATE "type" SET vendorid='3com', typename='SW9300', cdp=NULL, tftp=NULL, cs_at_vlan='0', chassis='1', frequency='3600', descr='Portswitch 9300' WHERE sysobjectid='1.3.6.1.4.1.43.1.16.2.2.2.1';
UPDATE "type" SET vendorid='alcatel', typename='alcatel6800', cdp=NULL, tftp=NULL, cs_at_vlan='0', chassis='0', frequency='3600', descr='Alcatel Omniswitch 6800' WHERE sysobjectid='1.3.6.1.4.1.6486.800.1.1.2.1.6.1.1';
UPDATE "type" SET vendorid='cisco', typename='catalyst2924XL', cdp='1', tftp='1', cs_at_vlan='1', chassis='1', frequency='3600', descr='Catalyst 2924 XL switch' WHERE sysobjectid='1.3.6.1.4.1.9.1.183';
UPDATE "type" SET vendorid='cisco', typename='catalyst2924XLv', cdp='1', tftp='1', cs_at_vlan='1', chassis='0', frequency='3600', descr='Catalyst 2924 XLv switch' WHERE sysobjectid='1.3.6.1.4.1.9.1.217';
UPDATE "type" SET vendorid='cisco', typename='catalyst295024G', cdp='1', tftp='1', cs_at_vlan='1', chassis='0', frequency='3600', descr='Catalyst 2950G-24-E1 switch' WHERE sysobjectid='1.3.6.1.4.1.9.1.428';
UPDATE "type" SET vendorid='cisco', typename='catalyst295048G', cdp='1', tftp='1', cs_at_vlan='1', chassis='0', frequency='3600', descr='Catalyst 295048G' WHERE sysobjectid='1.3.6.1.4.1.9.1.429';
UPDATE "type" SET vendorid='cisco', typename='catalyst297024TS', cdp='1', tftp='1', cs_at_vlan='1', chassis='1', frequency='3600', descr='Catalyst 2970' WHERE sysobjectid='1.3.6.1.4.1.9.1.561';