Hello again, today I'm going to do some
troubleshooting with OSSEC and MySQL. OSSEC
is very powerful host-based intrusion detection system, which can
help you monitor in real time attacks that target your
infrastructure. Main features are monitoring local and remote logs,
rootkit detection, file integrity checking, it can be configured
even for active response. More about OSSEC features can be found here.
The OSSEC system, by default, put all of
its logs in a plain text inside /var/ossec/logs/. However there is an
option all of the alerts and logs activity to be put inside a
database. The two options are – PostgreSQL and MySQL. The biggest
advantage of using a database is to monitor in real time attacks
against your servers using some graphic tool. There are a few web-based
tools, that I found, which can be connected to MySQL server, used by OSSEC. The first one is OSSEC Web UI (OSWUI) which can
be downloaded from here.
The second one, that I found, is Splunk
add-on and it can be downloaded from here.
The last web-based GUI, which I chose was Analogi.
The problem – connecting OSSEC with MySQL
The installation went very well, I just
followed the installation instructions. I added new repository for
OSSEC in CentOS, it appears I need EPEL repository too, because of
missing dependencies. I installed OSSEC and MySQL through yum and it
reports that the MySQL was installed from ossec repository
(atomic), that I added earlier. The MySQL version is 5.5.30 and OSSEC
version is 2.7.
I followed the instructions for
connecting and configuring OSSEC with MySQL from the OSSEC
documentation
and everything seemed to be ok. Just two things to mention – if the
mysql server is on the localhost, inside /var/ossec/etc/ossec.conf in
the <hostname> tags put 127.0.0.1, I tried with 'localhost',
but it gave me error inside the /var/ossec/logs/ossec.log. The other
thing to watch for is the mysql.schema file. On RedHat/CentOS systems
the file could be found under /var/ossec/etc/mysql/ there is no need
for downloading the source.
After I installed everything I started
watching the ossec.log file for errors. I connected several times
with a wrong username/password through ssh to generate some alerts
and then in the log appear the following error message:
ERROR: Error executing query 'INSERT
INTO server(last_contact, version, hostname, information) VALUES
('1362937495', 'v2.7', 'monitor.example.com', 'Linux
monitor.example.com 2.6.32-279.22.1.el6.x86_64 #1 SMP Wed Feb 6
03:10:46 UTC 2013 x86_64 - OSSEC HIDS v2.7')'. Error: 'Lock wait
timeout exceeded; try restarting transaction'.
After digging in the database I found
this lock:
TRANSACTIONS
------------
Trx id counter E402
Purge done for trx's n:o < 9B28 undo
n:o < 0
History list length 303
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 5, OS thread handle
0x7f28f821f700, query id 8354 localhost root
SHOW ENGINE INNODB Status
---TRANSACTION E401, ACTIVE 22 sec
inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size
376, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle
0x7f28f8260700, query id 8352 localhost 127.0.0.1 ossecuser update
INSERT INTO server(last_contact,
version, hostname, information) VALUES ('1362991366', 'v2.7',
'monitor.example.com', 'Linux monitor.example.com
2.6.32-279.22.1.el6.x86_64 #1 SMP Wed Feb 6 03:10:46 UTC 2013 x86_64
- OSSEC HIDS v2.7')
Trx read view will not see trx with id
>= E402, sees < E400
------- TRX HAS BEEN WAITING 22 SEC FOR
THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 597 n
bits 72 index `hostname` of table `ossec`.`server` trx id E401 lock
mode S waiting
Record lock, heap no 2 PHYSICAL RECORD:
n_fields 2; compact format; info bits 0
0: len 27; hex
6d7973716c2d73727630312e7365637572657368616b652e636f6d; asc
monitor.example.com;;
1: len 2; hex 0001; asc ;;
------------------
---TRANSACTION E400, ACTIVE 972 sec
8 lock struct(s), heap size 1248, 1 row
lock(s), undo log entries 3146
MySQL thread id 2, OS thread handle
0x7f28f82a1700, query id 8349 localhost 127.0.0.1 ossecuser
Trx read view will not see trx with id
>= E401, sees < E401
My concerns confirmed after watching
the process list inside mysql console. After version 5.5 MySQL
default storage engine is InnoDB, before it was MyISAM. I thought
that inside the schema file the storage engine would be marked, but
unfortunately there were only table definition. Next I decided to
convert the engine to MyISAM for all the tables used by OSSEC for
reporting. First connect to mysql with root credentials:
# mysql -h localhost -u root -p
Once connected, enter ossec database
and change the storage engine:
mysql > USE ossec;
mysql > SHOW TABLES;
mysql > ALTER TABLE agent
ENGINE=MYISAM;
Repeat the last command for all tables
inside ossec database. The result can be seen with:
mysql > SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'ossec' AND ENGINE =
'MYISAM';
After confirmation that all of the
tables use MyISAM engine restart the ossec server. Check again the
ossec.log and you should have no issues regarding database
connection.
If you read this before installation of
OSSEC, It will be better to change the mysql.schema file. Just put
the default storage engine into table definitions before applying the
schema and they will be created with the correct configuration. The
definition should look like:
CREATE TABLE category
(
cat_id SMALLINT UNSIGNED
NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(32) NOT NULL
UNIQUE,
PRIMARY KEY (cat_id),
INDEX (cat_name)
) ENGINE = MYISAM;
This should be added to all table
definitions.
I hope someone will find this useful. I want to share a
few additional resources about setting OSSEC and Analogi on CentOS:
And, of course if you consider OSSEC
deployment in your infrastructure I highly recommend this book.
