Output from MySQL

From /var/db/mysql/riffraff.company.com.err:

060619 17:54:25 [Note] /usr/local/libexec/mysqld: Normal shutdown

060619 17:54:25 InnoDB: Starting shutdown...
060619 17:54:26 InnoDB: Shutdown completed; log sequence number 0 43655
060619 17:54:26 [Note] /usr/local/libexec/mysqld: Shutdown complete

060619 17:54:26 mysqld ended

060619 17:55:54 mysqld started
060619 17:55:55 InnoDB: Started; log sequence number 0 43655
060619 17:55:55 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.9-beta' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysq
l-server-5.0.9_1

From mysqladmin version:

riffraff# /usr/local/bin/mysqladmin --password=somepass version
/usr/local/bin/mysqladmin Ver 8.41 Distrib 5.0.21, for portbld-freebsd6.0 on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.9-beta
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 3 days 1 hour 58 min 40 sec

Threads: 1 Questions: 41842 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 33 Queries per second avg: 0.157

Okay, so MySQL claims to have been up since the last reboot, or approximately a day after the sguil client was unable to connect.

So I have lots and lots of variables...

Variable_name Value
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 50
basedir /usr/local/
bdb_cache_size 8388600
bdb_home /var/db/mysql/
bdb_log_buffer_size 32768
bdb_logdir
bdb_max_lock 10000
bdb_shared_data OFF
bdb_tmpdir /var/tmp/
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/local/share/mysql/charsets/
collation_connection latin1_swedish_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
completion_type 0
concurrent_insert 1
connect_timeout 5
datadir /var/db/mysql/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
div_precision_increment 4
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
expire_logs_days 0
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
group_concat_max_len 1024
have_archive NO
have_bdb YES
have_blackhole_engine NO
have_compress YES
have_crypt YES
have_csv NO
have_example_engine NO
have_federated_engine NO
have_geometry YES
have_innodb YES
have_isam NO
have_ndbcluster NO
have_openssl NO
have_query_cache YES
have_raid NO
have_rtree_keys YES
have_symlink YES
init_connect
init_file
init_slave
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 8388608
innodb_checksums ON
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 1
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_support_xa ON
innodb_thread_concurrency 20
innodb_thread_sleep_delay 10000
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /usr/local/share/mysql/english/
large_files_support ON
large_pages OFF
large_page_size 0
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_bin OFF
log_bin_trust_routine_creators OFF
log_error
log_slave_updates OFF
log_slow_queries OFF
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 100
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads 20
max_join_size 4294967295
max_length_for_sort_data 1024
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 2147483647
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 8388608
engine_condition_pushdown OFF
net_buffer_length 16384
net_read_timeout 30
net_retry_count 1000000
net_write_timeout 60
new OFF
old_passwords OFF
open_files_limit 3549
optimizer_prune_level 1
optimizer_search_depth 62
pid_file /var/db/mysql/riffraff.milgard.com.pid
port 3306
preload_buffer_size 32768
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only OFF
read_rnd_buffer_size 262144
relay_log_purge ON
relay_log_space_limit 0
rpl_recovery_rank 0
secure_auth OFF
server_id 0
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_load_tmpdir /var/tmp/
slave_net_timeout 3600
slave_skip_errors OFF
slave_transaction_retries 10
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 2097144
sql_mode
storage_engine MyISAM
sql_notes ON
sql_warnings ON
sync_binlog 0
sync_replication 0
sync_replication_slave_id 0
sync_replication_timeout 10
sync_frm ON
system_time_zone PDT
table_cache 64
table_type MyISAM
thread_cache_size 0
thread_stack 196608
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 33554432
tmpdir
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.9-beta
version_bdb Sleepycat Software: Berkeley DB 4.1.24: (July 8, 2005)
version_comment FreeBSD port: mysql-server-5.0.9_1
version_compile_machine i386
version_compile_os portbld-freebsd6.0
wait_timeout 28800

