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.
Subscribe