Inserting records into a DB with argus-client rasqlinsert
This post will cover some of the technicalities of rasqlinsert() so that you can insert records into an SQL DB. I’ll also cover working on structuing a basic database to store some records. This post will serve as a streamlined summary (while making some sense independently), and be much less verbose than the argus database intro page, and this thread about mode (-M) parameters and general functioning of rasqlinsert and this thread about more aggregation functions.
After we populate a DB for about a day, we will get an idea of the size/count of our flows as well as have some real data to work with.
If you’re impatient, you can always use some pcaps from a large variety of places to populate your DB.
Which fields do we want?
There are many fields (called DSRs, data set records, in argus docs) available for flows that are exposed by argus. All of these fields are listed starting on page 3 of the ra() man page. Additionally, keep in mind that argus will happily process netflow data.
I’ll choose a few that interest me, and perform a simple query using the ra command to see how the SQL record entries will look:
ra -b -S localhost:561 -s stime dur ﬂgs proto saddr sport dir daddr dport spkts dpkts sbytes dbytes state
Looks pretty good and gives us information we wish to record.
Building the database structure
Let’s start by rolling up a quick database. We’ll worry about planning for production later (such as planning for truncation and rolling the tables).
Install and configure mysql
yum -y install mysql mysql-server service mysqld start /usr/bin/mysql_secure_installation #enter for no root password #Y to set root password #enter a password #Y to remove anonymous user #Y to disallow root login remotely #Y to remove test database and access to it #Y to reload privilege tables now #done
Create the database and the dedicated argus SQL user:
echo "create database argus;" > ~/argus_db.sql echo "create user 'argus'@'localhost' identified by 'argus_DBUSERPASSWORD';" >> ~/argus_db.sql echo "grant all privileges on argus.* to 'argus'@'localhost';" >> ~/argus_db.sql mysql -p < ~/argus_db.sql
You may also want to create a remote administrative user, and use some remote GUI tools.
Create and populate the DB:
You can populate the DB with all the argus flows using the following:
rasqlinsert -S localhost:561 -s stime dur flgs proto saddr sport dir daddr dport spkts dpkts sbytes dbytes state -w mysql://argus:argus_DBUSERPASSWORD@localhost/argus/argusTable -m none -d
`-m none` stops rasqlinsert from truncating the table, and re-populating it with a sequence of N flow count.
let it populate for a day or so.
rasqlinsert() will insert a column called ‘record’ that contains binary data that is the entire argus record. This is very cool, in that you lose no data upon insert, and can cause you to adjust your front end and database design.
rasql() can take the binary data that’s stored in the ‘record’ column and translate it back to useful data.
It’s quite clear that having the ability to pull other data out of the full record at a later time might prove to be useful. So, I suggest storing both your desired fields and the binary ‘record,’ as do the developers of argus (since you need to explicitly note that you do not want to store the binary ‘record’).
The amount of storage you want to dedicate will dictate your retention design. Here is a basic scheme (give age of a record) I will test and adjust for growth following by running a maintenance script daily:
0-15 days = full data fields + the binary ‘record’
15-30 days = full data fields + the binary ‘record’ stored compressed
30-90 days = full data fields stored compress + the binary ‘record’ stored compressed
90-365 days = full data fields stored compress
365 days = truncate or archive to slow second tier storage/server