How to interface LoriotPro with ORACLE

 

This How to explains how to create the LoriotPro database under ORACLE.

First of all, thanks to Sylvain Tremblay, a Technical support principal advisor & DBA from Ivaco inc. CANADA who has realized this interface.
Contact: tremblay@ivaco.com

LoriotPro allows you to store information (for example the received or sent events) in any ODBC compliant Database. LoriotPro do not provide a Database service but supports ODBC drivers provided with your preferred Database Engine.

Open Database Connectivity (ODBC) is a widely accepted application programming interface (API) for database access. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language.

The context used for running this interface was:

LoriotPro runs on a Pentium IV with Windows 2000
LoriotPro Version 2 and build 136-av.
Oracle version 9iR2

On the Oracle server you should setup the SQLNET.ORA and TNANAMES.ORA files.

This could be done during the Oracle installation in the Network Config window.

The ODBC interface was setup as hereunder:

Fisrt task to perform is the user creation for accessing this Database.

CREATE USER LORIOTPRO IDENTIFIED BY password DEFAULT TABLESPACE
tablespace_name TEMPORARY TABLESPACE temporary_tablespace_name QUOTA
UNLIMIMTED ON tablespace_name;
GRANT CONNECT, CREATE TRIGGER TO LORIOTPRO;
ALTER USER LORIOTPRO DEFAULT ROLE CONNECT;

Then, the creation of the LoriotPro Table

--
-- Table structure for table 'loriot_events'
--

CREATE TABLE loriot_events (
l_number number NOT NULL,
l_timestamp number ,
l_manager_id number DEFAULT 0 ,
l_reference number DEFAULT 0 ,
l_level number DEFAULT 0,
l_string varchar2(255) DEFAULT '0' ,
l_ip_agent_add varchar2(15) NOT NULL ,
l_ip_source_add varchar2(15) NOT NULL ,
constraint loriot_events_pk PRIMARY KEY (l_number)
);
create index loriot_events_key1 on loriot_events
(l_manager_id,l_reference,l_level,l_number,l_ip_agent_add);

CREATE SEQUENCE loriot_events_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 10
NOORDER;

CREATE OR REPLACE TRIGGER loriot_events_i_trig
BEFORE INSERT
ON loriot_events
FOR EACH ROW
BEGIN
Select loriot_events_seq.NextVal
Into :NEW.l_number
From Dual;
END;
/

--
-- Table structure for table 'loriot_hosts'
--

CREATE TABLE loriot_hosts (
l_host_id number DEFAULT 0 NOT NULL ,
l_ip_addr varchar2(15) ,
l_name varchar2(100) NOT NULL ,
l_mac varchar2(18) NOT NULL ,
l_groupref number ,
l_manager_id number DEFAULT 0 NOT NULL ,
l_polltimeout number ,
l_lastsnmppoll number DEFAULT 0 ,
l_lasticmppoll number DEFAULT 0 ,
constraint loriot_hosts_pk PRIMARY KEY (l_host_id)
);

--
-- Table structure for table 'loriot_managers'
--

CREATE TABLE loriot_managers (
l_id number DEFAULT 0 NOT NULL ,
l_name varchar2(50) NOT NULL ,
l_loriotkey varchar2(32) NOT NULL ,
l_ip number DEFAULT 0 NOT NULL ,
l_ip_addr varchar2(15) NOT NULL ,
l_version number DEFAULT 0 NOT NULL ,
l_http number DEFAULT 0 ,
constraint loriot_managers_pk PRIMARY KEY (l_id)
);

--
-- Table structure for table 'loriot_netflow'
--

CREATE TABLE loriot_netflow (
l_number number NOT NULL ,
l_timestamp number DEFAULT 0 NOT NULL ,
l_sysuptime number ,
l_version number ,
l_host_id number DEFAULT 0 NOT NULL ,
l_engine_type number ,
l_engine_id number ,
l_ip_src varchar2(15) ,
l_ip_dst varchar2(15) ,
l_srcaddr number ,
l_dstaddr number ,
l_nexthop number ,
l_input number ,
l_output number ,
l_dPkts number ,
l_dOctets number ,
l_first number ,
l_last number ,
l_srcport number ,
l_dstport number ,
l_tcp_flags number ,
l_prot number ,
l_tos number ,
l_dst_as number ,
l_src_as number ,
l_dst_mask number ,
l_src_mask number ,
l_flow_sequence number ,
constraint loriot_netflow_pk primary key
(l_number,l_host_id,l_srcaddr,l_dstaddr,l_nexthop)
);

CREATE SEQUENCE loriot_netflow_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 10
NOORDER;

CREATE OR REPLACE TRIGGER loriot_netflow_i_trig
BEFORE INSERT
ON loriot_netflow
FOR EACH ROW
BEGIN
Select loriot_netflow_seq.NextVal
Into :NEW.l_number
From Dual;
END;
/

--
-- Table structure for table 'loriot_networks'
--

CREATE TABLE loriot_networks (
l_network_id number DEFAULT 0 NOT NULL ,
l_mask number DEFAULT 0 NOT NULL ,
l_net_add varchar2(15) ,
l_name varchar2(100) NOT NULL ,
l_ifdesc varchar2(100) ,
l_iftype varchar2(100) ,
l_speed number ,
l_manager_id number DEFAULT 0 NOT NULL ,
constraint loriot_networks_pk PRIMARY KEY (l_network_id)
);

When done you could check that your Database is reachable by using our SQL query tool in the supervise option

If this works your ODBC setup and Database access is working. You could now exploit your data.

Don't forget to consult our documentation to have details on interfacing LoriotPro with Databases.

End of document