[cod3fr3ak]

Anyone have any info on getting syslog-ng to log to mysql?

I found the follwoing sites but they really don't get into details like wether or not i should install from ports or what flavours to add to the mysql config.

http://www.campin.net/syslog-ng/faq.html
http://www.campin.net/newlogcheck.html
http://ezine.daemonnews.org/200111/syslog.html

[cod3fr3ak]

Ok. I figured this one out. Syslog-ng has a program option that allows you to kick off an external program when the daemon recieves certain types of log messages. According to Nate at www.campin.net use of this option along with another program called sqlsyslogd (http://www.frasunek.com/sources/security/sqlsyslogd/) will poke syslog lines into a mysql database. Using this method I was only able to get syslog-ng to send the first line into the database. So i created an rc.local entry that kicks sqlsyslogd off after the mysql database starts, and that seems to work fine. this will not catch anything that happens until after rc.local is run however, but I think I can live with that. Eventually I'd like to post a Howto for Secure Centralized Log Servers Using OpenBSD 3.5, here in SE.

Now I am looking for a web based monitoring package that would run on OpenBSD. Any ideas.? I've been searching thru the ports but the closest thing I have found was webmin. Nagios (http://www.nagios.org/) looks promising but I really do not know much about it.

[elmore]

I've run nagios and it is awesome just be prepared to make a significant investment in time.

How difficult was syslog-ng to setup? I've been looking into it as I think I might use it here at my new company but haven't had the time to set it up yet.

[cod3fr3ak]

The default config for syslog-ng on the client side is extremely simple. The server side takes a little more planning but that was pretty simple too. actually getting the syslog-ng/stunnel config setup was pretty easy on OpenBSD. My solaris boxes sometimes had problems building stunnel, but overall, it was pretty simple. I'll post something this weekend. As I am pretty much done with the log server for now.

[tarballed]

I've run nagios and it is awesome just be prepared to make a significant investment in time.

How difficult was syslog-ng to setup? I've been looking into it as I think I might use it here at my new company but haven't had the time to set it up yet.

Syslog-ng is not terribly difficult to setup. It has a small learning curve really. I cheated a little bit though. I went to my local tech bookstore and read the book "Building Secure Servers with Linux." There is a nice section in there on how to setup syslog-ng. I just took some notes. :)

Anyway, after understanding the basics of how to set it up, it's easy. I have all my servers logging to a central logging server running syslog-ng, over stunnel. Great way to get all your info quickly.

Also, there is a little script out called 'logwatch.' I've used this before on Linux machines and it is very handy. Right now, it does not fully work with *BSD, but the next release should work very well with *BSD. I asked one of the maintainers of logwatch when it was supposed to be release. All I got was , "Soon." :)

Cheers.

Tarballed

[cod3fr3ak]

tarballed, have you compared logwatch to logsentry? I have that installed but I need to go in and fix the regular expressions. Darn thing gives me entries anytime someone ssh's into the box sucessfully.
Also when you generated your ssl certs for stunnel did you leave the password option open, or did you install the client side certs on the server and vice-versa? I read a article about doing it this way over the way I did it which is just using the SSL cert to encrypt the data stream. I suppose I should get scanssl or one of those other ssl ports and see if I can decode and sniff the traffic.

[elmore]

Eventually I'd like to post a Howto for Secure Centralized Log Servers Using OpenBSD 3.5, here in SE.

I'd love to see that. I'm attempting to setup 50 plus soekris boxes to log to a central loghost right now and while the client side is pretty easy I'm clueless on the server side.

I'd also like to log into a sql db.

Do you have a nice web-based front-end for viewing?

Any chance I can catch a quick glimpse at a server conf file?

[cod3fr3ak]

syslog-ng server conf file:


sysadmin@phantom># more /etc/syslog-ng/syslog-ng.conf
options {
keep_hostname(yes);
sync(0);
log_fifo_size(1024);
stats(360000);
};

source shell {
unix-dgram("/dev/log");
internal();
tcp(ip(10.14.0.94) port(5141) keep-alive(yes) max-connections(3000));
udp();
};

# set it up
destination std_log {
file("/var/log/servers/$HOST/$YEAR/$MONTH/$DAY/$FACILITY@$HOST.$YEAR$MONTH$DAY"
owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes)
);
};

destination d_mysql {
pipe("/tmp/mysql.pipe" template("INSERT INTO logs (host, facility, priority, level, tag, date, time, program, msg)
VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG'
);\n") template-escape(yes)
);
};

