On a recent engagement, we wanted to tighten down some firewall settings surrounding FTP connections. We wanted to take only known clients and add their IP addresses to the firewall in order to allow them to login and then prevent anyone else. We had a several months worth of FileZilla logs and didn't look forward to going line by line and file by file to extract the logins that were successful.
Windows cmd
Using Window command line, we were able to use the findstr command on a directory of log files to do exactly that.
We were able to issue: findstr /c:"230 Logged on" fzs-*.log >> logged_on.csv and extract each of these lines from all files and combine them into one single file.
Excel Time
From here we were able to load the file into Excel and then use the Text to Columns feature with the delimiter as <space>. We removed all of the unnecessary columns and then removed the (,),> from the IP address column. From here we were able to use the Remove Duplicates function within excel and then sorted by username, IP to provide a distinct list of clients and IPs used to login to the FTP server.
Some usernames had several IP addresses that were not in the same subnet. The client will work with their clients to determine if each of these IP addresses are allowable and when we can tighten further.
Going Further
I am sure this process could be further scripted out to not require any of the Excel work. I know PowerShell could do this handily as well. It would also be neat to link up a GeoIP database to attach location data as well.
Additionally, there are better ways overall on handling this type of work. One, the client should look to use SFTP or FTPS. They could also use a certificate as well instead of password which could easily be leaked.
One step at a time...