Log Parser Plus – Log Parser Example Queries.
FileZilla Server logs to W3C extended Converts FileZilla Server logs to W3C extended log format.
1 |
logparser - rtp :-1 -i:TEXTLINE -o:W3C " 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 ( 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%'" |
Parse FileZilla Server logs with Log Parser.
HTTP Status Codes (pie chart) Returns a pie chart showing what percentage status codes account for. Requires Microsoft Office.
1 |
logparser -o:chart -chartType:Pie -chartTitle:"Status as Percent of Requests" " SELECT sc-status AS [HTTP Status Code], COUNT(*) AS Requests INTO HttpStatusCodePieChart.png FROM ex0811*. log GROUP BY [HTTP Status Code] ORDER BY Requests DESC" |
Requests by hour (using TO_TIME) Returns a listing of requests by hour, across all days, converted to local time.
1 |
logparser - rtp :-1 " SELECT TO_TIME ( TO_LOCALTIME ( QUANTIZE ( TO_TIMESTAMP (date, time), 3600))) AS [Hour], COUNT(*) AS [Requests], MUL ( PROPCOUNT (*), 100) AS [PercentOfTotal] INTO HourlyReport.txt FROM ex0902*. log GROUP BY [Hour] ORDER BY [Hour]" |
Notes: Leave a comment on this query.
Requests by hour per day Returns a listing of requests by hour, per day, converted to local time.
1 |
logparser - rtp :-1 " SELECT TO_LOCALTIME ( QUANTIZE ( TO_TIMESTAMP (date, time), 3600)) AS [DateTime], COUNT(*) AS [Requests] INTO HourlyDayReport.txt FROM ex0902*. log GROUP BY [DateTime] ORDER BY [DateTime]" |
Requests by seconds to process Number of requests that took how x seconds to process, rounded up to the nearest second.
1 |
logparser -i:iisw3c -o:csv " SELECT ADD ( DIV ( QUANTIZE (time-taken, 1000), 1000), 1) AS [Seconds], COUNT(*) AS [Requests] INTO RequestProcessingTimes.csv FROM ex0906*. log GROUP BY [Seconds] ORDER BY [Seconds] DESC" |
Requests, bandwidth, and last visit, by IP address and user agent. Returns a listing of rquests, bandwidth, and last visit, by unique IP address and user agent.
1 |
logparser - rtp :-1 " SELECT COUNT(*) AS [Requests], DIV ( DIV ( SUM (cs-bytes), 1024), 1024) AS [MBytes received], DIV ( DIV ( SUM (sc-bytes), 1024), 1024) AS [MBytes sent], c-ip AS [IP Address], cs(User-Agent) AS [User agent], MAX (date) AS [Last visit] INTO IpAddress.txt FROM ex0811*. log GROUP BY [IP Address], [User agent] ORDER BY [Requests] DESC" |