Advice - LogParser
I want to start this post by stating that I'm a huge fan of the Microsoft LogParser tool. This tool is so useful and if you are dealing with log files, events, and/or file systems it is an absolute must have in your toolkit.
A great extension to this tool is Visual LogParser. If you like SQL Enterprise Manager (or SQL Query Analyser) then this interface to LogParser is extremely helpful.
Below are some of my more advanced LogParser techniques that I've collected over time
IIS Performance - The below query is perfect if you are trying to understand performance at a subfolder level. It will provide the Server, the URL stem, total pages and average time based on only pages that were successful.
set sDate=071107
cd "C:\Program Files\Log Parser 2.2"
LogParser.exe -i:IISW3C "select EXTRACT_TOKEN(LogFilename,2,'\\') as Server, TO_UPPERCASE(EXTRACT_PREFIX( cs-uri-stem, 1, '/')) AS URL, count(*) As PageTotal, avg(time-taken) as AvgTime From '\\server1\IISLogs\W3SVC1\ex%sDate%*.log', '\\server2\IISLogs\W3SVC1\ex%sDate%*.log' where sc-status = '200' group by EXTRACT_TOKEN(LogFilename,2,'\\'), TO_UPPERCASE(EXTRACT_PREFIX( cs-uri-stem, 1, '/')) order by TO_UPPERCASE(EXTRACT_PREFIX( cs-uri-stem, 1, '/')), EXTRACT_TOKEN(LogFilename,2,'\\')" -o:DATAGRID
IIS User Usage - Using the power of PSEXEC (psexec - I love this tool in that you can execute commands on remote servers without needing to copy and exe to that server) you can create a hosting server that schedules LogParser queries and unloads them to remote servers (the remote servers must have LogParser installed). Below are 2 batch files that allow you to run daily a LogParser query on a remote server and returning the results back to the scheduling server. This is perfect if you are running queries across the WAN.
Additionally the below batch file uses a Date/Time routine - depending on your IIS Log Files it can be better to query a specific log file using a date (e.g. ex080120.log) than using the LogParser CheckPoint features.
cd "C:\Program Files\BatchFiles"
c:
psexec \\server1 -c -u server1\username -p password
"C:\Program Files\BatchFiles\IISUsage\IISUsageDailyUsers.bat"
Below is the IISUsageDailyUsers.bat file
REM Set the Date / Time elements
For /f "tokens=1-7 delims=:/-, " %%i in ('echo exit^cmd /q /k"prompt $D $T"') do (
For /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^date')do (
set %%a=%%i
set %%b=%%j
set %%c=%%k
set hh=%%l
set min=%%m
set ss=%%n
)
)
if %yy%==2008 Set %YearStr=08
if %yy%==2009 Set %YearStr=09
cd "C:\Program Files\Log Parser 2.2"
C:
REM Page Statistics
LogParser.exe -i:IISW3C "select cs-username as Username, count(*) as ActivityCount INTO '\\ServerScheduling\IISUsage\Output%YearStr%%mm%%dd%.csv' from '\\server1\IISLogs\W3SVC1\ex%YearStr%%mm%%dd%*.log','\\server2\IISLogs\W3SVC1\ex%YearStr%%mm%%dd%*.log' where cs-username IS NOT NULL and c-ip like '10.1.0.%%' group by cs-username order by count(*) desc" -o:CSV
WLBS Status - Want to know whether both servers are still in the farm? Use this tool to provide NLB Status - done in 2 parts. First a batch file to retrieve the status into a log file, next part is a LogParser query to check.
Below is NLBStatus.cmd batch file
date /t
time /t
@wlbs query SERVERCLST /passw
It is called daily with a command like :-
call "C:\Program Files\BatchFiles\NLBStatus.cmd" >> "C:\Program Files\BatchFiles\Logs\NLB\NLBStatuslog_%yy%%mm%.log"
LogParser query :-
LogParser.exe -i:TEXTLINE "Select text FROM 'c:\Program Files\BatchFiles\Logs\NLB\*.log' WHERE Text LIKE '%converged%'" -o:DATAGRID -iCheckPoint:"C:\NLBCheckpoint.chk"
Get a response with less than the number of servers in the cluster then you know you have something wrong.
Fixed length files - In something like Visual LogParser you can view nicely (in Excel style) Fixed Length formatted files. Apply a query against TEXTLINE using SUBSTR to format the fields :-
SELECT SUBSTR(Text,1,2) As [Id], SUBSTR(Text,3,10) As [Name] FROM C:\FixedLengthFile.txt
The [..] fieldname syntax allows you to have space in the column names.
LogParser and Unicode files - Something interesting I got caught with is how LogParser deals with CheckPoint files when parsing Unicode files. The solution around this is if using Unicode files to specify the iCodePage=-1 paramater. e.g.
"c:\Program Files\Log Parser 2.2\LogParser.exe" "select EXTRACT_FILENAME(LogFileName), text FROM 'UnicodeFile.log' where text like '%%FINDSOMETHING%%'" -i:TextLine -o:DataGrid -iCheckPoint:"C:\checkpoint.chk" -iCodePage=-1
A great extension to this tool is Visual LogParser. If you like SQL Enterprise Manager (or SQL Query Analyser) then this interface to LogParser is extremely helpful.
Below are some of my more advanced LogParser techniques that I've collected over time
IIS Performance - The below query is perfect if you are trying to understand performance at a subfolder level. It will provide the Server, the URL stem, total pages and average time based on only pages that were successful.
set sDate=071107
cd "C:\Program Files\Log Parser 2.2"
LogParser.exe -i:IISW3C "select EXTRACT_TOKEN(LogFilename,2,'\\') as Server, TO_UPPERCASE(EXTRACT_PREFIX( cs-uri-stem, 1, '/')) AS URL, count(*) As PageTotal, avg(time-taken) as AvgTime From '\\server1\IISLogs\W3SVC1\ex%sDate%*.log', '\\server2\IISLogs\W3SVC1\ex%sDate%*.log' where sc-status = '200' group by EXTRACT_TOKEN(LogFilename,2,'\\'), TO_UPPERCASE(EXTRACT_PREFIX( cs-uri-stem, 1, '/')) order by TO_UPPERCASE(EXTRACT_PREFIX( cs-uri-stem, 1, '/')), EXTRACT_TOKEN(LogFilename,2,'\\')" -o:DATAGRID
IIS User Usage - Using the power of PSEXEC (psexec - I love this tool in that you can execute commands on remote servers without needing to copy and exe to that server) you can create a hosting server that schedules LogParser queries and unloads them to remote servers (the remote servers must have LogParser installed). Below are 2 batch files that allow you to run daily a LogParser query on a remote server and returning the results back to the scheduling server. This is perfect if you are running queries across the WAN.
Additionally the below batch file uses a Date/Time routine - depending on your IIS Log Files it can be better to query a specific log file using a date (e.g. ex080120.log) than using the LogParser CheckPoint features.
cd "C:\Program Files\BatchFiles"
c:
psexec \\server1 -c -u server1\username -p password
"C:\Program Files\BatchFiles\IISUsage\IISUsageDailyUsers.bat"
Below is the IISUsageDailyUsers.bat file
REM Set the Date / Time elements
For /f "tokens=1-7 delims=:/-, " %%i in ('echo exit^cmd /q /k"prompt $D $T"') do (
For /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^date')do (
set %%a=%%i
set %%b=%%j
set %%c=%%k
set hh=%%l
set min=%%m
set ss=%%n
)
)
if %yy%==2008 Set %YearStr=08
if %yy%==2009 Set %YearStr=09
cd "C:\Program Files\Log Parser 2.2"
C:
REM Page Statistics
LogParser.exe -i:IISW3C "select cs-username as Username, count(*) as ActivityCount INTO '\\ServerScheduling\IISUsage\Output%YearStr%%mm%%dd%.csv' from '\\server1\IISLogs\W3SVC1\ex%YearStr%%mm%%dd%*.log','\\server2\IISLogs\W3SVC1\ex%YearStr%%mm%%dd%*.log' where cs-username IS NOT NULL and c-ip like '10.1.0.%%' group by cs-username order by count(*) desc" -o:CSV
WLBS Status - Want to know whether both servers are still in the farm? Use this tool to provide NLB Status - done in 2 parts. First a batch file to retrieve the status into a log file, next part is a LogParser query to check.
Below is NLBStatus.cmd batch file
date /t
time /t
@wlbs query SERVERCLST /passw
It is called daily with a command like :-
call "C:\Program Files\BatchFiles\NLBStatus.cmd" >> "C:\Program Files\BatchFiles\Logs\NLB\NLBStatuslog_%yy%%mm%.log"
LogParser query :-
LogParser.exe -i:TEXTLINE "Select text FROM 'c:\Program Files\BatchFiles\Logs\NLB\*.log' WHERE Text LIKE '%converged%'" -o:DATAGRID -iCheckPoint:"C:\NLBCheckpoint.chk"
Get a response with less than the number of servers in the cluster then you know you have something wrong.
Fixed length files - In something like Visual LogParser you can view nicely (in Excel style) Fixed Length formatted files. Apply a query against TEXTLINE using SUBSTR to format the fields :-
SELECT SUBSTR(Text,1,2) As [Id], SUBSTR(Text,3,10) As [Name] FROM C:\FixedLengthFile.txt
The [..] fieldname syntax allows you to have space in the column names.
LogParser and Unicode files - Something interesting I got caught with is how LogParser deals with CheckPoint files when parsing Unicode files. The solution around this is if using Unicode files to specify the iCodePage=-1 paramater. e.g.
"c:\Program Files\Log Parser 2.2\LogParser.exe" "select EXTRACT_FILENAME(LogFileName), text FROM 'UnicodeFile.log' where text like '%%FINDSOMETHING%%'" -i:TextLine -o:DataGrid -iCheckPoint:"C:\checkpoint.chk" -iCodePage=-1
posted by Gluegood Software at 11:16 PM
2 Comments:
Dear Sir
thanks for the nice post...
I need your help if you don't mind.
Actually I’m administering exchange 2007 servers and I want to generate a weekly report from the IIS log on our Exchange Client Access Server (CAS) about OWA activities. I have the following difficulties:
We have 2 CAS servers (load Balance). This means that the connections to OWA will be distributed on the 2 servers based on the load. So I need to gather the info from the two server to generate a report. As you know that the IIS will create log for each day under this path c:\windows\system32\logfiles\w3svc1. I want to schedule a job to run at the end of the week to parse all week logs and generate the report. i.e. if today is 25-Jan-10, the query should process all logs generated in the last 7 days from 19 to 25 Jan 2010.
I'm using logparser to generate such report but I don’t know how to accomplish the following:
1- how can I combine the data from the two servers? i.e. parse the logs from the two servers for the same interval (last 7 days).
2- how to specify one week range in the logparser query? i.e. parse only the file for the last 7 days. if today is 25-Jan-2010, the query should process all logs generated in the last 7 days from 19 to 25 Jan 2010 (ex100119,ex100120,ex100120...ex1001205). I don't want to hardcode the date in the query.
3- how can I schedule this query to run on a certain day to generate the log. Shall I user batch file. then use windows schedule task wizard to run the batch at then end of the week?
-----------------------------------
here is an example to generate an OWA Hourly Hits chart , but I don’t know how to achieve the above:
logparser.exe "SELECT TO_STRING(time, 'HH') AS Hour, COUNT(*) AS Hits INTO hitPerSecond.jpg FROM ex100119.log GROUP BY Hour
ORDER BY Hour ASC" -i:IISW3C -o:CHART -chartType:ColumnClustered -chartTitle:"Hourly Hits" -groupSize:420x280 -view:on
-----------------------------------
i want to modify the above query to achieve the above point and run it every weekend.
thanks in advanced for your help...
This posting was very helpful to me as I am trying to parse fixed length records. Thanks!
Post a Comment
<< Home