Log Parser Example Queries

The following queries can be run with Log Parser. In every case (except if noted) the FROM and INTO statements should be modified based on your needs.

Query suggestions or questions are welcome, and can be emailed to strivinglife [at] gmail dot com, or submitted on the GitHub or GitLab project pages.

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"
Leave a comment on this query.

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]"
Leave a comment on this query.

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"
Leave a comment on this query.

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"
Leave a comment on this 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"
Leave a comment on this 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"
Leave a comment on this query.

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"
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:
  • 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]"
Leave a comment on this query.

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]"
Leave a comment on this query.

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"
Leave a comment on this query.

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. Leave a comment on this 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"
Leave a comment on this query.

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"
Leave a comment on this query.

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. Leave a comment on this query.

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. Leave a comment on this query.

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. Leave a comment on this query.

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. Leave a comment on this query.

Requests per day

Returns a listing of dates with the total number of requests.
Keywords:
  • iisw3c
Statement: 
logparser -rtp:-1 "SELECT TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 'yyyy-MM-dd') AS [Day], COUNT(*) AS [Requests] INTO DayReport.txt FROM ex1003*.log GROUP BY [Day] ORDER BY [Day]"
Leave a comment on this query.

Requests by seconds to process, with percentages

Number of requests that took how x seconds to process, rounded up to the nearest second, with what percent of requests that is.
Keywords:
  • iisw3c
Statement: 
logparser -rtp:-1 -i:iisw3c "SELECT ADD(DIV(QUANTIZE(time-taken,1000),1000),1) AS [Seconds], COUNT(*) AS [Requests], MUL(PROPCOUNT(*),100) AS [Percent] INTO RequestTimes.txt FROM ex1003*.log GROUP BY [Seconds] ORDER BY [Seconds]"
Leave a comment on this query.

Requests in the last 15 minutes

Pull a listing of requests within the last 15 minutes.
Keywords:
  • iisw3c
Statement: 
logparser -rtp:-1 -i:iisw3c "SELECT TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS [LocalTime], * INTO RecentRequests.txt FROM ex1008*.log WHERE LocalTime > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-01 00:15', 'yyyy-MM-dd HH:mm'))"
Notes: Change the created timestamp above as needed for more or less information. If you're planning on parsing out this data, make sure you use TO_STRING() to format the local time, as it'll contain a space and cause issues with further queries. Leave a comment on this query.

User accounts used in IIS 6 FTP login attempts

The following Log Parser query can be used on FTP log files in order to determine what user names were used to login, or attempt to login, to an FTP site.
Keywords:
  • iis6ftp
Statement: 
logparser -rtp:-1 "SELECT cs-uri-stem, count(cs-method) INTO FTPUserAccountAttempts.txt FROM ex*.log WHERE cs-method like '%USER' GROUP BY cs-uri-stem ORDER BY count(cs-method), cs-uri-stem"
Notes: See Using Log Parser to find users accounts used to log into an FTP site for more information. Leave a comment on this query.

IP addresses successfully logging into IIS 6 FTP site

This query will tell you what ip addresses successfully logged into your FTP site.
Keywords:
  • iis6ftp
Statement: 
logparser -rtp:-1 "SELECT c-ip, count(sc-status) INTO FTPSuccessfulIPLogins.txt FROM ex*.log WHERE sc-status = '230' GROUP BY c-ip ORDER BY count(sc-status), c-ip"
Notes: See Using Log Parser to find users accounts used to log into an FTP site for more information. Leave a comment on this 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"
Leave a comment on this query.

IP addresses attempting to log into an IIS 6 FTP site

This query will show you what ip addresses attempted to log into your FTP site, and will give a count of how many times.
Keywords:
  • iis6ftp
Statement: 
logparser -rtp:-1 "SELECT c-ip, count(*) INTO FTPIPLoginAttempts.txt FROM ex*.log GROUP BY c-ip ORDER BY count(*), c-ip"
Notes: See Using Log Parser to find users accounts used to log into an FTP site for more information. Leave a comment on this query.

Users logged into IIS 6 FTP site

Parses IIS 6 FTP logs for user accounts who have successfully logged in, and returns the IP(s) used and the number of times they logged in from those IPs.
Keywords:
  • iis6ftp
Statement: 
logparser -rtp:-1 "SELECT cs-username, c-ip, count(*) INTO FTPUsersLoggedIn.txt FROM ex1008*.log WHERE sc-status = '230' GROUP BY cs-username, c-ip ORDER BY count(*), cs-username, c-ip"
Notes: See Using Log Parser to find users accounts used to log into an FTP site for more information. Leave a comment on this query.

Requests for robots.txt with ip and user agent

This query grabs all requests for the robots.txt file, outputting ip address and user agents, with counts for each.
Keywords:
  • iisw3c