Wait_timeout and interactive_timeout are set to 8 hours.

It just died a short while ago

pid(854) Sensor Data Rcvd: DiskReport /nsm/riffraff 3%
pid(854) Sending sock14: InsertSystemInfoMsg riffraff /nsm/riffraff 3%
pid(854) Sensor Data Rcvd: PING
pid(854) Client Command Received: SendClientSensorStatusInfo
pid(854) Sending sock14: SensorStatusUpdate {riffraff {1 {2006-06-22 00:20:02}
1 1 Unknown}}
pid(854) Client Command Received: PING
pid(854) Client Command Received: SendClientSensorStatusInfo
pid(854) Sending sock14: SensorStatusUpdate {riffraff {1 {2006-06-22 00:20:02}
1 1 Unknown}}
pid(854) Client Command Received: SendClientSensorStatusInfo
pid(854) Sending sock14: SensorStatusUpdate {riffraff {1 {2006-06-22 00:20:02}
1 1 Unknown}}
pid(854) Sensor Data Rcvd: SancpFile riffraff parsed.riffraff.stats.xl0.1150936
168.20060620 20060620 125
pid(855) loaderd: Received: LoadSancpFile riffraff /tmp/parsed.riffraff.stats.x
l0.1150936168.20060620 20060620
pid(855) loaderd: Creating sancp table: sancp_riffraff_20060620.
Error: mysqlexec/db server: MySQL server has gone away
mysqlexec/db server: MySQL server has gone away
while executing
"mysqlexec $LOADERD_DB_ID $createQuery"
(procedure "CreateNewSancpTable" line 9)
invoked from within
"CreateNewSancpTable $tableName"
(procedure "LoadSancpFile" line 8)
invoked from within
"LoadSancpFile [lindex $data 1] [lindex $data 2] [lindex $data 3] "
("LoadSancpFile" arm line 1)
invoked from within
"switch -exact -- $cmd {

