Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I found this useful post relating this topic:

http://www.supertom.com/code/vsftpd_mysql_syslog_configuration.html

Unfortunately it is not a tutorial but kind of a notepad for the author. After some time of fiddling I managed to get basic logging to my MySQL database working. But some of the most interesting fields like the file name, file size and transfer speed are not being logged.

Here's the interesting part of my /etc/rsyslog.conf:

$template vsftpdmysql, "INSERT INTO vsftpd_logs (dte_generated,user,hostname,remote_host,status,action,filename,bytes,transfer_speed) VALUES ('%timegenerated:::date-mysql%','%msg:R,ERE,1,FIELD:\[(.*)\]--end%','%hostname%','%msg:R,ERE,1,Z$

ftp.*                                                   :ommysql:localhost,vsftpd,vsftpd,vsftpdpassword;vsftpdmysql

This is the interesting part of my /etc/pam.d/vsftpd

auth            required pam_mysql.so user=vsftpd passwd=vsftpdpassword host=localhost db=vsftpd table=accounts usercolumn=username passwdcolumn=pass crypt=3 sqllog=1 logtable=vsftpd_logs logmsgcolumn=action logusercolumn=user loghostcolumn=host logtimecolumn=date_generated logpidcolumn=pid logrhostcolumn=remote_host
account         required pam_mysql.so user=vsftpd passwd=vsftpdpassword host=localhost db=vsftpd table=accounts usercolumn=username passwdcolumn=pass crypt=3 sqllog=1 logtable=vsftpd_logs logmsgcolumn=action logusercolumn=user loghostcolumn=host logtimecolumn=date_generated logpidcolumn=pid logrhostcolumn=remote_host

And this is what my database schema looks like:

+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| ID             | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| date_generated | datetime         | YES  |     | NULL    |                |
| user           | varchar(120)     | YES  |     | NULL    |                |
| host           | varchar(255)     | YES  |     | NULL    |                |
| remote_host    | varchar(255)     | YES  |     | NULL    |                |
| status         | varchar(120)     | YES  |     | NULL    |                |
| action         | varchar(8192)    | YES  |     | NULL    |                |
| filename       | varchar(255)     | YES  |     | NULL    |                |
| bytes          | varchar(255)     | YES  |     | NULL    |                |
| transfer_speed | varchar(255)     | YES  |     | NULL    |                |
| pid            | int(11)          | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

At the moment, the log in the database only contains logins, no file transfer and alike. What am I doing wrong here?

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.