- syslog-ng - lists.balabit.hu

[RFC]: afsql improvement plans
by Gergely Nagy 03 Apr '11

03 Apr '11

Hi! I'm about to blow up^W^Wrefactor the afsql destination to increase both performance and functionality. To do this properly, and to satisfy most needs, I'd like to ask the greater community (especially those of you who have more experience with logging to SQL than I do!) about the plans laid out on the paragraphs below. Those of you who do log into SQL one way or the other, but aren't interested in technical mumbo-jumbo, please skip to the end of this e-mail, I'd have a few questions, for which all responses would be most appreciated! Where, when, how?? ================== There are multiple goals to accomplish, which I believe are not exclusive: we want to keep the sql() statement in the configuration file, and maintain backwards compatibility aswell. On the other hand, we want to improve performance too, and that is best accomplished by using the native database libraries. To this end, I propose we introduce new database types: "mysql-native", "postgresql-native", "sqlite-native", and so on and so forth. The first step will be to rearrange the current afsql driver, so that it has a generic configuration interface, but can have multiple backends (dbi - the only existing backend right now; and one for each native db client). I'd change the AFSqlDestDriver structure to only contain the configurable data and nothing else. The backends would subclass this type (much like how AFSqlDestDriver subclasses LogDriver) and add their own. The reason I want to do this, making the AFSqlDestDriver a config-only interface instead of keeping larger chunks of code there (for example, the threading code) is because I do not want to limit my possibilities: I can imagine an SQL driver utilizing multiple writer threads easily. However, neither model works for all databases (concurrent writes to SQLite are usually not a good idea, for example). And that's about the generic interface! Lets dive into the more interesting parts: the native drivers. Since MySQL is the most used server out there, that will be the first I tackle. My goal with that is to implement a driver that is not only faster than our current destinations, but one that is also faster than the perl scripts people have been using until now, when they wanted top performance. All that while maintaining reasonable flexibility. An ambitious plan, but hopefully not impossible. I've been reading the MySQL documentation for the past few hours to explore my options, and found a few ways to increase our insert speed: mysql_list_tables() ------------------- Instead of doing a full select to verify that a table exists, we can just try to list the table. This is probably not a significant increase, but every bit counts. Prepared statements ------------------- We'll use prepared statements wherever possible - I expect this alone will result in a measurable performance increase, since the server will not have to parse each and every insert - only once, we'll bind variables thereafter. LOCK TABLES / transactions -------------------------- Currently, syslog-ng can do bulk-inserts, wrapped in a transaction. But that only works if the MySQL table we insert to is on a storage engine that supports transactions (InnoDB). However, MyISAM has been the default for quite a while, and that does not support transactions to the best of my knowledge. In this case, insert performance can be increased by locking the tables for a few hundred inserts at a time, and unlocking after: poor man's transactions, to some extent. This, however, is more of a workaround than a real improvement. Insertion plans =============== There's a couple of alternative ways to improve insert speed by changing how we actually do them. All have their pros and cons, which I will try to enumerate below. LOAD DATA INFILE ---------------- The fastest way by far is LOAD DATA INFILE, which is what people use in the various perl scripts, as far as I remember. The downside is that - as far as I saw so far - this does need a temporary file, which has its downsides, like writing to disk (unless put on tmpfs or similar). An option which I haven't tried would be to use a pipe(), so that the data never hits the disk. But nevertheless, the mysql-native db type will have an option to use LOAD DATA INFILE. * Pros: + The fastest way to insert * Cons: - Needs a temporary file, with all its drawbacks, some of which can be worked around. - Serialisation is costlier than simple INSERTs. - No fine-grained error checking: if something goes wrong, the whole LOAD will have to be dropped (unless we add code that splits bulks up into smaller chunks, and retries - but that's something I want to avoid if possible) For LOAD DATA to work, we will have to format the data to insert into a suitable format, and if there's an error in the serialisation somewhere, we'll have a hard time to figure out where the problem is, and syslog-ng will just give up. Also, beating our data into a suitable format isn't exactly cheap, either (think escaping: processing large amounts of string data, possibly changing strings - there's both processing and memory ramifications involved). The need for a file is also a (minor) issue. I suppose named pipes would work, that way data never hits the disk between syslog-ng and mysql. However, that still involves copying data around. We'll see if these affect LOAD DATA's performance badly enough (they probably don't). INSERT INTO ... VALUES (...), (...), ... ---------------------------------------- This is considerably slower than LOAD DATA, but still faster than using INSERTs (even if we use transactions / locking for those). The downside is that - like with LOAD DATA - serialisation is still costlier than INSERTs. * Pros: + Reasonably fast + Does not require neither a temporary file, nor gross hacks * Cons: - Slower than LOAD DATA - Serialisation can be costly. - No fine-grained error checking Serialisation is a little less costly than in LOAD DATA's case, but this can be worked around a little with prepared statements (with a fallback to parsed ones, in case we have to insert less data than originally planned). The same issues with error handling persist, though. INSERT ... DELAYED ------------------ The DELAYED strategy works best if we have multiple writer threads, as in this case, INSERT becomes a fire & forget call, and we can return to our business far sooner. This does not work all that well with bulk inserts, though. * Pros: + Reasonably fast with multiple writers + Simple & fast serialisation * Cons: - No error checking at all (except for connection and syntax errors) - Probably still slower than LOAD DATA The advantage of this is that we can get the data from syslog-ng to the mysql server faster than any of the other methods, and then the work is off-loaded to the server. This has the potential of being faster than any of the above, but with the cost of hardly any possibilities to recover from an error, as we get no feedback after inserting: did it succeed? did it not? We'll have no idea. The Plan ======== The plan is to implement all the strategies above, and measure them against each other and external scripts. This will take a little time, because I want to get the architecture right, and figure out whether we want a single or multiple writer threads (per-destination, of course), or if I want to make that a configurable option aswell. Once done, and people are happy with it, I'll move on to the next database (PostgreSQL, to scratch my own itch too). The Questions ============= What I'd like to know most, is, that would YOU expect from a native mysql destination? Apart from speed, what would you want from it, that the current driver does not support, but which is possible via external scripts? If you happened to read through my wall of text above, did I miss anything important? Cheers, -- |8]

