PathSolutions Support Portal

ODBC data exports can be made via the Enterprise Module. The ODBC database connector should be configured to connect to the
database and have INSERT capabilities.

Data Warehouse table format for ODBC Exports:
Table name: Poll

Size (bytes) Field Name Type
10 Node Text
8 PollDate Date, auto-populate current date upon insert
16 Agent Text
4 Interface Long Integer
256 ifAlias Text
4 ifType Long Integer
4 ifMTU Long Integer
4 ifSpeed Long Integer
4 ifHighSpeed Long Integer
4 ifAdminStatus Long Integer
4 ifOperStatus Long Integer
4 ifLastChange Long Integer
4 ifInOctets Long Integer
4 ifInUcastPkts Long Integer
4 ifInNUcastPkts Long Integer
4 ifInDiscards Long Integer
4 ifiNErrors Long Integer
4 ifOutOctets Long Integer
4 ifOutUCastPkts Long Integer
4 ifOutUCastPkts Long Integer
4 ifOutDiscards Long Integer
4 iifOutErrors Long Integer
Total: 362 bytes per record(row)

Note: One record(row) is inserted per interface per polling period.  To calculate database growth, multiply 362 bytes by the number of polls per month, then multiply by the number of monitored interfaces.

Example
If the polling interval is 5 minutes, there are 8,928 polls that occur per month:
(31 days * 24 hours * 60 minutes / 5 minutes per poll) = 8,928 polls

If 1,000 interfaces are monitored, multiply the interfaces by the number of polls, then multiply by the record size:
(1,000 interfaces * 8,928 polls * 362 bytes) = 3,003,876,000 bytes

Note: The database should be configured to auto-populate the PollDate field with the current date and time upon record insertion.

Note: To help manage database size, the ifAlias text field can be made larger or smaller depending on the size of the interface descriptions configured on devices. If the field is set too short, then the field will be truncated when data is inserted.

SQL Create Script
Creating the Poll table can be accomplished with the following script:

CREATE TABLE Poll
(ID bigint IDENTITY(1,1) NOT NULL,
Node varchar(10) NOT NULL,
PollDate datetime DEFAULT GETDATE() NOT NULL,
Agent varchar(16) NOT NULL,
Interface integer NOT NULL,
ifAlias varchar(256) NOT NULL,
ifType integer NOT NULL,
ifMTU integer NOT NULL,
ifSpeed integer NOT NULL,
ifHighSpeed integer NOT NULL,
ifAdminStatus integer NOT NULL,
ifOperStatus integer NOT NULL,
ifLastChange integer NOT NULL,
ifInOctets integer NOT NULL,
ifInUcastPkts integer NOT NULL,
ifInNUcastPkts integer NOT NULL,
ifInDiscards integer NOT NULL,
ifInErrors integer NOT NULL,
ifOutOctets integer NOT NULL,
ifOutUcastPkts integer NOT NULL,
ifOutNUcastPkts integer NOT NULL,
ifOutDiscards integer NOT NULL,
ifOutErrors integer NOT NULL)
CONSTRAINT pk_poll PRIMARY KEY CLUSTERED (ID);

Adding Indexes
To speed performance of reporting, it may be advised to create indexes to help speed queries. Care should be taken to not create too many indexes because each index will slow down the performance of the record insert.

PollDate Index creation
CREATE INDEX ix_Poll_PollDate
ON Poll
(PollDate);

Agent & Interface Index creation
CREATE INDEX ix_Poll_AgentInterface
ON Poll
(Agent, Interface);

Dropping Indexes
Database performance may become a problem on large databases with many indexes. As a result, it may take too long to have each record inserted due to the indexes that have to be updated. Indexes can be removed with the following statement:

DROP INDEX ix_Poll_AgentInterface;