|
This tip shows how to use the VShell triggers capability
to create a MySQL database to track user file-transfer activity.
Triggers allow VShell to respond to SFTP upload and download
actions by calling a script or executable. Parameters that
can be passed on a trigger condition include IP address, path
to filename, time, and user. The download trigger is available
in VShell version 2.3 and newer.
The following instructions assume that you have MySQL installed
and working. First you create the database and table. Then
you add triggers to VShell that will insert a record into
the table for each file download and upload. You can adapt
this concept to build your own log using the VShell trigger.
Create The Database
After executing MySQL, create a database called "sftp"
with a table named "sftp_transfers" using the following
commands:
mysql> CREATE
DATABASE sftp;
mysql> USE sftp;
mysql> CREATE
TABLE `sftp_transfers` (
`id` int(11)
NOT NULL auto_increment,
`username`
char(25) default NULL,
`filename`
char(250) default NULL,
`ip_addr`
char(15) default NULL,
`xfer_time`
time default NULL,
`action` char(15)
default NULL,
`date` timestamp(14)
NOT NULL,
PRIMARY KEY
(`id`)
) TYPE=MyISAM;
The table contains the following fields:
id – automatically incremented
as records are added
username – the username of the
person transferring files
filename – the full path of
the file being transferred
xfer_time – the time as reported
by the VShell trigger
action – upload or download
date – when the record was added
to the database
Adding Triggers To VShell Configuration
Once you have created your table, edit your vshelld_config
file to add the appropriate triggers. In the vshelld_config
file, find the parameters SFTPUploadCommand and SFTPDownloadCommand,
and add two mysql commands that will insert a record into
the sftp_transfers table of your sftp database.
NOTE: Below you will see that
the trigger commands have line breaks, but in your configuration
file the commands should be on one line.
VShell for UNIX
vshelld_config
###########################################################################
# The following two parameters are
used to execute a command following a
# triggered condition. Commands should
be in the following format:
# executable
[param1] [paramN]
#
# Note: Quotation marks should not
be used around the entire command
# (e.g., "executable param1").
Quotes can be used around the individual
# parts of the command (e.g., "executable"
"param1"). The command also
# needs to be on one line.
#
# VShell for UNIX will replace all
instances of $<parameter> in the command.
# See the man page vshelld_config(5)
for details.
# Values: executable <param1>
<paramN>
# Valid
values for replacement parameter:
# Parameter
Replacement Value
# $I
IP address
# $P
path
to filename
#
$T time
#
$U user
###########################################################################
SFTPUploadCommand /usr/local/mysql/bin/mysql
--user=user --password=password
--database=sftp --execute "INSERT
INTO sftp_transfers ( username, filename,
ip_addr, xfer_time, action ) VALUES
( '$U','$P','$I','$T','upload');"
SFTPDownloadCommand /usr/local/mysql/bin/mysql
--user=user
--password=password --database=sftp --execute
"INSERT INTO sftp_transfers
( username, filename, ip_addr, xfer_time,
action ) VALUES ( '$U','$P','$I',
'$T', 'download');"
Reload the VShell server configuration to activate these
triggers. Alternatively, restart the VShell server.
VShell for Windows Configuration
The trigger could also be modified to call a script that
calls the commands listed above. That way, your script or
batch file could perform more complex operations, for example
logging only file transfer by certain users.
Now that your upload and download triggers are in place,
when users upload and download files, they will be logged
into your MySQL database. The data recorded looks something
like the following:
mysql> select
* from sftp_transfers;
+----+----------+----------------------------------------------------------+
|id | username | filename |
ip_addr | xfer_time | action | date |
+----+----------+----------------------------------------------------------+
| 13 | kkb |
/Jan.txt | 127.0.0.1 | 16:40:18 | upload | 20040222164018
| 14 | kkb |
/Feb.txt | 127.0.0.1 | 16:44:47 | downld | 20040222164448
+----+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)
|
