testdata

opennms得相关源码 请大家看看

--Insert test data into the necessary tables to get the count of the number of services managed on a node.
-- This is the required data that will be need for the computation of getManagedServiceCountForNode(nodeid)
-- I've taken data such that it will compute for the last weeks worth of outages, where the week would 
-- starting '19-Nov-2001 00:00:00' and ending on '25-Nov-2001 24:00:00'

delete from outages;
delete from ifservices;
delete from ipinterface;
delete from node;
delete from service;
drop sequence nodeNxtId;
drop sequence serviceNxtId;
create sequence nodeNxtId minvalue 1;
create sequence serviceNxtId minvalue 1;

insert into node values (1, 'localhost', '26-Nov-2001 11:15:00', 0, 'A', null, null, null, null, null, 'test-node-1', 'H' );

insert into ipinterface values (1, '192.168.100.1', null, 'test-node-1', 'M');

insert into service values (1, 'SNMP');
insert into service values (2, 'DHCP');
insert into service values (3, 'MSExchange');
insert into service values (4, 'HTTP');
insert into service values (5, '');
insert into service values (6, 'Postgres');
insert into service values (7, 'MySQL');
insert into service values (8, 'DNS');
insert into service values (9, 'ICMP');
insert into service values (10, 'SSH');
insert into service values (11, 'SQLServer');
insert into service values (12, 'FTP');
insert into service values (13, 'Oracle');
insert into service values (14, 'POP3');
insert into service values (15, 'Informix');
insert into service values (16, 'SMTP');
insert into service values (17, 'IMAP');
insert into service values (18, '');
insert into service values (19, 'Router');
insert into service values (20, 'Sybase');
insert into service values (21, 'SNMPv2');

insert into ifservices values (1, '192.168.100.1', null, 1, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 2, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 3, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 4, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 5, null, null, null, 'U');
insert into ifservices values (1, '192.168.100.1', null, 6, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 7, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 8, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 9, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 10, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 11, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 12, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 13, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 14, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 15, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 16, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 17, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 18, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 19, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 20, null, null, null, 'A');
insert into ifservices values (1, '192.168.100.1', null, 21, null, null, null, 'A');

-- Ignored outage conditions  	a) Start time < End Time < Lost Time
--				b) Regained Time = NOT NULL and Regained Time < Start Time 

-- Special boundary conditions	a) Regained Time = null and (i)	Start Time < Lost Time < End Time
--							    (ii)Lost Time < Start Time
--							    (iii) Start Time < End Time < Lost Time
--				b) Lost Time < Start Time and  	(i) Start Time < Regained Time < End Time
--							  	(ii)Start Time < End Time < Regained Time 
--				c) Start Time < Lost Time < End Time < Regained Time 

-- Outages within the rolling window: Start Time < Lost Time < Regained Time < End Time 

insert into outages values (1, '192.168.100.1', 1, '18-Nov-2001 12:16:43', '18-Nov-2001 12:30:31'); 
insert into outages values (1, '192.168.100.1', 1, '18-Nov-2001 15:30:28', '19-Nov-2001 03:22:56');
insert into outages values (1, '192.168.100.1', 1, '19-Nov-2001 04:23:55', '26-Nov-2001 09:00:15'); 
insert into outages values (1, '192.168.100.1', 1, '26-Nov-2001 09:23:08', '26-Nov-2001 03:34:41');

insert into outages values (1, '192.168.100.1', 2, '18-Nov-2001 12:16:43', '18-Nov-2001 12:30:31');
insert into outages values (1, '192.168.100.1', 2, '18-Nov-2001 15:30:28', '26-Nov-2001 03:34:41');

insert into outages values (1, '192.168.100.1', 3, '18-Nov-2001 12:16:43', '18-Nov-2001 12:30:31');
insert into outages values (1, '192.168.100.1', 3, '18-Nov-2001 15:30:28');

