Logging your FTP transfers to xferlog with ProFTPd is a nice thing. This can easily be done by a one-liner in /etc/proftpd/proftpd.conf:
|
1 2 3 |
TransferLog /var/log/proftpd/xferlog |
This generates a nice transfer log which we could then parse for transfer statistics. But there is a much better way to accomplish this: MySQL. Let’s use MySQL for everything!
It’s pretty straightforwarded to get ProFTPd to log into a MySQL table.
First, create a table ‚ftpxferlog‘ in your database and give the required user INSERT access to it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE `ftpxferlog` ( `id` INT NOT NULL auto_increment, `username` VARCHAR(30) NOT NULL default '', `filename` text, `size` bigint(20) default NULL, `host` tinytext, `ip` tinytext, `action` tinytext, `duration` tinytext, `localtime` timestamp NULL default NULL, `success` BOOL NOT NULL default '0', PRIMARY KEY (`id`), KEY `idx_usersucc` (`username`, `success`) ) TYPE=InnoDB; GRANT INSERT ON mydatabase.ftpxferlog TO myuser@localhost; FLUSH PRIVILEGES; |
Now, add the following to your proftpd.conf:
|
1 2 3 4 5 6 7 8 9 10 |
SQLConnectInfo mydatabase@localhost myuser mypassword # xfer log in mysql SQLLog RETR,STOR transfer1 SQLNamedQuery transfer1 INSERT "NULL, '%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), '1'" ftpxferlog SQLLOG ERR_RETR,ERR_STOR transfer2 SQLNamedQuery transfer2 INSERT "NULL, '%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), '0'" ftpxferlog |
That’s it. Restart ProFTPd and test it. As soon as you got some rows, try to run a query like e.g.:
|
1 2 3 |
SELECT sum(size) FROM ftpxferlog WHERE username = 'web2' AND success = '1' AND localtime LIKE '2007-06-07%'; |
For traffic statistics calculation this is way easier than parsing a logfile. Also I have defined the key ‚idx_usersucc‘ on the table to speed things up.
Check the ProFTPd SQL Tutorial (in German) for advanced configuration.
Apr 01, 2008 - 09:06 AM
Hello,
thank you for your article, but the word „localtime“ is a reserved word of mysql so I would suggest changing the name.
By Obe.
Sep 28, 2009 - 05:44 PM
I can’t make it work, the ftp service does not initialize, when I add the lines.
It seems that the server need something more installed.
I got this error:
Fatal: unknown configuration directive ‚SQLConnectInfo‘ on line 68 of ‚/etc/proftpd.conf‘
Regards!
Sep 28, 2009 - 05:52 PM
@dragnovich: Make sure you have the mod_sql_mysql Module enabled.
On Debian Lenny, you’ll find this configuration in /etc/proftpd/modules.conf:
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
Make sure that you have this module installed and restart Proftpd:
# apt-get install proftpd-mod-mysql
# /etc/init.d/proftpd restart