LoadPSFile { LoadPSFile [lindex $data 1] [lindex $data 2
] }
LoadSsnFile { Lo..."
(procedure "SguildCmdRcvd" line 15)
invoked from within
"SguildCmdRcvd file5"
SGUILD: Exiting...
pid(854) sguild: Received from loaderd:
pid(854) Unknown command received from loaderd:
Lost communications with loaderd.
SGUILD: killing child procs...
SGUILD: Exiting...

output (so far) from sguild run in foreground

pid(854) Loading access list: ./sguild.access
pid(854) Sensor access list set to ALLOW ANY.
pid(854) Client access list set to ALLOW ANY.
pid(854) Email Configuration:
pid(854) Config file: ./sguild.email
pid(854) Enabled: No
pid(854) Connecting to localhost on 3306 as sguil
pid(854) MySQL Version: version 5.0.9-beta
pid(854) SguilDB Version: 0.11
pid(854) Creating event MERGE table.
pid(854) Creating tcphdr MERGE table.
pid(854) Creating udphdr MERGE table.
pid(854) Creating icmphdr MERGE table.
pid(854) Creating data MERGE table.
pid(856) Queryd Forked
pid(854) Retrieving DB info...
pid(854) SELECT hostname FROM sensor ORDER BY hostname ASC
pid(855) Loaderd Forked
pid(854) SELECT sid FROM sensor WHERE hostname='riffraff'
pid(854) SELECT ip FROM sensor WHERE hostname='riffraff'
pid(854) SELECT MAX(timestamp) FROM event WHERE sid=1
pid(854) Querying DB for archived events...
pid(854) SELECT event.status, event.priority, event.class, sensor.hostname,

event.timestamp, event.sid, event.cid, event.signature,
INET_NTOA(event.src_ip), INET_NTOA(event.dst_ip), event.ip_proto,
event.src_port, event.dst_port, event.signature_id, event.signature_rev,
event.unified_event_id, unified_event_ref
FROM event
FORCE INDEX (status)
JOIN sensor ON event.sid=sensor.sid
WHERE event.status=0 ORDER BY event.timestamp ASC
pid(854) Archived Alert: 0 2 misc-attack riffraff {2006-06-20 01:35:09} 1 23181
{MISC UPnP malformed advertisement} 192.168.0.1 239.255.255.250 17 2052 1900 13
84 8 1
(...)
{MISC UPnP malformed advertisement} 192.168.0.1 239.255.255.250 17 2052 1900 13
84 8 20 20
pid(854) Querying DB for escalated events...
pid(854) SELECT event.status, event.priority, event.class, sensor.hostname,

event.timestamp, event.sid, event.cid, event.signature,
INET_NTOA(event.src_ip), INET_NTOA(event.dst_ip), event.ip_proto,
event.src_port, event.dst_port
FROM event
FORCE INDEX (status)
JOIN sensor ON event.sid=sensor.sid
WHERE event.sid=sensor.sid AND event.status=2 ORDER BY event.timestamp ASC
pid(854) Retrieving DB info...
pid(854) Getting a list of tables.
pid(854) ...Getting info on data.
pid(854) ...Getting info on event.
pid(854) ...Getting info on history.
pid(854) ...Getting info on icmphdr.
pid(854) ...Getting info on nessus.
pid(854) ...Getting info on nessus_data.
pid(854) ...Getting info on portscan.
pid(854) ...Getting info on sancp.
pid(854) ...Getting info on sensor.
pid(854) ...Getting info on sessions.
pid(854) ...Getting info on status.
pid(854) ...Getting info on tcphdr.
pid(854) ...Getting info on udphdr.
pid(854) ...Getting info on user_info.
pid(854) ...Getting info on version.
pid(854) Sguild Initialized.
pid(854) Sensor agent connect from 127.0.0.1:53761 sock13
pid(854) Validating sensor access: 127.0.0.1 :
pid(854) Valid sensor agent: 127.0.0.1
pid(854) Sensor Data Rcvd: AgentInit riffraff 1
pid(854) No clients to send info msg to.
pid(854) Sent sock13: SensorID 1
pid(854) Sensor Data Rcvd: PONG
pid(854) Sensor Data Rcvd: BYEventRcvd sock5 0 1 23201 riffraff 21 21 {2006-06-20 01:49:41} 1 1384 8 {MISC
UPnP malformed advertisement} {2006-06-20 01:49:41} 2 misc-attack 3232235521 192.168.0.1 4026531834 239.255.
255.250 17 4 5 0 346 0 2 0 4 50415 {} {} {} {} {} 2052 1900 {} {} {} {} {} {} {} {} 326 7990 4E4F54494659202
A20485454502F312E310D0A484F53543A203233392E3235352E3235352E3235303A313930300D0A43414348452D434F4E54524F4C3A2
06D61782D6167653D313830300D0A4C4F434154494F4E3A20687474703A2F2F3139322E3136382E302E313A35323836392F676174656
46573632E786D6C0D0A4E543A2075706E703A726F6F746465766963650D0A4E54533A20737364703A616C6976650D0A5345525645523
A204C696E75782F322E342E31375F6D766C32312D6D616C74612D6D6970735F66705F6C652C2055506E502F312E302C20496E74656C2
053444B20666F722055506E502064657669636573202F312E320D0A55534E3A20757569643A31363061303230302D616339302D34623
0352D386164662D6635636363356135656261613A3A75706E703A726F6F746465766963650D0A0D0A
pid(854) Alert Received: 0 2 misc-attack riffraff {2006-06-20 01:49:41} 1 23201 {MISC UPnP malformed advert
isement} 192.168.0.1 239.255.255.250 17 2052 1900 1384 8 21 21
(...)
pid(854) Sending sock14: IncrEvent 1.25161 19 2
pid(854) Sent sock13: Confirm sock5 25179
pid(854) Sensor Data Rcvd: BYEventRcvd sock5 0 1 25180 riffraff 2000 2000 {2006-06-21 01:33:56} 1 1384 8 {M
ISC UPnP malformed advertisement} {2006-06-21 01:33:56} 2 misc-attack 3232235521 192.168.0.1 4026531834 239.
255.255.250 17 4 5 0 408 0 2 0 4 50353 {} {} {} {} {} 2052 1900 {} {} {} {} {} {} {} {} 388 21319 4E4F544946
59202A20485454502F312E310D0A484F53543A203233392E3235352E3235352E3235303A313930300D0A43414348452D434F4E54524F
4C3A206D61782D6167653D313830300D0A4C4F434154494F4E3A20687474703A2F2F3139322E3136382E302E313A35323836392F6761
7465646573632E786D6C0D0A4E543A2075726E3A736368656D61732D75706E702D6F72673A736572766963653A57414E4950436F6E6E
656374696F6E3A310D0A4E54533A20737364703A616C6976650D0A5345525645523A204C696E75782F322E342E31375F6D766C32312D
6D616C74612D6D6970735F66705F6C652C2055506E502F312E302C20496E74656C2053444B20666F722055506E502064657669636573
202F312E320D0A55534E3A20757569643A36303830303763322D636437612D343234322D383061322D6563326161623364333336393A
3A75726E3A736368656D61732D75706E702D6F72673A736572766963653A57414E4950436F6E6E656374696F6E3A310D0A0D0A
pid(854) Alert Received: 0 2 misc-attack riffraff {2006-06-21 01:33:56} 1 25180 {MISC UPnP malformed advert
isement} 192.168.0.1 239.255.255.250 17 2052 1900 1384 8 2000 2000
pid(854) Sending sock14: IncrEvent 1.25161 20 2
pid(854) Sent sock13: Confirm sock5 25180
pid(854) Client Command Received: SendClientSensorStatusInfo
pid(854) Sending sock14: SensorStatusUpdate {riffraff {1 {2006-06-21 01:33:56} 1 1 Unknown}}

Waiting is the hardest part

Per Richard Bejtlich, sent an email off to the list at sguil-users. There's been no response, but it's a Monday and it's June. I imagine most folks are on vacation or maybe don't have any idea what I've said.

So...I'm going to try sguil-0.6.0p1 and see what happens. Maybe it's just in the new version.

Other things I'm trying

Tried:

1.) creating /etc/my.conf, adding open_files_limit=4096 as the ONLY line.
2.) editing /usr/local/src/sguil-0.6.1/server/sguild.conf with the information found in Richard Bejtlich's sguild.conf.patch file.
3.) FLUSH PRIVILEGES one last time...