destination oracle_alerts_log {
file("/var/log/servers/$HOST/$YEAR/$MONTH/$DAY/alert_$PROGRAM@$HOST.$YEAR$MONTH$DAY"
owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes)
);
};

destination oracle_export_log {
file("/var/log/servers/$HOST/$YEAR/$MONTH/$DAY/oracle_exports@$HOST.$YEAR$MONTH$DAY"
owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes)
);
};

destination salive_database_log {
file("/var/log/servers/salive/$YEAR/$MONTH/$DAY/salive.database.log"
owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes)
);
};

destination salive_server_log {
file("/var/log/servers/salive/$YEAR/$MONTH/$DAY/salive.server.log"
owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes)
);
};

destination salive_console_test_log {
file("/var/log/servers/salive/$YEAR/$MONTH/$DAY/salive.console_test.log"
owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes)
);
};

destination pflogd_log {
file("/var/log/servers/$HOST/$YEAR/$MONTH/$DAY/pflogd/pflogd.log"
owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes)
);
};

destination snort_log {
file("/var/log/servers/$HOST/$YEAR/$MONTH/$DAY/snort/snort.log"
owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes)
);
};

destination stunneld_log {
file("/var/log/servers/$HOST/$YEAR/$MONTH/$DAY/stunneld/stunneld.log"
owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes)
);
};

destination loghost {
file("/var/log/syslog-ng.all");
};

# Filters

filter f_no_salive_conn {
not match("client connected from 10.12.0.155")
and not match("client dropped connection from 10.12.0.155");
};

filter f_salive_database {
match("database.");
};

filter f_salive_server {
match("server.");
};

filter f_salive_console_test {
match("console.");
};

filter f_oracle_export_logs {
program("oracle_export");
};

filter f_no_oracle_export_logs {
not program("oracle_export");
};

filter f_pflogd_data {
program("pflogd");
};

filter f_no_pflogd_data {
not program("pflogd");
};

filter f_snort_data {
program("snort");
};

filter f_no_snort_data {
not program("snort");
};

filter f_stunneld_data {
program("stunnel_log");
};

filter f_no_stunneld_data {
not program("stunnel_log");
};

# Log Server Data
log {
source(shell);
filter(f_no_oracle_export_logs);
filter(f_no_salive_conn);
filter(f_no_pflogd_data);
filter(f_no_snort_data);
filter(f_no_stunneld_data);
destination(std_log);
};

# Log Oracle Export Data
log {
source(shell);
filter(f_oracle_export_logs);
destination(oracle_export_log);
};

# Log Snort Data
log {
source(shell);
filter(f_snort_data);
destination(snort_log);
};

# Log Server's Alive Data
log {
source(shell);
filter(f_no_salive_conn);
filter(f_salive_server);
destination(salive_server_log);
};

log {
source(shell);
filter(f_no_salive_conn);
filter(f_salive_database);
destination(salive_database_log);
};

# Log Console server Server's Alive Data
log {
source(shell);
filter(f_no_salive_conn);
filter(f_salive_console_test);
destination(salive_console_test_log);
};

# Log pflog Data
log {
source(shell);
filter(f_pflogd_data);
destination(pflogd_log);
};

# Log stunnel Data
log {
source(shell);
filter(f_stunneld_data);
destination(stunneld_log);
};

# Log everything to mysql
log {
source(shell);
filter(f_no_salive_conn);
destination(d_mysql);
};

# Log everything
log {
source(shell);
filter(f_no_salive_conn);
destination(loghost);
};


conf file for stunnel:


sysadmin@phantom># more /etc/stunnel/stunnel.conf
# Directives
cert = /etc/ssl/private/stunnel.pem
chroot = /var/stunnel
# PID is created inside chroot jail
pid = /var/run/stunnel.pid
setuid = _stunnel
setgid = _stunnel

# Workaround for Eudora bug
#options = DONT_INSERT_EMPTY_FRAGMENTS

# Authentication stuff
#verify = 2
# don't forget about c_rehash CApath
# it is located inside chroot jail:
#CApath = /certs
# or simply use CAfile instead:
#CAfile = /usr/local/etc/stunnel/certs.pem

# Some debugging stuff
debug = 7
output = /var/stunnel/tmp/stunnel.log

# Use it for client mode
client = no

# Service-level configuration

[secure_syslog-ng]
accept = phantom:5140
connect = phantom:5141

#[s1]
#accept = 5000
#connect = mail.osw.pl:110