Top 10 Topics for MSCOM Ops…Pull Out the Swiss Army Knife... (Logparser That Is)... The TOOL MAN Cometh!! (Part 4)
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.