4 10

More on httpry_logger
by Martin Holste 03 Apr '11

03 Apr '11

I Just thought I'd mention to the list that I posted a write-up on analyzing the effects of the lizamoon SQL injection campaign by looking at logs from httpry_logger through ELSA (both available at http://code.google.com/p/enterprise-log-search-and-archive/) on my blog at ossectools.blogspot.com. httpry_logger can be used stand-alone with whatever log system you're using now. In addition to syslog, it outputs to a number of other formats including basic DBI handle, MongoDB, and flat file. --Martin

1 0

Proper configuration for Syslog-ng PE v4.0 disk-based buffering feature is required...
by Prashant Badhe 02 Apr '11

02 Apr '11

Hi, I am evaluating Syslog-ng PE v4.0 disk-based buffering feature on Linux, but it doesn’t seems to be working. Below is the configuration I added to my sysylog-ng.conf file: destination d_syslog { tcp("myserver" port(1468) log_disk_fifo_size(102400) ); file("/var/log/messages"); }; When the connection to the server is up then I receive the forwarded syslogs properly. When the network connection goes down, syslog-ng stores these messages at location /opt/syslog-ng/var, (.qf files). But when the network connection reestablishes, these stored messages never sent to the server. Can anybody please guide me if there is some missing configuration or wrong configuration I have done to make this feature work? Thanks in advance, Prashant Badhe

2 1

Blog post on ELSA
by Martin Holste 01 Apr '11

01 Apr '11