But it would be nicest to know what the issue really is if loaderd receives a bad instruction and dies.

Let's look at this

From the FAQ:

5.11 Sguild (loaderd) dies while trying to load SANCP data into the database.

Sguil uses the MySQL "LOAD DATA INFILE" syntax to load the SANCP data into the central database, and this requires special privileges above the normal INSERT/UPDATE privileges. You must also grant the FILE privilege, like so:

GRANT FILE ON *.* TO sguil@localhost;
FLUSH PRIVILEGES;

Notice that you have to grant the permission on every database and every table. FILE is a global privilege, and cannot be granted for just a single table or database.

But, according to riffraff:

+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for sguil@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sguil'@'localhost' IDENTIFIED BY PASSWORD '(some password)' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `sguildb`.* TO 'sguil'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------------------------------+

Does "ALL PRIVILEGES" NOT contain "FILE"? I'm assuming "ALL PRIVILEGES" would contain literally all privileges, but I'm having a really hard time finding out. Does it matter if the IDENTIFIED BY PASSWORD is listed? That is, is this dying due to an expectation of a passed variable for password?

Yesterday I regranted FILE on *.* to sguil@localhost. I flushed privileges, rebooted, and tried again. Same loaderd error occurred, so I tried GRANT ALL as Hanashi suggested in #snort-gui. I'm currently at a loss as to what to try next.

