Using LogParser with FTP 7.x Sessions
One of the great features that we added to our W3Clogging enhancements in FTP 7.0 and FTP 7.5 is the ability to track unique sessions, which are represented by GUIDs in a field that is named x-session. Because of this addition, you can do some interesting things with LogParser when analyzing your FTP logs.
The purpose of today's blog is to show a couple of the scripts that I use to analyze some of the session-based information that I'm interested in from time to time.
Using LogParser to Count FTP Sessions
Since the new FTP service tracks unique sessions, it is now possible to generate reports that show the number of unique FTP sessions you served by day. The following batch file accomplishes this in two parts: first it creates a temporary tab-separated-value file that contains the unique sessions by day, then it calculates the number of sessions by day and writes the totals to a tab-separated-value file that is named Sessions.tsv, which you can open using an application like Microsoft Excel.
@echo off
set LOGPATTERN=u_ex*.log
logparser.exe "SELECT DISTINCT date,x-session INTO '%~n0.tmp' from %LOGPATTERN%" -i:w3c -o:tsv -headers:ON
if exist "%~n0.tmp" (
logparser.exe "SELECT date,COUNT(x-session) AS sessions INTO sessions.tsv FROM '%~n0.tmp' GROUP BY date" -i:tsv -o:tsv -headers:ON
del "%~n0.tmp"
)
set LOGPATTERN=
Using LogParser to Split FTP Log Files into Unique Session Activity Logs
I use the following script when I am testing various FTP scenarios that will split my FTP log files into individual log files that are named after the GUID for each session. (Note: Please bear in mind, this may generate a lot of log files, so use it sparingly!) You can then analyze the resulting log files to see the list of client activity that was unique to each session.
This script accomplishes its objective in two parts: first it creates a temporary tab-separated-value file with the list of unique session IDs, then it loops through each session ID and creates a W3C log file for each session's activity.
@echo off
set LOGPATTERN=u_ex*.log
logparser.exe "select distinct x-session into '%~n0.tmp' from '%LOGPATTERN%'" -i:w3c -o:tsv -headers:off
if exist "%~n0.tmp" (
for /f "delims=|" %%a in (%~n0.tmp) do (
logparser.exe "select date,time,c-ip,cs-username,s-ip,s-port,cs-method,cs-uri-stem,sc-status,sc-win32-status,sc-substatus,x-session,x-fullpath into '%%a.log' from '%LOGPATTERN%' where x-session='%%a' order by date,time" -i:w3c -o:w3c
)
del "%~n0.tmp"
)
set LOGPATTERN=
That about does it for today - I hope this helps!