I just put up an entry on my blog (http://ossectools.blogspot.com) describing the Enterprise Log Search and Archive Project (http://code.google.com/p/enterprise-log-search-and-archive) I've been working on which uses Syslog-NG >= 3.1 and pattern-db at its core. There are a lot of other open-source log collection frameworks out there that are easier to install, such as Logzilla (php-syslog-ng), but if you're trying to log > 1k messages/sec (common in large orgs) and need something GPL licensed, installing ELSA will probably be worth your while. We're using it to index 15k messages/sec with basic hardware. It's currently storing tens of billions logs, and full-text, ad-hoc queries complete in about 1/2 to 2 seconds, including group-by queries on arbitrary fields for reporting. I put a few screenshots and a feature list in the post. The documentation is pretty basic right now, but I'm happy to assist if you run into issues. ELSA is also open to plugin creation, so if you find ELSA useful and create plugins, please let me know and I can add them to the project. Also, patterns for the pattern-db are more than welcome! I've included patterns for Cisco FWSM connections and denies, Snort logs, Windows logs from Eventlog-to-Syslog as well as Snare, and URL's from my httpry wrapper, which is available on the project site as well as in the tarball/source code. Comments and feedback are welcome! Thanks, Martin

6 8

how to push sulogs/utmpx/wtmpx to central logserver
by Tony MacDoodle 31 Mar '11

31 Mar '11

Hello All, I would like to push only the following log files to a central syslog-ng server. All clients are Solaris 10 and the syslog-ng server is also Solaris 10. IS it possible to push only this information? wtmpx utmpx sulog Thanks

4 3

Make Errors on Solaris 10
by Tony MacDoodle 31 Mar '11

31 Mar '11

Hello, I am trying to compile syslog-ng 3.2.2 for Solaris 10 Sparc and getting the following errors when I run a make: ./configure --enable-tcp-wrapper syslog-ng Open Source Edition 3.2.2 configured Compiler options: compiler : gcc -std=gnu99 compiler options : -g -O2 -Wall -D_REENTRANT -D_PTHREADS -I/usr/local/include/glib-2.0 -I/usr/local/lib/glib-2.0/include -I/usr/local/include/eventlog -D_GNU_SOURCE -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 linker flags : -lpthread prefix : /usr/local linking mode : dynamic Features: Sun STREAMS support (module): yes Debug symbols : no GCC profiling : no Memtrace : no IPV6 support : yes spoof-source support : no tcp-wrapper support : yes SSL support (module) : no SQL support (module) : no Linux capability support : no PCRE support : no Env wrapper support : no PACCT module (EXPERIMENTAL) : no root:~/syslog/syslog-ng-3.2.2#* make* make all-recursive Making all in lib make: Fatal error in reader: Makefile, line 637: Unexpected end of line seen Current working directory /root/syslog/syslog-ng-3.2.2/lib *** Error code 1 The following command caused the error: fail= failcom='exit 1'; \ for f in x $MAKEFLAGS; do \ case $f in \ *=* | --[!k]*);; \ *k*) failcom='fail=yes';; \ esac; \ done; \ dot_seen=no; \ target=`echo all-recursive | sed s/-recursive//`; \ list='lib modules syslog-ng scripts tests doc contrib scl debian tgz2build'; for subdir in $list; do \ echo "Making $target in $subdir"; \ if test "$subdir" = "."; then \ dot_seen=yes; \ local_target="$target-am"; \ else \ local_target="$target"; \ fi; \ (CDPATH="${ZSH_VERSION+.}:" && cd $subdir && make $local_target) \ || eval $failcom; \ done; \ if test "$dot_seen" = "no"; then \ make "$target-am" || exit 1; \ fi; test -z "$fail" make: Fatal error: Command failed for target `all-recursive' Current working directory /root/syslog/syslog-ng-3.2.2 *** Error code 1 make: Fatal error: Command failed for target `all' Thanks

2 1

Embedded log statements
by Hendrik Visage 29 Mar '11

29 Mar '11

Hi there, The example in the Administrator's Guide, looks (simplified) like this: log{ Source(source); filter(f1); destination(d1) log { filter(f1.1); destination(d1.1) log {filter(f1.1.1); destination(d1.1.1)}; }; }; ie. the equivalence of: log{ Source(source); filter(f1); destination(d1)} log{ Source(source); filter(f1); filter(f1.1); destination(d1.1)} log{ Source(source); filter(f1); filter(f1.1); filter(f1.1.1); destination(d1.1.1)}; I need to know if the following also allowed/expected behavior of embedded logs: log{ Source(source); filter(f1); destination(d1) log { filter(f1.1); destination(d1.1) log {filter(f1.1.1); destination(d1.1.1)}; }; log {filter(f1.2); destination(d1.2)}; }; ie. equivalent to: log{ Source(source); filter(f1); destination(d1)} log{ Source(source); filter(f1); filter(f1.1); destination(d1.1)} log{ Source(source); filter(f1); filter(f1.1); filter(f1.1.1); destination(d1.1.1)}; log{ Source(source); filter(f1); filter(f1.2); destination(d1.2)} And example to this nature in the documentation would be appreciated ;) Thank you Hendrik

2 2

Re: [syslog-ng] Regexp values in comparison
by Roland Kamke 29 Mar '11

29 Mar '11

Thank you, Robert. I guess since I only have syslog-ng OSE 3.0.10-1.rhel5 this regexp comparison stuff won't work anyway - that's a pity. I have to use CentOS 5 - do you know some yum repository providing newer OSE versions then 3.0.10-1.rhel5, e. g. OSE 3.3 or 3.2? Regards, Roland -----Ursprüngliche Nachricht----- Von: syslog-ng-bounces(a)lists.balabit.hu [mailto:syslog-ng-bounces@lists.balabit.hu] Im Auftrag von Fekete Robert Gesendet: Dienstag, 29. März 2011 11:57 An: Syslog-ng users' and developers' mailing list Betreff: Re: [syslog-ng] Regexp values in comparison Hi, yes, you can do such things in syslog-ng OSE 3.2 and 3.3, but not in earlier versions. Check out https://www.balabit.com/sites/default/files/documents/syslog-ng-ose-v3.2-gu… for details. Regards, Robert On 03/29/2011 10:27 AM, Roland Kamke wrote: > A newbie question: Is it possible to use values matching a regular expression in > a comparison? > > Imagine I receive messges like this: "Disk usage is 60%", "Disk usage is 80%", > etc. All match the regular expression "Disk usage is [0-9]+%". I do not want to > log all these messages, but only those where disk usage is at least 75 per cent. > Can this be achieved by proper configuration? > Where to look in the documentation? > > Currently I am stuck with this approch: > parser p_disk { > csv-parser(columns("fix.disk","fix.usage", "fix.is", "diskusage") > delimiters(" %") > ); > }; > filter f_disk_syntax { > message("Disk usage is [0-9]+%"); > }; > filter f_disk_usage { "$diskusage" > "75" }; > log l_mylog { > source(...); > filter(f_disk_syntax); > parser(p_disk); > filter(f_disk_usage); > destination(...); > }; > > Is this a useful approach? Can it be accomplished with fewer definitions? > The definition of f_disk_usage results in a syntax error. What would be the > right syntax? > filter f_disk_usage { "$diskusage" > "75" }; > filter f_disk_usage { "$diskusage" ge "75" }; > filter f_disk_usage { value("diskusage") > "75" }; > filter f_disk_usage { "$diskusage" > 75}; > filter f_disk_usage { "${diskusage}" > "75" }; > filter f_disk_usage { ${diskusage} > "75" }; > > Will I need to set up a pattern database to get rid of all unwanted messages? > > Thank you > Roland > > > > ______________________________________________________________________________ > Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng > Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng > FAQ: http://www.campin.net/syslog-ng/faq.html > ______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html

2 1

Regexp values in comparison
by Roland Kamke 29 Mar '11

29 Mar '11

A newbie question: Is it possible to use values matching a regular expression in a comparison? Imagine I receive messges like this: "Disk usage is 60%", "Disk usage is 80%", etc. All match the regular expression "Disk usage is [0-9]+%". I do not want to log all these messages, but only those where disk usage is at least 75 per cent. Can this be achieved by proper configuration? Where to look in the documentation? Currently I am stuck with this approch: parser p_disk { csv-parser(columns("fix.disk","fix.usage", "fix.is", "diskusage") delimiters(" %") ); }; filter f_disk_syntax { message("Disk usage is [0-9]+%"); }; filter f_disk_usage { "$diskusage" > "75" }; log l_mylog { source(...); filter(f_disk_syntax); parser(p_disk); filter(f_disk_usage); destination(...); }; Is this a useful approach? Can it be accomplished with fewer definitions? The definition of f_disk_usage results in a syntax error. What would be the right syntax? filter f_disk_usage { "$diskusage" > "75" }; filter f_disk_usage { "$diskusage" ge "75" }; filter f_disk_usage { value("diskusage") > "75" }; filter f_disk_usage { "$diskusage" > 75}; filter f_disk_usage { "${diskusage}" > "75" }; filter f_disk_usage { ${diskusage} > "75" }; Will I need to set up a pattern database to get rid of all unwanted messages? Thank you Roland

2 1

[PATCH] syslog-ng development module
by Juhasz Viktor 28 Mar '11

28 Mar '11

Hi! This patch creates a new package: syslog-ng-dev. Using this you can create your own syslog-ng modules BR, Viktor Juhasz

2 1