insert into outages values (1, '192.168.100.1', 4, '18-Nov-2001 12:16:43', '18-Nov-2001 12:30:31');
insert into outages values (1, '192.168.100.1', 4, '18-Nov-2001 15:30:28', '19-Nov-2001 03:22:56');
insert into outages values (1, '192.168.100.1', 4, '19-Nov-2001 03:31:11', '19-Nov-2001 03:40:48');
insert into outages values (1, '192.168.100.1', 4, '20-Nov-2001 04:17:01', '20-Nov-2001 04:18:08');
insert into outages values (1, '192.168.100.1', 4, '20-Nov-2001 12:51:20', '20-Nov-2001 13:00:00');
insert into outages values (1, '192.168.100.1', 4, '23-Nov-2001 14:01:00', '23-Nov-2001 15:06:49');
insert into outages values (1, '192.168.100.1', 4, '23-Nov-2001 15:30:28');

insert into outages values (1, '192.168.100.1', 6, '18-Nov-2001 12:16:43', '18-Nov-2001 12:30:31');
insert into outages values (1, '192.168.100.1', 6, '18-Nov-2001 15:30:28', '19-Nov-2001 03:22:56');
insert into outages values (1, '192.168.100.1', 6, '19-Nov-2001 03:31:11', '19-Nov-2001 03:40:48');
insert into outages values (1, '192.168.100.1', 6, '20-Nov-2001 04:17:01', '20-Nov-2001 04:18:08');
insert into outages values (1, '192.168.100.1', 6, '20-Nov-2001 12:51:20', '20-Nov-2001 13:00:00');
insert into outages values (1, '192.168.100.1', 6, '23-Nov-2001 14:01:00', '23-Nov-2001 15:06:49');
insert into outages values (1, '192.168.100.1', 6, '25-Nov-2001 00:56:07', '25-Nov-2001 09:43:09');
insert into outages values (1, '192.168.100.1', 6, '26-Nov-2001 03:23:08', '26-Nov-2001 03:34:41');

insert into outages values (1, '192.168.100.1', 5, '18-Nov-2001 12:16:43', '18-Nov-2001 12:30:31');
insert into outages values (1, '192.168.100.1', 5, '18-Nov-2001 15:30:28', '19-Nov-2001 03:22:56');
insert into outages values (1, '192.168.100.1', 5, '19-Nov-2001 03:31:11', '19-Nov-2001 03:40:48');
insert into outages values (1, '192.168.100.1', 5, '20-Nov-2001 04:17:01', '20-Nov-2001 04:18:08');
insert into outages values (1, '192.168.100.1', 5, '20-Nov-2001 12:51:20', '20-Nov-2001 13:00:00');
insert into outages values (1, '192.168.100.1', 5, '23-Nov-2001 14:01:00', '23-Nov-2001 15:06:49');
insert into outages values (1, '192.168.100.1', 5, '25-Nov-2001 00:56:07', '25-Nov-2001 09:43:09');
insert into outages values (1, '192.168.100.1', 5, '26-Nov-2001 03:23:08', '26-Nov-2001 03:34:41');

select getManagedServiceCountForNode(1);
select getOutageTimeInWindow(1, '192.168.100.1', 1, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getOutageTimeInWindow(1, '192.168.100.1', 2, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getOutageTimeInWindow(1, '192.168.100.1', 3, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getOutageTimeInWindow(1, '192.168.100.1', 4, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getOutageTimeInWindow(1, '192.168.100.1', 5, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getOutageTimeInWindow(1, '192.168.100.1', 6, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getOutageTimeInWindow(1, '192.168.100.1', 7, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getManagedOutageForNodeInWindow(1, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getPercentAvailabilityInWindow(1, '192.168.100.1', 1, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getPercentAvailabilityInWindow(1, '192.168.100.1', 2, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getPercentAvailabilityInWindow(1, '192.168.100.1', 3, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getPercentAvailabilityInWindow(1, '192.168.100.1', 4, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getPercentAvailabilityInWindow(1, '192.168.100.1', 5, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getPercentAvailabilityInWindow(1, '192.168.100.1', 6, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getPercentAvailabilityInWindow(1, '192.168.100.1', 7, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');
select getManagePercentAvailNodeWindow(1, '25-Nov-2001 24:00:00', '19-Nov-2001 00:00:00');