The following information was submitted in the past by customers. I'm reposting it here as-is. It contains various samples for using logparser.
Time-Constrained Queries
Here's a way to slice queries by time:
LogParser "SELECT
EventLog, RecordNumber, TimeGenerated, EventID, EventType, EventTypeName,
EventCategory, SourceName, ComputerName, Message FROM System, Application,
Security TO tblEvents WHERE TO_STRING(TimeGenerated, 'yyyyMMdd') =
TO_STRING(TO_LOCALTIME(SYSTEM_TIMESTAMP()), 'yyyyMMdd') AND
TO_INT(TO_STRING(TimeGenerated, 'HH')) =
SUB(TO_INT(TO_STRING(TO_LOCALTIME(SYSTEM_TIMESTAMP()), 'HH')),1)" -o:SQL -server:SQLSERVER
-driver:"SQL Server" -database:EventLogs -username:sa -password:XXXXX -createtable:ON
This way, for example, whether I run the command at 9:00:00 or 9:15 or 9:45 or 9:59:59, it will always get the events from 8:00:00 – 8:59:59.
Microsoft Print Server Reports
LogParserPrintJobReports.zip contains example reports for Microsoft Print Server print jobs.
Example output:
FileOwner FileSizeTotal PagesPrintedTotal
ACVP00QA 79546825 34632
btdfci 1161198955 32379
qtyan 1193090146 22827
dgyuard 692137473 19488
ABVP00QW 28372018 16378
maklare 694898256 14711
ASVP00EW 12829138 14048
DatePrinted FileSizeTotal PagesPrintedTotal
2005-07 1937151412 23786
2005-08 4813694697 71478
2005-09 4256144126 70289
2005-10 5434451891 69645
2005-11 5157293144 71456
2005-12 4436677005 53183
2006-01 5747978162 81802
2006-02 5403575457 74912
2006-03 6452139823 64490
ISA Logs
Here is how to get a summary of a single User's web traffic from ISA 2000 with extended logs. This query is getting all of the transactions for mydomain\porncruzeruser during Jan and February 2006.
logparser "SELECT c-ip,TO_LOWERCASE(cs-username) As User,date,time,r-host,r-ip into '%userprofile%\desktop\report.csv' FROM 'c:\Program Files\Microsoft ISA Server\ISALogs\WEBEXTD200602*.log','c:\Program Files\Microsoft ISA Server\ISALogs\WEBEXTD200601*.log' where User='mydomain\porncruzeruser'" -i:w3c -o:CSV
VB6/SQL Sample
Sample how to use COM-Object in VB6 with ODBC- DSN-Entry as SQL-Server Input
LogParser "SELECT * Into in200511.Log FROM IISReportParser" -i:COM -iProgID:ComParserSql.Application
-iCOMParams:User=sa;Pwd=sa;Where="ServiceInstance='W3SVC116' AND
Report='2005-11' " -o:IIS
LogParserRegexInputFormat
LogParserRegexInputFormat is a C# COM Plugin I wrote in VS 2005 that parses log files using an arbitrary regex and field type definitions configured in an XML file.
Log Parser GUI
I am one of those who are astounded by the power of LogParser. I think a lot of people have not discovered it because they are put off by the command-line interface.
I am attaching a gui I have created in C# along the principle of KISS. I realize it is pretty basic but I see it as a starting point. Based on whatever feedback/assistance/tips I receive, I will develop it further. I am providing all the code in the hope that anyone who sees errors or ways of improving it will help improve it.
Where Clauses at the Command Line
As I write .sql files, I find that using a parameter like %logfiles% makes them much more useful – I think this is fairly common. My trick is that if you use quotes appropriately, you can sneak your where clause on the end. Example:
SelectAll.sql:
Select * from %logfile%
cmd-line statement:
logparser file:SelectAll.sql?logfile=”ex*.log where cs-uri-stem like ‘%.aspx’
LogParser Editor
A while back I created a little front end to LogParser (in VB) and haven't been able to get back to it. I thought I would send the application to you just in case somebody wanted to use it or rewrite it and make it useable.
EventArchiver
EventArchiver is a C# app i've written, its still alpha (some little bad inputs can crash it, still need to track these down, as long as user doesn't do anything silly it should work) but the major functionality is there. This program is much more in depth.
It has 3 modes: Configuration, command line, batch.
The configuration mode lets you configure it to operate with MS SQL (set database name, user name, password, server, the password is encrypted by Rijndael 256 bit encryption)
Once the SQL configuration is complete you can then either run through command line or through batch mode.
To configure batch mode, once again enter configuration mode and go to s) Configure SQL batch mode.
Here you can add servers, add credentials and link servers to credentials.
The two output methods configured are CSV & SQL.
Accepts the following switches:
Downloads event logs from local or remote host and outputs to either a csv file
or an sql table.
EventArchiver [/?] [/h:host] [/o:outputType] [/d:destination] [/i:path]
[</u:username> </p:password>] [/b] [/c[:config]] [/f[:config]]
/? Brings up this screen.
/h Specify host to connect to, if omitted will assume local.
/o Specifies whether to output to SQL or to CSV file,
if omitted will assume CSV file.
outputType SQL - sql output format (default "TestSystems")
CSV - csv output format (default "c:\")
/d Specifies destination table (SQL) or directory (CSV).
destination <table name> used for SQL format specified with /o switch.
<directory name> used for CSV format specified with /o switch.
/i Specifies destination path for checkpoint files
/u Specify username (for domain accounts specify as DOMAIN\user).
/p Specify password.
/b Batch mode - uses SQL database for servers & credentials.
/c Config mode - to configure SQL settings.
config <filename> - Configuration file name.
/f Use configuration file. (required for /o:SQL)
config <filename> - Configuration file name.
[] represents optional parameters, <> represents required paramters
switches can be specified using '-' or '/'
Some limitations, currently you cannot delete servers, credentials or batched runs, i haven't had time to add this yet. Currently the only way to do this is to go into SQL
Both programs (EventArchiver and PullLogs) will come packaged with some SQL scripts required to setup associated databases, the c# app can also be used to configure the database that the script will run off.
Pulling Event Logs
this utility will pull all the event logs from a remote machine to the local computer.
Using Log Parser from C#
try
{
Type comLogQueryType = Type.GetTypeFromProgID("MSUtil.LogQuery", true);
object comLogQueryObject = Activator.CreateInstance(comLogQueryType);
// Get the IIS Input and XML output filters
Type ws3LogType = Type.GetTypeFromProgID("MSUtil.LogQuery.IISW3CInputFormat", true);
object ws3LogObject = Activator.CreateInstance(ws3LogType);
Type xmlLogType = Type.GetTypeFromProgID("MSUtil.LogQuery.XMLOutputFormat", true);
object xmlLogObject = Activator.CreateInstance(xmlLogType);
// Setup input and output files
string inPath = "someIISlog.log";
string outpath = "temp.xml";
// Create a SQL query to get the referers, count and uri-to. Order by total hits
string query="SELECT cs(Referer) as Referer,cs-uri-stem as To,COUNT(*) as Total from "+inPath+" TO "+outpath+" \
WHERE (sc-status=200) AND (Referer LIKE 'http:%') GROUP BY Referer,To ORDER BY Total DESC";
// Invoke the ExcuteBatch method
object[] inputArgs = {query, ws3LogObject, xmlLogObject};
comLogQueryType.InvokeMember("ExecuteBatch", BindingFlags.InvokeMethod, null, comLogQueryObject, inputArgs);
}
catch(Exception e)
{
string errorString = "An exception has occurred: " + e.Message;
Console.WriteLine(errorString);
}
This will create an XML file that has your uri's that are refered to, hit count, and the referer.
Total Traffic Over a Time Period For Multiple IIS Sites
Scenario: I needed to determine the total amount of traffic over a time period for multiple sites on a Windows 2003 server. This entailed looping over all the sites in IIS, summarizing the bytes served for each site between two dates, then storing the results in a table for display and subsequent onward processing.
LogParser's recordset object as output was the obvious way to go instead of storing the various results in a bunch of separate XML/CSV files for each site. However, examples of this approach - especially using date ranges - were pretty rare and somewhat unintuitive to get working, so here's my take on it (tested on Server 2003 & XP only):
First, add a COM reference to the project. Browse to the LogParser install location and select LogParser.dll (the default location is C:\Program Files\IIS Resources\Log Parser). This creates a COM wrapper namespace called MSUtil. You will also need:
----------------------------------
using System.Data;
using System.DirectoryServices;
using System.Globalization;
----------------------------------
Next, create a holding table somewhere for the data:
----------------------------------
private DataTable tblTraffic = new DataTable(); private DataColumn dcSite = new DataColumn("Site"); private DataColumn dcBytes = new DataColumn("Bytes"); tblTraffic.Columns.Add(dcSite);
tblTraffic.Columns.Add(dcBytes);
tblTraffic.DefaultView.Sort = "Site";
----------------------------------
Then run the core action routine as follows (assumes two datepicker controls for the From & To dates, and a datagrid for display):
----------------------------------
// create the LogParser object and associated IISW3C input object MSUtil.LogQueryClassClass LogParser = new MSUtil.LogQueryClassClass(); MSUtil.COMIISW3CInputContextClassClass IISlog = new MSUtil.COMIISW3CInputContextClassClass();
// get the user-chosen date ranges from datepicker controls DateTime dtFrom = dtPickerFrom.Value; DateTime dtTo = dtPickerTo.Value;
// create date format to match LogParser Timestamp field string strFormat = "yyyy-MM-dd";
// create From & To date strings in Timestamp format string strDateFrom = dtFrom.ToString(strFormat,
DateTimeFormatInfo.InvariantInfo) + " 00:00:00"; string strDateTo = dtTo.ToString(strFormat,
DateTimeFormatInfo.InvariantInfo) + " 23:59:59";
// find IIS sites properties via ADSI
DirectoryEntry root = new DirectoryEntry("IIS://localhost/W3SVC");
foreach(DirectoryEntry de in root.Children) {
if (de.SchemaClassName == "IIsWebServer")
{
string strSiteName = de.Properties["ServerComment"][0].ToString();
// get numeric site identifier for use in FROM clause
string strSiteID = de.Name.ToString();
string strSQL = "SELECT SUM(sc-bytes) AS total "
+ "FROM <" + strSiteID + "> "
+ "WHERE TO_TIMESTAMP(date, time) >= "
+ "TO_TIMESTAMP('" + strDateFrom + "', 'yyyy-MM-dd hh:mm:ss') "
+ "AND TO_TIMESTAMP(date, time) <= "
+ "TO_TIMESTAMP('" + strDateTo + "', 'yyyy-MM-dd hh:mm:ss') ";
// prepare LogParser Recordset & Record objects
MSUtil.ILogRecordset rsLP = null;
MSUtil.ILogRecord rowLP = null;
// run the query against the IIS log for this site
rsLP = LogParser.Execute(strSQL,IISlog);
rowLP = rsLP.getRecord();
// populate holding table with site name & summary bytes for the period
DataRow dr = tblTraffic.NewRow();
dr[0] = strSiteName;
dr[1] = rowLP.getValue(0);
tblTraffic.Rows.Add(dr);
}
}
// show results in grid
dgDisplay.DataSource = tblTraffic.DefaultView;
----------------------------------
This produces something like:
Site Bytes
------- -------
Administration 304566
BasicHackers 56438387
CSharpCoders 46567439
Searches on a SharePoint Site
Here is a little LogParser SQL that I found useful in determining what users were searching for on our Intranet use SPS 2003 Search.
I call it via "C:\Program Files\IIS Resources\Log Parser\LogParser" -o:csv file:search.sql
search.sql:
SELECT DISTINCT
TO_UPPERCASE(EXTRACT_VALUE(cs-uri-query, 'k')) AS SearchString,
EXTRACT_VALUE(cs-uri-query, 's') AS Scope, COUNT(*) AS HowMany
FROM *.log
TO search.csv
WHERE cs-uri-stem = '/search.aspx'AND cs-uri-query NOT LIKE
'%[Microsoft+Office+SharePoint+Portal+Server+2003+LOG]%' AND SearchString IS
NOT NULL
GROUP BY SearchString, Scope
ORDER BY HowMany DESC
Restarting a malfunctioning application
Here is a script in VB Script to address a problem that we had with a malfunctioning IIS application.
I execute the script every 10 minutes to check the status of the IIS server.
The script exams the IIS log to find rows with sc-status 200 and 500 written in the last 5 minutes.
If the percentage of requests with sc-status 500 reaches a critical level the script then restarts the W3SVC service.
' Restarts IIS when errors start appearing
Option Explicit
Dim oShell, oWMIService, oLogQuery, oIISW3CInputFormat
Dim oRecordSet, oRecord, oService
Dim strComputer, strQuery, perCentErrors, totalCorrect, flagStarted
Dim colServiceList, errReturn
Const perCentErrorThreshold = 10.0
Set oShell = WScript.CreateObject("WScript.Shell")
strComputer = "."
Set oWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate, (Security)}!\\" & _
strComputer & "\root\cimv2")
Set oLogQuery = CreateObject("MSUtil.LogQuery")
Set oIISW3CInputFormat = CreateObject("MSUtil.LogQuery.IISW3CInputFormat")
'Create CheckPoint file
oIISW3CInputFormat.iCheckPoint = "RecupWebApp.lpc"
'Create Query. Exams log in last 5 minutes for sc-status 200 and 500
'Returns 2 or less rows
strQuery = "SELECT sc-status, MUL(PROPCOUNT(*), 100.0), COUNT(*) FROM
<1> WHERE "
strQuery = strQuery & "SUB(TO_INT(TO_LOCALTIME(SYSTEM_TIMESTAMP())),"
strQuery = strQuery & "TO_INT(TO_LOCALTIME(TO_TIMESTAMP(date,time)))) < 300"
strQuery = strQuery & " AND (sc-status = 200 OR sc-status = 500) GROUP
BY sc-status"
'Execute query
Set oRecordSet = oLogQuery.Execute(strQuery,oIISW3CInputFormat)
Do While Not oRecordSet.atEnd
Set oRecord = oRecordSet.getRecord
If oRecord.getValue(0) = 500 Then
perCentErrors = oRecord.getValue(1)
totalErrors = oRecord.getValue(2)
Else
'cs-status = 200
totalCorrect = oRecord.getValue(2)
End If
oRecordSet.MoveNext
Loop
'Write information to application event log
If perCentErrors > 0 Then
oShell.LogEvent 0, "WebApp: Errors " & totalErrors & _
" of a total of " & (100 * totalErrors)/perCentErrors
Else
oShell.LogEvent 0, "WebApp: Errors 0 of a total of " & totalCorrect
End If
'Restart W3SVC if there are too many errors.
If perCentErrors > perCentErrorThreshold Then
ReStartIIS
End If
oRecordSet.close
Sub ReStartIIS
flagStarted = "n"
Set colServiceList = oWMIService.ExecQuery("SELECT * FROM
Win32_Service WHERE Name='W3SVC'")
'Stop IIS
For Each oService In colServiceList
errReturn = oService.StopService()
'Write information to application event Log
oShell.LogEvent 2, "IIS stopped due to error in WebApp with code " & errReturn
'Wait a while
WScript.Sleep 55000
Do While flagStarted = "n"
'Wait a bit more
WScript.Sleep 5000
errReturn = oService.StartService()
'Test to see if w3svc starts OK, if not try again 5 sec late
If errReturn = 0 Then
flagStarted = "y"
End If
Loop
'Write information to application event Log
oShell.LogEvent 0, "IIS Restarted"
Next
End Sub
EventLogs.Zip
This zip file, contains a Log Parser script and supporting files. He says "I'd like to have a scheduled event executed every morning at 6:00AM. This event will backup all the event logs for the previous day and then convert the information to something that is easily readable. When I get into work, I can quickly look through a list of events and focus on those that need my attention. I'm still working on this, but here's what I've got so far."
HomeFolderReport.Zip
In this zip file:
A batch file that returns a report on a folder of these three values.
1.) The top 10 largest files
2.) The top 20 largest files that have not been written to in a year
3.) The top 10 largest duplicate files.
We have many users with LARGE home folders and I hope to use this to give them a tool to clean house. The way it works is put the batch file anywhere. The tpl files need to go in the Log Parser folder and a c:\log files folder needs to exist. The batch file works off 2 parameters; The location of the folder and a name for report. The syntax would;
HFR FolderName-(usually the name of the users home folder) UserName
Ex. HFR M:\ksowers ksowers
This will return a report of the m:\ksowers folder called ksowers_HFR.html in the c:\log files folder
LogParser COM input plugin sample
this sample contains a COM input plugin to read some proprietary binary logs. The plugin itself won't be useful to anyone but it's a good sample for those who want to write one of their own.
Split IIS Logs
I use this console app to split the IIS logs using Host Headers into individual log files with directory name the domain name. This was a requirement for using LiveStats.
using System;
using MSUtil;
using System.Collections;
using System.IO;
namespace LogSplitter
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
MSUtil.LogQueryClassClass Log = new MSUtil.LogQueryClassClass();
MSUtil.COMIISW3CInputContextClass InputW3C = new MSUtil.COMIISW3CInputContextClass();
MSUtil.COMW3COutputContextClass OutputW3C = new MSUtil.COMW3COutputContextClass();
//record Set
MSUtil.ILogRecordset Record = null;
MSUtil.ILogRecord row = null;
string strSourceFile = "";
if (args.Length == 0)
{
strSourceFile = "ex";
System.DateTime dtNow = System.DateTime.Now;
strSourceFile += dtNow.Year.ToString().Substring(2,2);
string strMonth = dtNow.Month.ToString();
if (dtNow.Month < 10) strMonth = "0" + strMonth;
strSourceFile += strMonth;
strSourceFile += (dtNow.Day-1).ToString();
strSourceFile += ".log";
}
else
strSourceFile = args[0];
string QUERY1 = "Select distinct cs-host from " + strSourceFile + " order by cs-host";
Record = Log.Execute(QUERY1,InputW3C);
ArrayList arDomains = new ArrayList(500);
ArrayList arLogs = new ArrayList(500);
while (!Record.atEnd() )
{
//print
row = Record.getRecord();
string RowColumns = row.getValue(0).ToString().ToLower();
arDomains.Add(RowColumns);
Record.moveNext();
}
foreach(string strItem in arDomains)
{
//remove www.
string strDomain = strItem.Replace("www.","");
//do each one once for all combinations of domain
if (arLogs.Contains(strDomain) == false)
{
//create subdir
DirectoryInfo di = new DirectoryInfo(strDomain);
if (di.Exists == false)
di.Create();
QUERY1 = "select * into " + strDomain + "\\" + strSourceFile + " from " + strSourceFile + " where cs-host = '"+ strDomain + "' OR cs-host = 'www." + strDomain + "'";
Log.ExecuteBatch(QUERY1,InputW3C, OutputW3C);
arLogs.Add(strDomain);
}
}
}
}
}