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