Parse FileZilla Server logs with Log Parser

• November 23, 2008
• James Skemp
• article, software

While FileZilla Server is one of the best FTP servers available, it's logging leaves much to be desired.

However, after a couple of hours, I've created a script for Log Parser that will generate a W3C log from FileZilla Server's logs.

Save the following to a file, for example, FileZillaServer.sql:

SELECT
 SUBSTR(Text, 1, SUB(INDEX_OF(Text, ')'), 1)) AS RequestNumber
 , TO_TIMESTAMP(
  TRIM(
   SUBSTR(
    Text
    , ADD(INDEX_OF(Text, ')'), 1)
    , SUB(INDEX_OF(Text, '-'), ADD(INDEX_OF(Text, ')'), 4))
   )
  )
  , 'M/d/yyyy?H:mm:ss'
 ) AS DateTime
 --, TRIM(SUBSTR(Text, ADD(INDEX_OF(Text, '-'), 1), SUB(INDEX_OF(Text, '>'), ADD(INDEX_OF(Text, '-'), 1))))
 , TRIM(SUBSTR(
  TRIM(SUBSTR(Text, ADD(INDEX_OF(Text, '-'), 1), SUB(INDEX_OF(Text, '>'), ADD(INDEX_OF(Text, '-'), 1))))
  , 0
  , LAST_INDEX_OF(
   TRIM(SUBSTR(Text, ADD(INDEX_OF(Text, '-'), 1), SUB(INDEX_OF(Text, '>'), ADD(INDEX_OF(Text, '-'), 1))))
   , '('
  )
 )) AS User
 , SUBSTR(
  TRIM(SUBSTR(Text, ADD(INDEX_OF(Text, '-'), 1), SUB(INDEX_OF(Text, '>'), ADD(INDEX_OF(Text, '-'), 1))))
  , ADD(LAST_INDEX_OF(
   TRIM(SUBSTR(Text, ADD(INDEX_OF(Text, '-'), 1), SUB(INDEX_OF(Text, '>'), ADD(INDEX_OF(Text, '-'), 1))))
   , '('
  ), 1)
  , SUB(LAST_INDEX_OF(
   TRIM(SUBSTR(Text, ADD(INDEX_OF(Text, '-'), 1), SUB(INDEX_OF(Text, '>'), ADD(INDEX_OF(Text, '-'), 1))))
   , ')'
  ), ADD(LAST_INDEX_OF(
   TRIM(SUBSTR(Text, ADD(INDEX_OF(Text, '-'), 1), SUB(INDEX_OF(Text, '>'), ADD(INDEX_OF(Text, '-'), 1))))
   , '('
  ), 1))
 ) AS IpAddress
 , SUBSTR(Text, ADD(INDEX_OF(Text, '>'), 2), SUB(STRLEN(Text), INDEX_OF(Text, '>'))) AS Request
INTO FileZilla.log
FROM fzs-*.log
WHERE Text LIKE '(%'
 AND Request NOT LIKE 'Connected,%'
 AND Request NOT LIKE '221 %'
 AND Request NOT LIKE 'disconnected%'
 AND Request NOT LIKE 'QUIT%'

Items in bold can be easily changed.

Then call the SQL with:

logparser -rtp:-1 -i:TEXTLINE -o:W3C file:FileZillaServer.sql

This returns the following fields:

• RequestNumber (string)
• DateTime (timestamp)
• User (string)
• IpAddress (string)
• Request (string)

Notes

In my attempt to parse the files, I ran into an issue with generating the timestamp. While AM/PM is added to the time, the time is actually output in 24-hour time.

Ignoring the fact that it therefore seems unnecessary to include AM/PM as well, Log Parser seems to run into issues if you try to TO_TIMESTAMP with the AM/PM included, and 24-hour time. In particular, it chokes on hour 0 and 13-23, if there's also AM or PM.

Changing the code to pull AM/PM resolved the issue with NULLs (-) being returned.

• log parser