Log Parser Plus - Log Parser Queries

200 status codes Return a listing of Web pages, and referring pages, that returned a 200 status code.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT cs-uri-stem, cs-uri-query, date, sc-status, cs(Referer) INTO 200sReport.txt FROM ex0902*.log WHERE (sc-status >= 200 AND sc-status < 300) ORDER BY sc-status, date, cs-uri-stem, cs-uri-query"

300 status codes Return a listing of Web pages, and referring pages, that returned a 300 status code.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT cs-uri-stem, cs-uri-query, date, sc-status, cs(Referer) INTO 300sReport.txt FROM ex0902*.log WHERE (sc-status >= 300 AND sc-status < 400) ORDER BY sc-status, date, cs-uri-stem, cs-uri-query"

400 status codes Return a listing of Web pages, and referring pages, that returned a 400 status code.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT cs-uri-stem, cs-uri-query, date, sc-status, cs(Referer) INTO 400sReport.txt FROM ex0811*.log WHERE (sc-status >= 400 AND sc-status < 500) ORDER BY sc-status, date, cs-uri-stem, cs-uri-query"

400 status codes (using BETWEEN) Return a listing of Web pages, and referring pages, that returned a 400 status code.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT cs-uri-stem, cs-uri-query, date, sc-status, cs(Referer) INTO 400sReport.txt FROM ex0811*.log WHERE (sc-status BETWEEN 400 AND 499) ORDER BY sc-status, date, cs-uri-stem, cs-uri-query"

500 status codes Return a listing of Web pages, and referring pages, that returned a 500 status code.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT cs-uri-stem, cs-uri-query, date, sc-status, cs(Referer) INTO 500sReport.txt FROM ex0811*.log WHERE (sc-status >= 500 AND sc-status < 600) ORDER BY sc-status, date, cs-uri-stem, cs-uri-query"

Bandwidth usage Returns bytes (as well as converted to KB and MB) received and sent, per date, for a Web site.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT date, SUM(cs-bytes) AS [Bytes received], DIV(SUM(cs-bytes), 1024) AS [KBytes received], DIV(DIV(SUM(cs-bytes), 1024), 1024) AS [MBytes received], SUM(sc-bytes) AS [Bytes sent], DIV(SUM(sc-bytes), 1024) AS [KBytes sent], DIV(DIV(SUM(sc-bytes), 1024), 1024) AS [MBytes sent], COUNT(*) AS Requests INTO Bandwidth.txt FROM ex0811*.log GROUP BY date ORDER BY date"

Bandwidth usage by request Returns pages sorted by the total number of bytes transferred, as well as the total number of requests and average bytes.

Keywords:

iisw3c

Statement:

logparser -i:iisw3c -rtp:-1 "SELECT DISTINCT TO_LOWERCASE(cs-uri-stem) AS [Url], COUNT(*) AS [Requests], AVG(sc-bytes) AS [AvgBytes], SUM(sc-bytes) AS [Bytes sent] INTO Bandwidth.txt FROM ex0909*.log GROUP BY [Url] HAVING [Requests] >= 20 ORDER BY [Bytes sent] DESC"

Notes: Original query posted to Server Fault by Jeff Atwood and corrected by James Skemp.

Browsers accessing content Return a listing of browsers (user agents), with counts, that accessed content.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT cs(User-Agent) AS Browser, COUNT(*) AS Requests INTO BrowserReport.txt FROM ex0811*.log GROUP BY Browser ORDER BY Requests DESC"

Cookie lengths Returns cookie lengths along with the ip and the number of requests made with that cookie/ip.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT STRLEN(cs(Cookie)) AS [CookieLength], c-ip, COUNT(*) AS [Requests], cs(Cookie) INTO CookieLengths.txt FROM ex0910*.log WHERE cs(cookie) IS NOT null GROUP BY cs(Cookie), c-ip, [CookieLength] ORDER BY [CookieLength] DESC"

Notes: By default, IIS does not log cs(Cookie). If you wish to log this you must enable it. See Performance Research, Part 3: When the Cookie Crumbles for more information.

Data sent and received, by file type. Returns a listing of file types, as well as the amount of data sent and received by the server, for each request.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT EXTRACT_EXTENSION(cs-uri-stem) AS [File Type], DIV(SUM(sc-bytes), 1024) AS [Sent (KB)], DIV(SUM(cs-bytes), 1024) AS [Received (KB)] INTO FileTypeDataSentRec.txt FROM ex0812*.log GROUP BY [File Type] ORDER BY [File Type]"

