Save SNMP Traps into a MySQL database

This article explains how you can store SNMP traps as they arrive into a MySQL database using the Unbrowse SNMP Scripting Interface. We introduce a sample schema and a script which you can easily adapt to your requirements.

You can script the behavior of Unbrowse SNMP via VBScript or Ruby. This is done by accessing various objects defined in the Unbrowse Scripting Reference.

In this article we will script the SNMP Trap Receiver to

1. Open a network adapter and listen to all SNMP Traps on it
2. Open a ODBC connection to a MySQL database
3. As each trap arrives insert a row into the database containing the trap details.
4. Close the trap receiver.
5. Close the ODBC connection.

New ! Use Ruby to script the Unbrowse SNMP Trap receiver. Sample code available at end of this page.

The setup

Our setup consists of a MySQL database on a Linux box which will act as a central repository of traps. We can then have many instances of Unbrowse SNMP insert trap records into the database. We will use the MySQL ODBC connector to establish the database connection.

Note that you can also use a local database or even a different database like Oracle, Postgres, or SQL Server.

Scripting SNMP Trap Reception and Handling with Unbrowse SNMP

Contents
About scripting SNMP Traps
The setup
Setting up the MySQL database
Setting up the ODBC connection
Running the script
Notes
Script Files
Resources
Unbrowse SNMP Scripting Guide
TrapReceiver object methods

The following diagram explains the setup.

SNMP Traps to MySQL network setup

Setting up the MySQL database

1. Assume you have installed MySQL and have it up and running.

2. Create a new database into which we will store traps.
   2.1 Login as admin into MySQL
   2.2 Create a database called unbrowse ( command create database unbrowse; )

3. Create the table 'alltraps'. Use the file ubtraps.schema as follows.
   2.1 login as admin into MySQL
   2.2 Switch to the unbrowse database via "use unbrowse;"
   2.3 Type "source ubtraps.schema;" to create the table
   2.4 Check table via "desc alltraps;"

4. Create a new user for Unbrowse SNMP to connect.

   2.1 login as an admin
   2.2 create user 'unbrowse'@'192.168.1.10';
   2.3 Grant permissions via grant all privileges on unbrowse.* to 'unbrowse'@'192.168.1.10' identified by 'ubpass';
   2.4 Save via flush privileges;

Note : In the above example '192.168.1.10' is the IP address of the remote PC running Unbrowse SNMP. You can use a '%' command to allow connections from any IP address. Modify the IP address according to your setup.

Setting up the ODBC connection

1. Download and install the MySQL ODBC Connector (Version 5.1) from http://dev.mysql.com/downloads/connector/odbc/5.1.html

Note : You do not need to install MySQL on the PC running Unbrowse SNMP. Just the ODBC connector will do.

2. Open Control Panel -> Administrative Tools -> Data Sources

3. Switch to the "User DSN" tab.

4. Click on "Add.." to create a new data source.

5. From the list select the MySQL 5.1 ODBC connector.

6. Enter the connetion and authentication details of the MySQL server into the box.

ODBC connection screen

Write down the name of the DSN (in the screenshot above it is called "unb")

Running the script

1. Download the t2sql.vbs script into a folder.

2. CD to the folder above.

3. Run the script via
cscript t2sql.vbs thedsnname
thedsnname = The name of the data source you specified when you configured the ODBC connection

4. Thats it. You can stop it by pressing Ctrl+C

The script will insert each trap as it sees them into the MySQL database.

Notes

1. This is a sample script and schema.

2. Only a maximum of 16 variable bindings per SNMP trap are supported.

3. The script inserts raw OIDs into the table instead of names. You can easily enhance it by writing tiny code to translate the OID. See oidtrans.vbs

4. It is not suitable for high performance or high volume traps. On our testbed the script could handle 3-5 traps per second.

5. You are free to extend this script and use it any way you like. No warranties are provided (see license statement in the script file).

Script Files

t2sql.vbs	VBScript program to insert SNMP traps automatically into a SQL database via ODBC. This version uses Winpcap to capture SNMP traps.
t2sqludp.vbs	Same as above (t2sql.vbs) but uses normal UDP sockets instead of Winpcap to capture SNMP Traps
unbtrap.schema	SQL table creation script
t2sql.rb	Ruby program to insert SNMP traps into a MySQL database
t2sql_buf.vbs	Demonstrates use of Buffered mode which allows the trap receiver to run forever without memory growth.