GoAdmio

ftplog

Import the vsftpd transfer log into the FTP log database and look up the country and organization of each IP

[!NOTE] The index describes this command as "Report FTP activity for a domain". The script documented here takes no domain and prints no report. It copies the transfers from /var/log/vsftpd.log into /var/lib/sqlite/ftplog.db and fills in the country and organization of each IP address. Other commands and the panel read that database. Either update the index description to match this page, or check that this is the script you meant to document.

On this page: Overview · Quick start · Requirements · Syntax · Output · What is stored · Examples · Troubleshooting · Details · See also


Overview

/go/ftplog keeps the FTP log database up to date. It:

fixquota runs this command first, so that its FTP bandwidth figures are current.


Quick start

/go/ftplog

Requirements


Syntax

/go/ftplog

The command has no arguments or options. Any arguments are logged and ignored.


Output

Outcome What you get
Caught up Break... once, when the import reaches a line that is already stored. This is the normal result of most runs.
Import error Break... once, when any insert fails. The import stops there.
Lookups Nothing.

The exit code is 0.

Logging

Every run is appended to /var/log/go/ftplog.log.


What is stored

Each transfer becomes one row in Ftplog. The values come from fixed positions in the vsftpd log line.

Column Source and meaning
Domain The domain of the hosting account whose username matches Username. Empty if none matches.
Username The FTP user who made the transfer.
Date Weekday, day, month, and year, in the form Fri 20 Nov 2026.
Time Time of day from the log.
TransferTime Seconds the transfer took.
IP The first IPv4 address found in the line.
Country, Descr, Organization Filled in later from whois.
FileSize Bytes transferred.
FileName The file's path.
Mode ASCII or Binary.
Operation No Special for _. Other values are stored as they are in the log.
Direction Upload for i, Download for o. Other values are stored as they are.
AccessMode Anon, Guest, or Regular.
Completed Yes for a complete transfer, No for an incomplete one.
UniqueLog The MD5 hash of the log line.
TimeStamp Not the real time. The time of the run minus the line's position, counted from the end of the log.

Examples

# Update the FTP log database
/go/ftplog
# Show the latest rows (rowid order; TimeStamp is not the real time)
sqlite3 /var/lib/sqlite/ftplog.db "SELECT Date, Time, Username, Direction, FileName, FileSize FROM Ftplog ORDER BY rowid DESC LIMIT 20"
# Transfer volume per domain for a month
sqlite3 /var/lib/sqlite/ftplog.db "SELECT Domain, SUM(FileSize) FROM Ftplog WHERE Date LIKE '%Nov 2026%' GROUP BY Domain"

Troubleshooting

Symptom Likely cause Fix
Break... is printed The import reached a line that is already stored. Normal. It isn't an error unless data is missing.
Nothing is imported /var/log/vsftpd.log is missing or unreadable, or vsftpd doesn't write the standard xferlog format. Check the log file, and the xferlog_enable and xferlog_std_format settings of vsftpd.
Old transfers are missing Only /var/log/vsftpd.log is read, not rotated copies. Run the command more often than the log is rotated.
There are gaps in the history One failed insert stops the import, and the older lines behind it are skipped for good. Look at the transfers around the gap. See Notes for maintainers.
A user's transfers are missing A file name that contains an IP-like number or quotes causes the line to be skipped or to break the import. See Notes for maintainers.
Country stays empty whois failed, was rate-limited, or returned text that can't be stored. The IP is looked up again on every run. Check whois by hand for that IP.
The command is slow Each log line needs its own database calls, and each new IP needs a whois lookup. Run it regularly so that each run has little to import. The first run on a large log takes the longest.
TimeStamp doesn't match the real time It is built from the time of the run. Use Date and Time instead.
tac: Permission denied The command wasn't run as root. Run it as root.
Domain is empty for some rows The FTP user is not the account's main user. See Notes for maintainers.

Details

What it does

  1. Logs the call, and loads /go/function (it provides the WHOIS function).
  2. Reads /var/log/vsftpd.log from the end with tac.
  3. For each line:
    1. Removes the first single quote.
    2. Hashes the line, and splits it into fields.
    3. Works out the date, time, IP, file size, file name, mode, direction, user, and completion flag, and finds the domain of the user.
    4. If the line contains exactly one IPv4 address, inserts a row. If the insert fails, prints Break... and stops.
  4. Writes the list of IPs with an empty Country to /usr/local/goadmio/tmp/ftplog.db. This is a text file, not a database.
  5. For each IP in that list, runs whois, takes the country, the last description, and the organization (organization or org-name), and updates every row for that IP.
flowchart TD
    A["ftplog"] --> B["Read vsftpd.log from the newest line"]
    B --> C{"Line has exactly one IPv4?"}
    C -- "No" --> B
    C -- "Yes" --> D["Insert row"]
    D -- "Failed or already stored" --> E["Break, stop reading"]
    D -- "OK" --> B
    E --> F["List IPs without a country"]
    F --> G["whois each IP, update rows"]

Notes for maintainers

Issue Why it matters Suggested fix
Only the first single quote is removed, and the rest of the line goes straight into the SQL A file name with two or more quotes breaks the statement and can inject SQL into a database that is edited as root. FTP users choose their own file names. Insert the values through parameters or escape every ' as '', and validate every field.
whois values (description, organization) are placed in SQL unescaped An organization name such as "O'Reilly" breaks the update, and a crafted record can inject SQL. Escape the values.
The IP is the result of grep -Eo over the whole line A file name that contains an IP-like number gives two matches, the check fails, and the transfer is skipped. A user can use this to keep transfers out of the FTP bandwidth count. Use the client address field of the log line.
TimeStamp is the time of the run minus the line's position It isn't the event time, and the same line gets a different value if it is ever reloaded. Anything that filters by TimeStamp is wrong. Convert the real Date and Time of the line.
Any insert error stops the import One bad line (a quote, a locked database, a full disk) loses all older lines behind it. A duplicate-line hash in the same second has the same effect. Break... is also the normal end. Tell "already stored" apart from real errors, and ignore only duplicates.
Only /var/log/vsftpd.log is read Lines in rotated logs are lost. Keep the position in a state file, or read the rotated files too.
Two database calls and an MD5 per log line Very slow on large logs. Look up users once, and insert in a single transaction.
whois is run for every IP that has no country, on every run IPs that always fail are retried forever, and whois servers rate-limit. Store a placeholder for failed lookups, and cache results.
The domain is found from Site.Username only Transfers by other FTP accounts of a domain have no domain, and fixquota counts only the main username. Also look the user up in the FTP accounts table.
Fields are found by position File names with spaces shift the fields, unless vsftpd replaces them. Parse from both ends, or check the format.
The log is read with tac without sudo, and there is no root check Fails when not run as root. Re-run through sudo at the top.
/usr/local/goadmio/tmp/ftplog.db is a plain text list Its name looks like a database, and is easy to confuse with /var/lib/sqlite/ftplog.db. Rename it, for example ftplog-ips.txt.
The description is the last descr line and stops at the next : Parts of the text can be cut. Use the whole value after the first :.

See also

Command Description
fixquota Recalculate every account's disk and bandwidth usage, block FTP uploads over the disk quota, and suspend expired accounts
ftp Create or edit FTP accounts for a domain
vsftpd Install and configure the VSFTPD FTP server
permission Enable or disable FTP permissions for a domain
bandwidth Set or check monthly bandwidth limits per domain
block Block an IP address on a GoAdmio server