Domains referring traffic for resources. Returns a listing of domains that referred traffic to the site.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT EXTRACT_TOKEN(cs(Referer), 2, '/') AS [Domain], COUNT(*) AS [Requests] INTO ReferringDomains.txt FROM ex0902*.log GROUP BY [Domain] ORDER BY [Requests] DESC"

FileZilla Server logs to W3C extended Converts FileZilla Server logs to W3C extended log format.

Keywords:

textline
FileZilla Server
w3c

Statement:

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

Notes: For more information visit 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.

Keywords:

iisw3c

Statement:

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"

Notes: Microsoft Office must be installed to generate the chart. Example output available at StrivingLife.com.

HTTP status codes (text) Return a listing of HTTP status codes.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT sc-status AS [HTTP Status Code], COUNT(*) AS Requests INTO HttpStatusCodes.txt FROM ex0811*.log GROUP BY [HTTP Status Code] ORDER BY Requests DESC"

HTTP status codes and percentage of total Returns a listing of HTTP status codes with counts and percentage per.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT sc-status, COUNT(*) AS [Requests], MUL(PROPCOUNT(*), 100) AS [Percentage] FROM ex0902*.log GROUP BY sc-status"

POSTs per ip by total requests Returns a listing of ips doing POST requests, ordered by the number of POSTs. Helpful for tracking down users attempting to spam.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT c-ip, COUNT(*) AS [Requests] INTO PostRequests.txt FROM ex0910*.log WHERE cs-method = 'POST' GROUP BY c-ip ORDER BY [Requests] DESC"

Query parameter hacking attempt - http:// Returns a listing of IP addresses that may be making a hacking attempt by passing a site address.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 -o:w3c "SELECT c-ip, COUNT(*) AS [Requests] INTO QueryParams-1c.log FROM ex0910*.log WHERE cs-uri-query IS NOT null and STRCNT(TO_LOWERCASE(cs-uri-query), 'http://') > 0 GROUP BY c-ip ORDER BY [Requests] DESC"

Notes: This query will only work if you are not expecting http:// to be present in a query parameter. Run another query against the logs to determine what files were requested by questionable IP addresses.

Query parameters with counts Returns a listing of query parameters passed to pages, with the number of times such requests were made. Helpful to get an idea on whether any hacking attempts were being made.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 -o:w3c "SELECT cs-uri-query, COUNT(*) AS [Requests] INTO QueryParams-1b.log FROM ex0910*.log WHERE cs-uri-query IS NOT null GROUP BY cs-uri-query ORDER BY cs-uri-query"

Notes: See also Query parameters with counts and ips.

Query parameters with counts and ips Returns a listing of query parameters passed to pages, along with the number of times IP addresses made those requests.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 -o:w3c "SELECT cs-uri-query, COUNT(*) AS [Requests], c-ip INTO QueryParams-1a.log FROM ex0910*.log WHERE cs-uri-query IS NOT null GROUP BY cs-uri-query, c-ip ORDER BY cs-uri-query"

Notes: See also Query parameters with counts.

Requests by file type (extension). Returns a listing of file types and the total number of requests.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT EXTRACT_EXTENSION(cs-uri-stem) AS [File Type], COUNT(*) AS [Requests] INTO FileTypeRequests.txt FROM ex0812*.log GROUP BY [File Type] ORDER BY [Requests] DESC"

Requests by hour (using TO_STRING) Returns a listing of requests by hour, across all days, by converting the hour to a string.

Keywords:

iisw3c

Statement:

logparser -rtp:-1 "SELECT TO_STRING(time, 'hh') AS [Hour], COUNT(*) AS [Requests], MUL(PROPCOUNT(*), 100) AS [PercentOfTotal] INTO HourlyReport.txt FROM ex0902*.log GROUP BY [Hour] ORDER BY [Hour]"

Requests by hour (using TO_TIME) Returns a listing of requests by hour, across all days, converted to local time.

Keywords:

iisw3c

Statement:

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

Requests by hour per day Returns a listing of requests by hour, per day, converted to local time.

Keywords:

iisw3c

Statement:

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.

Keywords:

iisw3c

Statement:

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.

Keywords:

iisw3c

Statement:

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"