Log Parser Queries

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.

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

 

This entry was posted in Code Snippets. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s