Author Topic: sql session/transfer logging recipes?  (Read 276 times)

Offline tony-k

  • New user
  • *
  • Posts: 9
    • View Profile
sql session/transfer logging recipes?
« on: May 13, 2017, 11:17:29 pm »
i'm interested in logging session and transfer history to sql tables.

can anyone share any recipes to use in sql conf files for this?

not sure if-can/how to track session end times

not sure if-can/how to track transfer duration times

any how-to's appreciated!


Offline openbridge

  • Regular User
  • **
  • Posts: 46
    • View Profile
Re: sql session/transfer logging recipes?
« Reply #1 on: July 13, 2017, 10:59:28 pm »
Here is a block that I use. It leverages a class statement so only remote (not local) events are logged. This logs the MD5 checksum of the file as well as a bunch of other attributes. The AWS S3 bucket reflects the fact we use S3 to back the file transfers so we want to know which bucket a file resides. We also will look at the log for the MD5 checksum and the checksum of the file on S3, if they are the same then success, if a new DB entry marks the file as failed.

We also log the outbound and inbound transfer sizes. We use our own quota system for users.

Code: [Select]
  <IfClass remotehost>
    SQLLog              PASS updatecount IGNORE_ERRORS
    SQLNamedQuery       updatecount UPDATE "count=count+1, accessed=now() WHERE userid ='%U'" ftpd_user logging
    SQLNamedQuery       accesslog FREEFORM "INSERT INTO ftpd_log (`userid`, `server_ip`, `transfer_date`, `operation`, `protocol`, `client_ip`, `transfer_time`, `bytes_transfer`, `bucket`, `file_hash_type`, `file_hash`, `file_path`, `transfer_status`) VALUES ('%u', '%H', NOW(), '%r', '%{protocol}', '%a', '%T', '%b', '%{env:AWS_S3BUCKET}', '%{note:mod_digest.algo}', '%{note:mod_digest.digest}', '%f', '%{transfer-status}')" logging

    SQLLog              RETR quota_xfer_out IGNORE_ERRORS
    SQLLog              STOR,STOU quota_xfer_in IGNORE_ERRORS
    SQLNamedQuery       quota_xfer_in UPDATE "bytes_in_used = bytes_in_used + '%b', bytes_xfer_used = bytes_xfer_used + '%b', files_in_used = files_in_used + 1, files_xfer_used = files_xfer_used + 1 WHERE userid = '%u'" ftpd_quotatally logging
    SQLNamedQuery       quota_xfer_out UPDATE "bytes_out_used = bytes_out_used + '%b', bytes_xfer_used = bytes_xfer_used + '%b', files_out_used = files_out_used + 1, files_xfer_used = files_xfer_used + 1 WHERE userid = '%u'" ftpd_quotatally logging