from /var/log/messages

Jun 13 20:03:59 riffraff snort[2656]: Log directory = /nsm/riffraff/
Jun 13 20:03:59 riffraff snort[2656]: Snort initialization completed successfull
y (pid=2656)
Jun 13 20:04:07 riffraff barnyard[2123]: Closing spool file '/nsm/riffraff//snor
t.log.1150157862'. Read 2180 records
Jun 13 20:04:07 riffraff barnyard[2123]: Opened spool file '/nsm/riffraff//snort
.log.1150254238'
Jun 13 20:04:07 riffraff barnyard[2123]: Waiting for new data
Jun 13 20:04:54 riffraff SGUILD: Client Connect: 10.153.8.201 49396 sock14
Jun 13 20:04:54 riffraff SGUILD: Validating client access: 10.153.8.201
Jun 13 20:04:54 riffraff SGUILD: Valid client access: 10.153.8.201
Jun 13 20:04:59 riffraff SGUILD: sock14 added to clientList
Jun 14 00:00:26 riffraff SGUILD: Socket sock14 closed
Jun 14 10:12:18 riffraff SGUILD: Creating event table event_riffraff_20060615.
Jun 14 10:12:18 riffraff SGUILD: Creating tcphdr table tcphdr_riffraff_20060615.
Jun 14 10:12:18 riffraff SGUILD: Creating udphdr table udphdr_riffraff_20060615.
Jun 14 10:12:18 riffraff SGUILD: Creating icmphdr table icmphdr_riffraff_2006061
5.
Jun 14 10:12:18 riffraff SGUILD: Creating data table data_riffraff_20060615.
Jun 14 10:12:18 riffraff SGUILD: Creating event MERGE table.
Jun 14 10:12:18 riffraff SGUILD: Creating tcphdr MERGE table.