Statement: 
logparser -i:w3c -o:xml "SELECT c-ip AS [ClientIp], cs(user-agent) AS [ClientUserAgent], COUNT(*) AS [Requests] INTO robots.xml FROM u_ex1009*.log WHERE cs-uri-stem = '/robots.txt' GROUP BY ClientIp, ClientUserAgent ORDER BY Requests DESC"
Notes: This particular query outputs the data to XML, and renames columns because of that. See The benefits of using a file for Log Parser SQL queries for more information. Leave a comment on this query.

Request methods with ip and user agent

This query grabs all requests by method, excluding common GETs and POSTs. Returns ip address and user agents, with count for each, for determining whether any are testing vulnerabilities.
Keywords:
  • iisw3c
Statement: 
logparser -i:w3c -o:w3c "SELECT cs-method, c-ip, cs(User-Agent), COUNT(*) AS [Requests] INTO requestMethods.log FROM u_ex1010*.log WHERE cs-method NOT IN ('GET';'POST') GROUP BY cs-method, c-ip, cs(User-Agent) ORDER BY cs-method, Requests"
Notes: If POSTs aren't used on your site, you may want to remove that exclusion. Leave a comment on this query.

Bandwidth usage, with local dates

Returns bytes (as well as converted to KB and MB) received and sent, per date, for a Web site, with request date/time converted to local time.
Keywords:
  • iisw3c
Statement: 
logparser -i:w3c -o:csv "SELECT TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))) AS [LocalDate], 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.csv FROM u_ex1102*.log GROUP BY LocalDate ORDER BY LocalDate"
Notes: Tweaked version of 'Bandwidth usage' query, to use local times. Leave a comment on this query.

Request time

Returns the number of times a particular page (in this case .as* files) was hit, with the average, minimum, and maximum time taken, along with the standard deviation.
Keywords:
  • iisw3c
Statement: 
logparser -i:iisw3c -o:csv "SELECT TO_LOWERCASE(cs-uri-stem) AS csUriStem, COUNT(*) AS Hits, DIV ( MUL(1.0, SUM(time-taken)), Hits ) AS AvgTime, SQRROOT ( SUB ( DIV ( MUL(1.0, SUM(SQR(time-taken)) ), Hits ) , SQR(AvgTime) ) ) AS StDev, Max(time-taken) AS Max, Min(time-taken) AS Min, TO_REAL(STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus)))) AS Status, Min(TO_LOCALTIME(date)) AS LastUpdate INTO TimeTaken.csv FROM *ex1106*.log WHERE cs-uri-stem like '%.as%' GROUP BY TO_LOWERCASE(cs-uri-stem), TO_REAL(STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus)))) HAVING COUNT(*) > 2"
Notes: Query written and provided by Chevis L. Nelson (drcheeves [at] yahoo.com). Of particular note is StDev, which Chevis wrote to find the standard deviation with one query, and which has been tested against Microsoft SQL's STDEV function. Leave a comment on this query.

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]"
Leave a comment on this query.

Average bandwidth per second by half hour

Returns the average bandwidth, as KBytes per second, broken into 30 minute blocks.
Keywords:
  • iisw3c
Statement: 
logparser -i:iis "SELECT TO_LOCALTIME(QUANTIZE(time, 1800)) as HalfHour, ADD(SCKBytesSec, CSKBytesSec) as KBytesSec USING DIV(DIV(MUL(1.0, SUM(sc-bytes)), 1024), 1800) as SCKbytesSec, DIV(DIV(MUL(1.0, SUM(cs-bytes)), 1024), 1800) as CSKBytesSec INTO AvgKBytesPerSec.txt FROM *ex1106*.log GROUP BY HalfHour ORDER BY HalfHour ASC"
Notes: Query written and provided by Chevis L. Nelson (drcheeves [at] yahoo.com). Leave a comment on this query.

Average response time by half hour

Returns the average response time, in milliseconds, of a particular page (in this case .as* files) broken into 30 minute blocks.
Keywords:
  • iisw3c
Statement: 
logparser -i:iis "SELECT TO_LOCALTIME(QUANTIZE(time, 1800)) as HalfHour, AVG(time-taken) as Time INTO AvgResponse.txt FROM *ex1107*.log WHERE cs-uri-stem like '%.as%' AND sc-status < 400 GROUP BY HalfHour ORDER BY HalfHour ASC"
Notes: Query written and provided by Chevis L. Nelson (drcheeves [at] yahoo.com). Leave a comment on this query.

Number of errors by half hour

Returns the total number of errors, broken into 30 minute blocks.
Keywords:
  • iisw3c
Statement: 
logparser -i:iis "SELECT TO_LOCALTIME(QUANTIZE(time, 1800)) as HalfHour, COUNT(*) as Hits INTO ErrorCount.txt FROM *ex1107*.log WHERE sc-status > 399 GROUP BY HalfHour ORDER BY HalfHour ASC"
Notes: Query written and provided by Chevis L. Nelson (drcheeves [at] yahoo.com). Leave a comment on this query.

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"
Leave a comment on this query.

