Monitoring Proftpd

My major problem with proftpd is the real time monitoring of log analysis.

I will try to describe on this post the current solution I have build on my proftpd server.

Prerequisites:

  • Proftpd + mysql
  • Perl + Perl-MIME-Lite + Perl-MailsTools + Perl-DBI and Perl-DBD-mysql

ProFTPD + MySQL Authentication + E-Mail Monitor

First step is to sending an alert by email when a user update or connect to the ftp
This howto become from this great tutorial:
ProFTPD + MySQL Authentication + E-Mail Monitor Howto

The idea is to make a crontab script which check every X minutes that the field “modified” or “accessed” has been updated.

Logon to MySQL and issue the following command:

Code:

CREATE TABLE ftpusermonitor (
  id int(10) unsigned NOT NULL,
  userid varchar(32) NOT NULL default '',
  accessed datetime NOT NULL default '0000-00-00 00:00:00',
  modified datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (id),
  UNIQUE KEY userid (userid)
) TYPE=MyISAM;

You need to add this row to the ftpusermonitor table whenever you add a new ftp user :

Code:

INSERT INTO ftpusermonitor (id, userid) SELECT id, userid FROM ftpuser WHERE userid='myuser';

Now just copy this script on you ftp server:

Code:

#! /usr/bin/perl

# ftpmon.pl
# Monitor updates to FTP directories
# Author: Stefano Radice <stefano@despammed.com>

use strict;
use DBI;
use MIME::Lite;

my $server = 'localhost';
my $db = 'ftp';
my $dblogin = 'root';
my $dbpwd = 'password';
my $ftpalert = 'emmanuel.vargas@gmail.com';
#my $dbquery ="SELECT a.id, a.userid, a.modified FROM ftpuser a INNER JOIN ftpusermonitor b ON a.id = b.id WHERE a.modified > b.modified";
my $dbquery ="SELECT a.id, a.userid, a.accessed FROM ftpuser a INNER JOIN ftpusermonitor b ON a.id = b.id WHERE a.accessed > b.accessed";

my $dbh = DBI->connect("dbi:mysql:$db:$server", $dblogin, $dbpwd);
my $sth = $dbh->prepare($dbquery);
$sth->execute();

my @dbrow;

while(@dbrow=$sth->fetchrow_array()) {
    my $subject='FTP Update: ' . $dbrow[1];
    my $body=("The user -".$dbrow[1]. "- has acceded his FTP home.");
    send_mail($ftpalert,
              'FTPSERVER@ftp.crapules.com',
              $subject,
              $body);
    my $dbupd="UPDATE ftpusermonitor SET accessed='". $dbrow[2] . "' WHERE id=" . $dbrow[0];
#       my $dbupd=
#          "UPDATE ftpusermonitor SET modified='". $dbrow[2] . "' WHERE id=" . $dbrow[0];
    $dbh->do($dbupd);
}

$sth->finish( );

sub send_mail {
    print "Evoie d'un email\n";
    my($to, $from, $subject, $body)=@_;

    my $msg = new MIME::Lite
        From    =>$from,
        To      =>$to,
        Subject =>$subject,
        Type    =>'TEXT',
        Data    =>$body;
    $msg -> send;

}

Make this script executable:

Code:

# chmod 700 ftpmon.pl

and add this script in you crontab file:

Code:

# ftpmonitoring
*/5 * * * * /usr/sbin/ftpmoni.pl

Logs Analysis

First step is webalizer installation:

Code:

[root@pussycat plugins]# yum install webalizer

now add in your /etc/proftpd.conf:
In the general config:

Code:

TransferLog none     # WARNING: TransferLog directive might need to be placed inside a virtual host context if you use them.

In the <Anonymous>:

Code:

  TransferLog                 /var/log/proftpd/xferlog.legacy

Now edit your /etc/webalizer.conf:

Code:


LogFile        /var/log/proftpd/xferlog.legacy
LogType	ftp
OutputDir      /var/www/html/admin/webalizer
HistoryName	/var/lib/webalizer/webalizer.hist
Incremental	yes
IncrementalName	/var/lib/webalizer/webalizer.current
HostName       pussycat
PageType	htm*
PageType	cgi
PageType        php
PageType        shtml
DNSCache	/var/lib/webalizer/dns_cache.db


DNSChildren	10
Quiet	   yes

FoldSeqErr	yes
HideURL		*.gif
HideURL		*.GIF
HideURL		*.jpg
HideURL		*.JPG
HideURL		*.png
HideURL		*.PNG
HideURL		*.ra

SearchEngine	yahoo.com	p=
SearchEngine	altavista.com	q=
SearchEngine	google.com	q=
SearchEngine	eureka.com	q=
SearchEngine	lycos.com	query=
SearchEngine	hotbot.com	MT=
SearchEngine	msn.com		MT=
SearchEngine	infoseek.com	qt=
SearchEngine	webcrawler	searchText=
SearchEngine	excite		search=
SearchEngine	netscape.com	search=
SearchEngine	mamma.com	query=
SearchEngine	alltheweb.com	query=
SearchEngine	northernlight.com  qr=
# End of configuration file...  Have a nice day!

In your crontab file:

Code:


# webalizer for proftpd
10 3 * * * root /usr/bin/webalizer > /dev/null

Leave a Reply