Jun 14 10:12:18 riffraff SGUILD: Creating udphdr MERGE table.
Jun 14 10:12:18 riffraff SGUILD: Creating icmphdr MERGE table.
Jun 14 10:12:18 riffraff SGUILD: Creating data MERGE table.
Jun 14 11:54:19 riffraff SGUILD: loaderd: Creating sancp table: sancp_riffraff_2
0060614.
Jun 14 11:54:19 riffraff SGUILD: Unknown command received from loaderd:
Jun 14 11:54:19 riffraff SGUILD: Lost communications with loaderd.
Jun 14 12:08:33 riffraff SGUILD: DB Error during: INSERT INTO `event_riffraff_20
060615` (sid, cid, unified_event_id, unified_event_ref, unified_ref_time, sign
ature, signature_gen, signature_id, signature_rev, timestamp, priority, class,
status, src_ip, dst_ip, ip_proto, ip_ver, ip_hlen, ip_tos, ip_len, ip_id, ip_fl
ags, ip_off, ip_ttl, ip_csum, src_port, dst_port) VALUES ('1', '12841', '1321',
'1321', '2006-06-14 19:08:33', 'MISC UPnP malformed advertisement', '1', '1384
', '8', '2006-06-14 19:08:33', '2', 'misc-attack', '0', '3232235521', '402653
1834', '17', '4', '5', '0', '346', '0', '2', '0', '4', '50415', '205
2', '1900') : mysqlexec: handle already closed (dangling pointer)
Jun 14 12:08:33 riffraff SGUILD: ERROR: While inserting event info: mysqlexec: h
andle already closed (dangling pointer)
Jun 14 12:08:33 riffraff barnyard[2123]: FATAL ERROR: Expected Confirm 12841 and
got: Failed to insert 12841: mysqlexec: handle already closed (dangling pointer)
Jun 14 12:08:33 riffraff barnyard[2123]: Exiting

Privileges

Grants for root@localhost:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '(some password)' WITH GRANT OPTION

Grants for sguil@localhost:
GRANT FILE ON *.* TO 'sguil'@'localhost' IDENTIFIED BY PASSWORD '(some password)'
GRANT ALL PRIVILEGES ON `sguildb`.* TO 'sguil'@'localhost'

Grants for magenta@company.com:
GRANT FILE ON *.* TO 'magenta'@'company.com'
GRANT ALL PRIVILEGES ON `sguildb`.* TO 'magenta'@'company.com'

So what am I missing???

What just happened?

Last night: rebooted and restarted Riffraff after MySQL error.
Today: loaderd error. Again.

I've added all the permissions requested. I've added permissions for Riffraff, Magenta, and root, on localhost AND using fully qualified domain names. What could possibly be causing this to fail, if the permissions have been set more than once to be the ones listed in the script???

Richard Bejtlich still has my admiration and respect.

Without a viable, working Sguil box, though, I'm pretty hosed. The permissions are listed as the issue, but they have been granted. Twice.

Methinks it may be time to switch to the older versions of stuff. At least that worked...

results of: mysqladmin version

"/usr/local/bin/mysqladmin Ver 8.41 Distrib 5.0.21, for portbld-freebsd6.0 on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.9-beta
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 6 hours 39 min 23 sec

Threads: 3 Questions: 27529 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 27 Queries per second avg: 1.149"

Frustrating

On June 7th, before I left for vacation, I put Riffraff out on the network and left him running. Today, June 12th, I could not log in to Riffraff on the sguil client console.

So what went wrong? I logged into the box remotely and checked /var/log/messages. "Jun 9 16:30:45 riffraff SGUILD: ERROR: loaderd: mysqlexec/db server: MySQL server has gone away"? But how? I'm still looking into that.

Loaded sguil components: 06/07/06 20:16
Fatal error occured: 06/09/06 16:30

I'm supposed to have a stable, working test box in place for one of our locations by the end of the month. I doubt it will happen, with all these frustrating errors occurring.

Figured it out

I've been working hard the last few days to get Riffraff going again. I reinstalled FreeBSD, and typed in all the instructions from Richard Bejtlich's scripts by hand. While not terribly quick, it did save me a lot of the trouble I experienced earlier, for some reason.

The two major installation issues I encountered were in sguil_sensor_install.sh and sguil_server_install.sh. In the sensor install, for some reason after doing a pkg_add -r snort, I didn't have a /usr/local/etc/snort. That meant I couldn't chown -R it to sguil:sguil. It did appear later, which leads me to suspect that perhaps the first time, the package didn't add properly. No idea why, but I suspect either human error (typo) or hardware error (this thing's been acting a bit goofy, and it's physically wobbly unless dropped like an old-school desktop to its rubber feet). With the server install, there was much sqwaking about how mysqltcl wasn't installed, even though I'd just installed it. The solution ended up being to add the "LD_LIBRARY_PATH=/usr/local/lib/mysql; export LD_LIBRARY_PATH" line to /etc/rc.conf, since it didn't seem to be picking it up anywhere else, even though it was added to sguild_start.sh manually. While I hate making global overrides like that, it did seem to be the only thing that worked, considering my limited capacity in FreeBSD.

The other issues had me stuck for a bit, until I finally puzzled them out. The errors were: "riffraff snort[645]: FATAL ERROR: Unable to open rules file: nsm/rules/riffraff/classification.config or /usr/local/etc/nsm/nsm/rules/riffraff/classification.config" and "riffraff snort[652]: FATAL ERROR: Unable to open rules file: nsm/rules/riffraff/reference.config or /usr/local/etc/nsm/nsm/rules/riffraff/reference.config" In /nsm/rules/riffraff/snort.conf, the includes for classification.config and reference.config were set to ../share/snort/classification.config and ../share/snort/reference.config. I changed them to "include $RULE_PATH/classification.config" and "include $RULE_PATH/reference.config".

Seems to be working so far, but...