Requests by half hour

Total number of requests, broken into 30 minute blocks.
Keywords:
  • iisw3c
Statement: 
logparser -i:iis "SELECT TO_LOCALTIME(QUANTIZE(time, 1800)) as HalfHour, COUNT(*) as Hits INTO HalfHourRequestCount.txt FROM *ex1107*.log GROUP BY HalfHour ORDER BY HalfHour ASC"
Notes: Query written and provided by Chevis L. Nelson (drcheeves [at] yahoo.com). Leave a comment on this query.

HTTP status codes and sub-status

Returns a listing of status and sub-status codes, with number of requests returning each.
Keywords:
  • iisw3c
Statement: 
logparser -i:iis "SELECT STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS Status, Count(*) as Hits INTO StatusCodes.txt FROM *ex1107*.log GROUP BY Status ORDER BY Hits DESC"
Notes: Query written and provided by Chevis L. Nelson (drcheeves [at] yahoo.com). Leave a comment on this query.

Browsers accessing content, without operating system (image)

Returns a listing of browsers accessing content as a bar chart, based upon the number of requests made.
Keywords:
  • iisw3c
Statement: 
logparser -i:w3c -o:chart -chartType:barstacked "SELECT CASE strcnt(cs(user-agent),'Firefox') when 1 THEN 'Firefox' else case strcnt(cs(user-agent),'netscape') when 1 THEN 'netscape' else case strcnt(cs(user-agent),'AOL') when 1 THEN 'AOL' else case strcnt(cs(user-agent),'Opera') when 1 THEN 'Opera' else case strcnt(cs(user-agent),'Chrome') when 1 THEN 'Chrome' else case strcnt(cs(user-agent),'Mobile') when 1 THEN 'SmartPhone' else case strcnt(cs(user-agent),'Safari') when 1 THEN 'Safari' else case strcnt(cs(user-agent),'MSIE+5') when 1 THEN 'IE 5' else case strcnt(cs(user-agent),'MSIE+6') when 1 THEN 'IE 6' else case strcnt(cs(user-agent),'MSIE+7') when 1 THEN 'IE 7' else case strcnt(cs(user-agent),'MSIE+8') when 1 THEN 'IE 8' else case strcnt(cs(user-agent),'MSIE') when 1 THEN 'IE other' else case strcnt(cs(user-agent),'bot') when 1 THEN 'Bot' else case strcnt(cs(user-agent),'spider') when 1 THEN 'spider' else case strcnt(cs(user-agent),'PutHTTP') when 1 THEN 'PutHTTP' else case strcnt(cs(user-agent),'Mozilla/4.0') when 1 THEN 'Mozilla/4.0 other' ELSE 'Unknown' End End End End End End End End End End End End End End End End as Browser, COUNT(cs(User-Agent)) as Hits INTO Browsers.jpg FROM *ex1107*.log GROUP BY Browser ORDER BY Hits ASC"
Notes: Query written and provided by Chevis L. Nelson (drcheeves [at] yahoo.com). Leave a comment on this query.

Count of directory of files requested

Returns a count of the parent directory of all file requests.
Keywords:
  • iisw3c
Statement: 
logparser -i:w3c "SELECT COUNT(*) AS [Requests], EXTRACT_PATH(cs-uri-stem) AS [Path Requested] INTO PathCounts.txt FROM *ex1208*.log GROUP BY [Path Requested] ORDER BY [Requests] DESC"
Notes: Can be useful to determine if any directories can be removed, and if sorted ascending instead, possible exploit attempts. Leave a comment on this query.

SMTP usage with basic logs

Returns information from an IIS SMTP instance (Windows Server 2012) when all you have is basic logs.
Keywords:
  • smtp
Statement: 
logparser -rtp:-1 "SELECT c-ip, REVERSEDNS(c-ip) AS [ClientHostName], cs-method, COUNT(*) AS [Requests] INTO SmtpClients.txt FROM *ex2104*.log WHERE cs-method IN ('RCPT') GROUP BY c-ip, [ClientHostName], cs-method ORDER BY [Requests] DESC"
Notes: If you only have the very basic logs for an SMTP instance in Windows Server 2012, this may help determine usage. Leave a comment on this query.

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. Leave a comment on this 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"
Leave a comment on this query.

Requests, bandwidth, and last visit, by IP address and user agent.

Returns a listing of requests, 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"
Leave a comment on this query.

FileZilla Server logs to W3C extended

Converts FileZilla Server logs to W3C extended log format.
Keywords:
  • FileZilla Server
  • textline
  • 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. Leave a comment on this query.

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"
Leave a comment on this query.

blog comments powered by Disqus