Logging SNMP traps to MySQL/MariaDB.

I recently got a request formulated approximately like this:
"How can our servicedesk count the number of devices a user has on our wireless network." 

The reason for the request is that we enforce a maximum of two devices per user. But whenever a user tries with a third, there is no useful feedback to the end user that the number of devices has been exceeded. Hence they will call servicedesk to request help getting their device on the network. Because they are not aware of the limit, because they have forgotten about one or another device in their backpack, or because someone is abusing the account in question. Or any other reason. Ideally, we would give users nice feedback. Practically, we don't.

Our provider of wireless network gear got tools to manage the wireless network, which may or may not assist with counting devices per user. But they are not extremely user friendly. And we'd rather not let more users have access to these tools than strictly required. Can I make a simple tool that assists superusers and the servicedesk in simple debugging of wireless access clients? 

Turns out I can.

The wireless controllers already emit a huge stream of SNMP traps to another network device which makes use of them to match usernames with IPaddresses. For... reasons. I can make use of the same messages. The following is an account of how I get the messages into a database, and how I can query the database to extract the information I want.


Before we start, you should be aware of a few important things with regards to SNMP:

• SNMP version 1 and 2c are not encrypted protocols. Easily eavesdropped upon, easily spoofed. 
• The traffic is UDP-based. An adversary can flood spoofed packets from anywhere. See your friendly firewall admin for advice.
• It is common to have separate SNMP communities for read and write access to an SNMP agent. Having no write community enabled until you really need it, it is a good idea. 
• The SNMP community is not a password. It is more like a username, for which there is no password.
• An SNMP agent is a process which can be queried (read) and possibly configured (write), as well as emit traps ('alerts') directed at an SNMP trap receiver. The agent typically runs on a piece of network equipment, like a router, switch, firewall or even a server. The SNMP agent is called snmpd in most Linux distributions.
• An SNMP trap receiver accepts traps with specific communities, transmitted from SNMP agents. The trap receiver typically runs on a server. The SNMP trap receiver is called snmptrapd in most Linux distributions.
• For proper security, you must employ SNMP version 3. This is not covered in this post.

This is greatly simplified. A link to suggested reading materials is provided at the end.

I got my friendly sysadmin to spin up a virtual Ubuntu machine for me. Then, I logged in and did:

user@ubuntu:~$ sudo apt-get install mariadb-server mariadb-client snmp snmpd snmp-mibs-downloader


You will be prompted for an admin user/password etc. for the database. Note that MySQL/MariaDB both use an internal user database, unrelated to the system userdatabase. Writing the admin user and password down and keeping it in a safe place is a good idea.

MariaDB is a drop-in replacement for MySQL. I choose MariaDB over MySQL, as MariaDB had support for an option not present in the version of MySQL available to me at the moment. More about this in later posts. The names of the binaries and the startup-scripts are the same for MySQL and MariaDB. Drop-in replacement, remember.

The command above adds mysql and snmpd to your default services, and starts them both. We don't need snmpd at the moment, so we will stop and disable it.

user@ubuntu:~$ sudo service snmpd stop
user@ubuntu:~$ sudo update-rc.d snmpd disable



Now, we need to create the database the traps will be stored in, as well as the database user we will use when logging traps.

user@ubuntu:~$ mysql -u root -p


When prompted, provide the password you gave during install of MariaDB. You will be greeted with this prompt:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 47
Server version: 5.5.44-MariaDB-1ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 


Create the database we will log traps to:

MariaDB [(none)]> create database net_snmp;
Query OK, 1 row affected (0.03 sec)

Keep the database name 'net_snmp' for the purpose of logging traps.
If you end up with a '     ->' prompt, you have forgotten the terminating semicolon in the command above. Always terminate an sql command with a ';'. You can add that terminating semicolon at the '     ->' prompt. After every successful command you give the database engine, you will get a line stating Query OK, xxx row affected (0.03 sec). This is normal, and I will not repeat this line below.



Create the database user we will use to log traps with, and give that user all rights to tables in that database:


MariaDB [(none)]> create user 'netsnmp'@'localhost' identified by 'sekritpass';
MariaDB [(none)]> grant all on net_snmp.* to 'netsnmp'@'localhost' identified by 'sekritpass';

 
Create the schema we will enter data into. 
A database schema is the actual structure we write data to, including the tables. Consider it the 'shape' and 'function' of the database. The file describing the database schema is, as far as I can tell, not installed with snmpd. But it is present in the net-snmp source code distribution. 
 I have reproduced it below. 

