Dealing With Sguil Partition Issue

I operate several Sguil sensors in production environments for clients. At one location I have a single box deployment where the Sguil sensor, server, and database occupy a single FreeBSD platform. This wasn't the original configuration, but I am making do with what I was given.

Here is the current df -h output.

# df -h
Filesystem       Size    Used   Avail Capacity  Mounted on
/dev/aacd0s2a    989M     76M    834M     8%    /
devfs            1.0K    1.0K      0B   100%    /dev
/dev/aacd0s2f    989M    106K    910M     0%    /home
/dev/aacd0s2h    436G    363G     38G    90%    /nsm
/dev/aacd0s2e    989M    562M    348M    62%    /tmp
/dev/aacd0s2d    5.9G    986M    4.4G    18%    /usr
/dev/aacd0s2g    4.8G    3.8G    639M    86%    /var

As you can see, /var is approaching the 90% mark. /nsm is already there, but Sguil's log_packets.sh script rotates the full content files stored there. Notice I keep all /nsm data in its own partition, to avoid catastrophe if a runaway program were to begin filling the drive. I've found myself on many Red Hat boxes that have only a / (root) and swap partition, which is an invitation to disaster.

Because everything runs on this system, it stores the sguildb MySQL database in the /var partition. You'll see /var is at 86%. What's taking up all the space?

/var/db/mysql# du -ch .
214K    ./mysql
2.0K    ./test
3.7G    ./sguildb
3.8G    .
3.8G    total

Let's see the 20 biggest files in the sguildb directory.

/var/db/mysql/sguildb# ls -alhS | head -n 20
total 3929694
-rw-rw----  1 mysql  wheel    77M Apr  4 19:17 sancp_scn-sensor-colo_20060324.MYI
-rw-rw----  1 mysql  wheel    69M Apr  4 19:17 sancp_scn-sensor-colo_20060323.MYI
-rw-rw----  1 mysql  wheel    63M Apr  4 19:17 sancp_scn-sensor-colo_20060330.MYI
-rw-rw----  1 mysql  wheel    62M Mar  9 14:00 data_scn-sensor-colo_20060309.MYD
-rw-rw----  1 mysql  wheel    61M Apr 16 22:59 sancp_scn-sensor-colo_20060322.MYI
-rw-rw----  1 mysql  wheel    59M Apr  4 19:17 sancp_scn-sensor-colo_20060331.MYI
-rw-rw----  1 mysql  wheel    59M Mar 22 19:00 sancp_scn-sensor-colo_20060314.MYI
-rw-rw----  1 mysql  wheel    58M Apr 16 19:57 sancp_scn-sensor-colo_20060404.MYI
-rw-rw----  1 mysql  wheel    58M Mar 12 07:02 data_scn-sensor-colo_20060310.MYD
-rw-rw----  1 mysql  wheel    57M Mar 22 19:00 sancp_scn-sensor-colo_20060309.MYI
-rw-rw----  1 mysql  wheel    57M Apr 18 18:53 sancp_scn-sensor-colo_20060412.MYI
-rw-rw----  1 mysql  wheel    57M Apr 18 18:53 sancp_scn-sensor-colo_20060410.MYI
-rw-rw----  1 mysql  wheel    57M Apr  4 19:17 sancp_scn-sensor-colo_20060403.MYI
-rw-rw----  1 mysql  wheel    56M Mar 22 19:00 sancp_scn-sensor-colo_20060317.MYI
-rw-rw----  1 mysql  wheel    56M Apr  4 19:17 sancp_scn-sensor-colo_20060328.MYI
-rw-rw----  1 mysql  wheel    56M Apr 18 18:53 sancp_scn-sensor-colo_20060411.MYI
-rw-rw----  1 mysql  wheel    55M Apr  4 19:17 sancp_scn-sensor-colo_20060329.MYI
-rw-rw----  1 mysql  wheel    55M Apr  4 19:17 sancp_scn-sensor-colo_20060327.MYI
-rw-rw----  1 mysql  wheel    55M Apr 18 18:53 sancp_scn-sensor-colo_20060407.MYI

The sancp tables are session data.

