One of the most useful tools that we use in MSCOM Ops has to be LogParser. I can’t say enough about this tool. We use it to parse IIS Logs, NetMon logs, NT Event logs, HTTP… in fact I can’t think of log that I ever met that LogParser could not parse. It is truly the Swiss Army Knife of parsers. https://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07\&displaylang=en
Here is some quick LogParser info:
How to get Help
· For IIS W3C Logs
logparser –h -i:iisw3c
· For Event Logs
logparser –h –i:evt
· For File System properties
logparser –h –i:fs
· For URLScan logs
Logparser –h –i:urlscan
· For HTTP Error logs
Logparser –h –i:httperr
And some real world examples:
This query parses IIS Logs and returns specific information. In this case the client URI Stem and Referer; the http status, it gives you the count of all of these grouped by the Referer value, then Stem value, then Status. It pulls this information from a specific server and saves the output to a SQL server. We usually configure IIS to do hourly IIS logs, so the wild card * after the log file ex4042* will give you this information for all of the log files in April, 2005 for the all the hours on days that start with 2…i.e., April 20-29.
Returns Values For URI Stem, Referer and Status, Groups and Counts Results, Stores in SQL Server
logparser.exe -i:iisw3c -o:SQL -server:SQLServerName -driver:"SQL Server" –database:DatabaseName -username:SQLUserName -password:SQLUserPassword -createtable:ON "Select cs-uri-stem as STEM, cs(Referer) as Referer, sc-status as Status, count(*) as Count from \\servername\logdriveletter$ :\wwwlog\w3svc1\ex04052*.log TO RegLogReferer WHERE STEM LIKE 'URIStemValue%' group by Referer, Stem, Status"
I am not going into this much detail with the rest of these query examples but you should get the idea. The LogParser Help is very useful.
Here are a bunch of examples. Note: where you see text in italic you need to supply the value.
Returns MyDoom type requests
for /f %i in ('text file with your server names) do logparser -i IISW3C "SELECT count(*) FROM \\%i\drive letter\wwwlog\W3SVC1\\LogFileName.log WHERE cs(User-Agent) IS NULL AND cs-uri-stem = '/default.asp' AND cs-version = 'HTTP/1.1' AND cs-method = 'GET' AND cs-host = 'host value:80' "
Returns the count for each IIS status code
logparser "select sc-status, count(*) from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log group by sc-status order by count(*) desc
Returns status code counts for each file downloaded
logparser.exe "select cs-uri-stem, sc-status, count(*) from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log group by sc-status, cs-uri-stem order by cs-uri-stem asc
Returns each log entry where the HTTP status code is greater than 400
logparser "SELECT cs-uri-stem, sc-status FROM \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log WHERE sc-status>=400"
Returns IIS request made from a specific IP
logparser "select * from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName ex04062112.log where c-ip = 'IP_Address"
Returns the count an total bytes for each of the listed extensions
logparser -q:on "SELECT distinct TO_LOWERCASE(SUBSTR(cs-uri-stem, ADD(LAST_INDEX_OF(cs-uri-stem, '.'),1))) AS Extension, COUNT(*) AS CNT2, SUM(sc-bytes) AS CNT FROM \\ServerName\DriveLetter$\wwwlog\w3svc1\LogFileName.log where Extension IN ('htm';'html';'txt';'js';'css';'xml';'xsl';'htc';'doc';'ini';'dat') GROUP BY Extension ORDER BY CNT"
Returns the count of each extension in Drive Letter:\http
logparser -i:fs "SELECT TO_LOWERCASE(SUBSTR(Name, LAST_INDEX_OF(Name,'.'), STRLEN(Name))) AS Extension, Count(*) as Files from \\ServerName\DriveLetter$\http\*.* WHERE Attributes NOT LIKE 'D%' GROUP BY Extension ORDER BY Files DESC" -rtp:-1
Returns the total bytes sent per port
logparser -q:on "select s-port, sc-status, count(*), sum(sc-bytes) from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log where sc-status = 200 group by sc-status, s-port
Returns a list of all URL requests for port 443
logparser -q:on "select s-port, cs-uri-stem from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log where s-port = 443 group by cs-uri-stem, s-port
Returns the total bytes sent for each URL for Port 443
logparser -q:on "select s-port, cs-uri-stem, sum(sc-bytes) from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log where s-port = 443 group by cs-uri-stem, s-port
Returns a list of time take for all URLs ending in .mspx
logparser -rtp:-1 "select cs-uri-stem, time-taken from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log where cs-uri-stem like '%.mspx'"
Returns the average time taken for all URLs in the logfile
logparser "select avg(time-taken) from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log"
Returns the average time taken for each URL
logparser -rtp:-1 "select cs-uri-stem, avg(time-taken) from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log group by cs-uri-stem"
Returns the average time taken for each URL ordered by time taken
logparser -rtp:-1 "select cs-uri-stem, avg(time-taken) as RSPSE from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log group by cs-uri-stem order by RSPSE"
Returns the average, minimum and maximum time taken for each URL ordered by time taken
logparser -rtp:-1 "select cs-uri-stem, avg(time-taken) as RSPSE, min(time-taken), max(time-taken) from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log group by cs-uri-stem order by RSPSE"
Returns all fields for URLs starting with / your_value
logparser -q:on -recurse:on "select * from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log where cs-uri-stem like '/your value/%'"
Returns a count of all HTTP Status Code in a datagrid format
logparser.exe "select sc-status as HTTP_Status_Code, count(*) as Count from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log group by sc-status order by count(*) desc" -o:datagrid
Reports all EventID 1077 entries in the System Event Log
logparser -i:evt "select * from \\ServerName\System where EventID = 1077 order by TimeWritten desc"
Reports all event is the System Event Log and writes it to a file
logparser "Select TO_DATE(TimeGenerated) as date, TO_TIME(TimeGenerated) as time, EventID as event-id, EventType as event-type, SourceName as sourcename FROM System TO c:\temp\exevent.log" -o:W3C
Crawls the Filesystem and returns a descending list of counts of each filetype
logparser "SELECT SUBSTR(TO_LOWERCASE(name), ADD(LAST_INDEX_OF(name, '.'),1)) as FileType, count(*) as NumFiles from d:\http\*.* where size > 0 group by FileType order by NumFiles desc" -i:FS
Crawls the Filesystem and returns a descending list of total sizes by filetype
logparser "SELECT SUBSTR(TO_LOWERCASE(name), ADD(LAST_INDEX_OF(name, '.'),1)) as FileType, sum(size) as TotalSize from c:\temp\*.* where size > 0 group by FileType order by TotalSize desc" -i:FS
Quantize Queries
logparser "select quantize(time-taken,5), count(*) from \\ServerName\DriveLetter$\wwwlog\w3svc1\\LogFileName.log where cs-uri-stem = '/foo/footest.aspx' group by quantize(time-taken,5) order by quantize(time-taken,5)"
logparser “select quantize(TO_TIMESTAMP(Date,Time), <interval in seconds>) AS Second, count(*) as Hits group by Second”
Netmon Queries
logparser -i:netmon -o:w3c "select * from capture.cap to slashrequests.log where payload like 'GET%' and EXTRACT_TOKEN(Payload, 1, ' ') = '/'"
logparser -i:netmon -o:w3c "select EXTRACT_TOKEN(SUBSTR(Payload, ADD(INDEX_OF(Payload, 'User-Ag'), 12),80), 0, '..') as UserAgent, count(*) as Total from capture.cap to UserAgent_NoAcceptLanguage_SlashReq.log where payload like 'GET%' and EXTRACT_TOKEN(Payload, 1, ' ') = '/' and not payload like '%MSBN%' and not payload like '%My AppName%' and not payload like '%Accept-Language%' group by UserAgent order by Total desc"
Comments
- Anonymous
October 19, 2005
quick plug for www.logparser.com where Gabriele Giuseppini hangs out a lot - get your answers from the source. - Anonymous
October 19, 2005
Absolutly!!! www.logpaser.com has a wealth of information. Great site and an awesome tool.