Copy and paste this at the MariaDB prompt:

USE net_snmp;
DROP TABLE IF EXISTS notifications;
CREATE TABLE IF NOT EXISTS `notifications` (
  `trap_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date_time` datetime NOT NULL,
  `host` varchar(255) NOT NULL,
  `auth` varchar(255) NOT NULL,
  `type` ENUM('get','getnext','response','set','trap','getbulk','inform','trap2','report') NOT NULL,
  `version` ENUM('v1','v2c', 'unsupported(v2u)','v3') NOT NULL,
  `request_id` int(11) unsigned NOT NULL,
  `snmpTrapOID` varchar(1024) NOT NULL,
  `transport` varchar(255) NOT NULL,
  `security_model` ENUM('snmpV1','snmpV2c','USM') NOT NULL,
  `v3msgid` int(11) unsigned,
  `v3security_level` ENUM('noAuthNoPriv','authNoPriv','authPriv'),
  `v3context_name` varchar(32),
  `v3context_engine` varchar(64),
  `v3security_name` varchar(32),
  `v3security_engine` varchar(64),
  PRIMARY KEY  (`trap_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS varbinds;
CREATE TABLE IF NOT EXISTS `varbinds` (
  `trap_id` int(11) unsigned NOT NULL default '0',
  `oid` varchar(1024) NOT NULL,
  `type` ENUM('boolean','integer','bit','octet','null','oid','ipaddress','counter','unsigned','timeticks','opaque','unused1','counter64','unused2') NOT NULL,
  `value` blob NOT NULL,
  KEY `trap_id` (`trap_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 
This states that we will use the net_snmp database, and creates the two tables our trap receiver expects to have access to. With the database schema in place, we are ready to configure the trap receiver. 


Drop out of the MariaDB shell by typing exit at the prompt. Then create the following three files and make sure they have the same content as shown below:

root@ubuntu:~# cat /etc/snmp/snmptrapd.conf 

authCommunity log mytrapcommunity

sqlMaxQueue 1

sqlSaveInterval 9

root@ubuntu:~# cat /etc/rc.local 

/usr/sbin/snmptrapd -c /etc/snmp/snmptrapd.conf

exit 0

root@ubuntu:~# cat /etc/mysql/conf.d/snmptrapd.cnf 

[snmptrapd]

user=netsnmp

password=sekritpass

host=localhost

 
The first file is the config file for the process receiving the snmp traps from your network device. The first line tells snmptrapd to log traps with the SNMP community mytrapcommunity.  The next two lines instructs the trap receiver to log to mysql, have at most 1 traps in the buffer before commiting to the database,  and commit to the database at least every 9 seconds. For production, you may want to increase the buffer to, say 20. Or a 100.

The second file is a dirty way of starting the SNMP trap receiver on boot. Any reader who wants to contribute a proper initfile for snmptrapd is welcome to do so.

The third file specifies the database user, password and hostname the trap receiver will use when logging to the database. You should recognise the user and password we created earlier.


And finally, we start snmptrapd:

user@ubuntu:~$ sudo sh /etc/rc.local



We should now be ready to receive SNMP traps and have them entered into the database. To send a test SNMP trap, execute the following command:

user@ubuntu:~$ sudo snmptrap -v 2c -c mytrapcommunity 127.0.0.1 "" 1.2.3.4.0


Log in to the database console again:

user@ubuntu:~$ mysql -u root -p 
MariaDB [(none)]> use net_snmp;
[.....]
Database changed
MariaDB [net_snmp]> select * from varbinds;
+---------+------------------------+-----------+----------------------------------+
| trap_id | oid                    | type      | value                            |
+---------+------------------------+-----------+----------------------------------+
|       1 | .1.3.6.1.2.1.1.3.0     | timeticks | Timeticks: (8422516) 23:23:45.16 |
|       1 | .1.3.6.1.6.3.1.1.4.1.0 | oid       | OID: .1.2.3.4.0                  |
+---------+------------------------+-----------+----------------------------------+
2 rows in set (0.00 sec) 

Congratulations! You have logged the first trap to the database. You will also find stuff in the table 'notifications'. You may now point your network devices at your SNMP trap receiver and store wast amounts of data in your database.

But as you may or may not know, having a huge amount of data does not automatically translate into information. I intend to write a part 2, where I give an example of how to make use of the trap database.

For further reading about SQL syntax, check out w3schools' SQL tutorial.. 
For further reading about SNMP, you could do worse than starting at the Net-SNMP wiki.