[syslog-ng] Anyone got a well performing search interface for syslog data?

[Bill Nash]

On Thu, 8 Sep 2005, Jason Haar wrote:

> I just want to thank everyone for their responses. Very interesting stuff!
>
> I think I can paraphrase that SQL-backends don't give much advantage
> with large data sets due to the lack of relationships within syslog
> data, and the "fastest" solutions are going to be those that basically
> have custom-written "hot searches" pre-defined so that the appropriate
> indexes/extra files are already created to speed things up.

You're correct in that syslog, by itself, doesn't offer any amount of 
relationships.. by itself. This is what log analyzers are for.

mysql> select syslogRule.appSet, count(*) from syslog left join syslogRule 
on (syslog.syslogRule = syslogRule.id) group by appSet;
+---------------------+----------+
| appSet              | count(*) |
+---------------------+----------+
| NULL                |      235 |
| Alteon              |     2316 |
| Cisco IOS           |     1552 |
| Cron                |        6 |
| Linux Kernel        |   214689 |
| Linux PAM           |    13465 |
| logrotate           |        6 |
| named               |   157584 |
| PIX Firewall        |  3868906 |
| proftpd             |      112 |
| Snare Syslog Daemon |    91115 |
| Snort               |     7559 |
| sshd                |      103 |
| syslog-ng           |        7 |
| tacacs              |       95 |
+---------------------+----------+

The top 'null' set are entries I don't have rules for.

mysql> select eventDefinition.name, count(*) from syslog left join 
syslogRule on (syslog.syslogRule = syslogRule.id) left join 
eventDefinition on (eventDefinition.id = syslogRule.eventId) group by 
eventDefinition.name;
+---------------------------------------+----------+
| name                                  | count(*) |
+---------------------------------------+----------+
| NULL                                  |  2933114 |
| ACL Violation                         |     1033 |
| Attack Detected                       |     4599 |
| Configuration Change                  |  1192107 |
| Device Shutdown                       |   214805 |
| Failed login attempt                  |      384 |
| Interface State Change                |      332 |
| Load balanced device failure          |     1152 |
| Load balanced device restored         |     1143 |
| Promiscuous Network Interface         |        2 |
| Software reported an error            |        9 |
| Unexpected software termination       |       72 |
| Use of super-user privileges detected |       36 |
| User Login                            |    13514 |
| User Logout                           |     2878 |
| VLAN State Change                     |        2 |
+---------------------------------------+----------+

There are very few (free) packages that offer panacea for syslog 
management. The problem with a lot of packages is that they simply aren't 
flexible enough to let you do what you want to do, and you still wind up 
modifying them, or worse, scrapping them for that reason.

I need to go find the 11,000 users who haven't logged out now. ;)

- billn