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" |
