You can identify the agent - you just need to dig into the schema a
bit. The schema is pretty simple. There are like 4 tables that we
really care about:
Everything starts with the location table. The location table
identifies both the host that generated the alert, and the source of
the alert on the host. Here's what the contents of the table look
like:
mysql> select * from location;
+----+-----------+------------------------------------------+
| id | server_id | name |
+----+-----------+------------------------------------------+
| 1 | 1 | linuxlab1->ossec-monitord |
| 2 | 1 | (linuxlab2) 10.60.6.148->/var/log/secure |
| 3 | 1 | (linuxlab2) 10.60.6.148->ossec |
| 4 | 1 | (windowslab1) any->WinEvtLog |
| 5 | 1 | linuxlab1->/var/log/secure |
| 6 | 1 | (linuxlab2) any->ossec |
| 7 | 1 | (linuxlab2) any->/var/log/secure |
| 8 | 1 | (linuxlab2) any->syscheck |
+----+-----------+------------------------------------------+
As you can see they combine the hostname (which in production will be
the hosting account ID) and the alert source in the name field. So if
you were going to output all of the file integrity alerts associated
with a given host you'd start here like so:
mysql> select * from location where name='(linuxlab2) any->syscheck';
+----+-----------+---------------------------+
| id | server_id | name |
+----+-----------+---------------------------+
| 8 | 1 | (linuxlab2) any->syscheck |
+----+-----------+---------------------------+
>From there you can use the location.id field to get the list of alerts
associated with that location like so:
mysql> select * from alert where location_id=8;
+----+-----------+---------+------------+-------------+--------+--------+----------+----------+-------------------+
| id | server_id | rule_id | timestamp | location_id | src_ip |
dst_ip | src_port | dst_port | alertid |
+----+-----------+---------+------------+-------------+--------+--------+----------+----------+-------------------+
| 32 | 1 | 550 | 1336413257 | 8 | 0 |
0 | 23744 | 54 | 1336413254.443098 |
+----+-----------+---------+------------+-------------+--------+--------+----------+----------+-------------------+
Then from there you can use the alert.id field to get the extended
text of the alert (formatted vertically for easier reading):
mysql> select * from data where id=32 \G
*************************** 1. row ***************************
id: 32
server_id: 1
user: (null)
full_log: Integrity checksum changed for:
`/var/ossec/etc/ossec.conf`Size changed from `2061` to `2059`Old
md5sum was: `f0c1c29412190c754982e838d9edab62`New md5sum is :
`6bfb948ba8fb4d4e76e9d9c1bca5b496`Old sha1sum was:
`052860041c47976cddca732eb63fbca306d2537c`New sha1sum is :
`d24a4176708876b18e72639df767ff85fde7bd56`
timestamp: 2012-05-07 13:54:17
...and that's really all there is to it.
On Wed, May 16, 2012 at 6:36 AM, dan (ddp) <[email protected]> wrote:
> That table isn't populated yet. There's been some interest in
> finishing that part of the code, but as far as I know no work has been
> done.
>
> On Wed, May 16, 2012 at 3:28 AM, mikes <[email protected]> wrote:
>> Hi.
>>
>> Hmmm, why table 'agent' is empty in MySQL?
>>
>> I've got 150+ hosts and i can't search events by host :/
>>
>> best regards
