VanDyke Software

Index

Using a VShell Trigger to Log File-Transfer Activity to a MySQL Database

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 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)