# mysql -u sguil -p sguildb -e "describe sancp"
Enter password: 
+------------+----------------------+------+-----+---------+-------+
| Field      | Type                 | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| sid        | int(10) unsigned     | NO   | MUL |         |       |
| sancpid    | bigint(20) unsigned  | NO   |     |         |       |
| start_time | datetime             | NO   | MUL |         |       |
| end_time   | datetime             | NO   |     |         |       |
| duration   | int(10) unsigned     | NO   |     |         |       |
| ip_proto   | tinyint(3) unsigned  | NO   |     |         |       |
| src_ip     | int(10) unsigned     | YES  | MUL | NULL    |       |
| src_port   | smallint(5) unsigned | YES  | MUL | NULL    |       |
| dst_ip     | int(10) unsigned     | YES  | MUL | NULL    |       |
| dst_port   | smallint(5) unsigned | YES  | MUL | NULL    |       |
| src_pkts   | int(10) unsigned     | NO   |     |         |       |
| src_bytes  | int(10) unsigned     | NO   |     |         |       |
| dst_pkts   | int(10) unsigned     | NO   |     |         |       |
| dst_bytes  | int(10) unsigned     | NO   |     |         |       |
| src_flags  | tinyint(3) unsigned  | NO   |     |         |       |
| dst_flags  | tinyint(3) unsigned  | NO   |     |         |       |
+------------+----------------------+------+-----+---------+-------+

The data tables contain packet payloads.

# mysql -u sguil -p sguildb -e "describe data"
Enter password: 
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| sid          | int(10) unsigned | NO   | MUL |         |       |
| cid          | int(10) unsigned | NO   |     |         |       |
| data_payload | text             | YES  |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+

At this point it looks like I should back up some of the session data elsewhere, as it is taking up the most space.

First I make sure sensor_agent.tcl, sguild, and Barnyard are not running. I leave Snort and SANCP running, so the sensor continues to collect alert data, session data, and full content data.

Next I shut down MySQL.

# /usr/local/etc/rc.d/mysql-server.sh stop
Stopping mysql.
Waiting for PIDS: 76074, 76074.

I create a directory in the partition with the most free space that isn't going to be used any time soon.

# mkdir /usr/db_backups

Now I see how much space the SANCP tables occupy.

/var/db/mysql/sguildb# du -ch *sancp*
...edited...
3.3G    total

Yes, they are the culprit. I have files reaching back to 8 March. Let's see how much space I can reclaim if I move all of March's entries.

/var/db/mysql/sguildb# du -ch *sancp*200603*
...edited...
1.9G    total

That will work fine. Now to tar up the files elsewhere.

/var/db/mysql/sguildb# tar -czvf /usr/db_backups/sancp_200603.tar.gz *sancp*200603*
a sancp_scn-sensor-colo_20060308.MYD
a sancp_scn-sensor-colo_20060308.MYI
a sancp_scn-sensor-colo_20060308.frm
...edited...
a sancp_scn-sensor-colo_20060331.MYD
a sancp_scn-sensor-colo_20060331.MYI
a sancp_scn-sensor-colo_20060331.frm

/var/db/mysql/sguildb# ls -alh /usr/db_backups/
total 434788
drwxr-xr-x   2 root  wheel   512B Apr 18 19:16 .
drwxr-xr-x  20 root  wheel   512B Apr 18 19:11 ..
-rw-r--r--   1 root  wheel   424M Apr 18 19:20 sancp_200603.tar.gz

Now to delete the files, along with the sancp.MRG and sancp.frm files. They will be rebuilt automatically.

/var/db/mysql/sguildb# rm *sancp*200603*
/var/db/mysql/sguildb# rm sancp.MRG
/var/db/mysql/sguildb# rm sancp.frm

# df -h
Filesystem       Size    Used   Avail Capacity  Mounted on
/dev/aacd0s2a    989M     76M    834M     8%    /
devfs            1.0K    1.0K      0B   100%    /dev
/dev/aacd0s2f    989M    106K    910M     0%    /home
/dev/aacd0s2h    436G    363G     38G    91%    /nsm
/dev/aacd0s2e    989M    562M    348M    62%    /tmp
/dev/aacd0s2d    5.9G    1.4G    4.0G    26%    /usr
/dev/aacd0s2g    4.8G    1.9G    2.5G    43%    /var

Great -- /var has plenty of room to grow now. However, I don't have ready access to session data from March. If I need it, though, I can restore it.

Now restart MySQL, then the Sguil components.

# /usr/local/etc/rc.d/mysql-server start 
Starting mysql.
# su - sguil
sguil@scn-sensor-colo$ ./sguild_start.sh
pid(77008)  Loading access list: ./sguild.access
pid(77008)  Sensor access list set to ALLOW ANY.
pid(77008)  Client access list set to ALLOW ANY.
pid(77008)  Email Configuration:
pid(77008)    Config file: ./sguild.email
pid(77008)    Enabled: No
sguil@scn-sensor-colo$ ./sensor_agent_start.sh 
sguil@scn-sensor-colo$ ./barnyard_start.sh 
Barnyard Version 0.2.0 (Build 32)

A quick log in to Sguil shows that everything is working.