Search results matching tag 'logparser Documentation'

How to now the members of local Administrators group in Windows 2003

gsiqadm — Mon, 29 Jun 2009 12:15:17 GMT

Hi all,

My company Policies say that I have to at all times see who use's the administrator accounts. I’ve been using Logparser to import Securitylog and Applicationslog (for the SQL server) into a SQL server. I’m using MS Access to extract reports. This is ok.

The problem is to know at all times who is in the group Local Administrators. Normally this is known users, but in this hacker world you never no. So I wish to, someway, to import into an SQL table of all the users of Local Administrators, either they are Domain users or local users. Hope someone has any ideas by using Logparser combined with WMI.

Thanks

Gsiqadm

Just Bought Book (new) Log Parser Toolkit by Giuseppini - Looking for new script download site

Ed Grossheim — Fri, 27 Feb 2009 23:53:39 GMT

5-49 pm 02-27-09

The book says 4 e-booklets are available, and the scripts for the book. You must register at "syngress.com" which does not exist anymore

You are redirected to http://www.elsevierdirect.com/ which yields no leads upon entering one search phrase after another.

Google yields nothing.

Would anyone know or offer to send me them?

Thanks, Ed

logparser samples

pharr — Fri, 27 Feb 2009 21:41:08 GMT

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);
                        }
                  }
 
            }
      }
}

General logparser information

pharr — Fri, 27 Feb 2009 21:39:02 GMT

The following information was submitted in the past by customers. I'm reposting it here as-is. It contains some general informaiton and some troubleshooting information.

Can't install Log Parser 2.1 on Windows 2000

Version: 2.1

The procedure for getting Log Parser 2.1 on to a Windows 2000 box is non-obvious. You can't just run the setup, because it will fail on any operating system other than Windows XP and Windows 2003.

Though the IIS 6.0 Resource Kit itself is limited to Windows 2003 and Windows XP, Log Parser 2.1 runs fine on Windows 2000. The trick is getting it there. You have two options to do this. The first is to install to a Windows 2003 or Windows XP box, and then copy the Program Files\IIS Resources\Log Parser folder to your Windows 2000 box. If that's not an option, you can still get the files by performing an administrative installation directly to your Windows 2000 computer. Download the iis60rkt.exe file to your computer and open a command prompt window to the directory where you saved the file. Then execute this command line:

iis60rkt.exe /V/a

The InstallShield Wizard will appear and ask you where to place the files for the administrative install. Specify a directory such as c:\Temp, and then you'll find the Log Parser 2.1 files in c:\Temp\program files\IIS Resources\Log Parser.

What is the current version of Log Parser?

The current version of Log Parser is Log Parser 2.2, which is available directly from the Microsoft Download site.

What operating systems will Log Parser run on?

Log Parser runs on Windows 2000, Windows XP, Windows Server 2003, or Windows Server 2008.

Date Arithmetic (2.1)

Starting in version 2.1, you can perform date arithmetic in the WHERE clause. Here's an example:

LogParser "SELECT EventLog, RecordNumber, TimeGenerated, EventID, EventType, EventTypeName, EventCategory, SourceName, ComputerName, Message FROM System, Application, Security WHERE TO_DATE(TimeGenerated) = TO_DATE(SUB(SYSTEM_TIMESTAMP(), TO_TIMESTAMP('01-02', 'MM-dd')))" -o:DATAGRID

The origin date for timestamp values is 0000-01-01 00:00:00, so 0000-01-02 00:00:00 represents a span of exactly one day.

Write a query that returns records that are not already in the output file

Note: In version 2.2, you can use the new Checkpoint feature to solve this problem in many cases. See the help topic "Parsing Input Incrementally".

Versions: 2.0, 2.1

You can't directly write a query that returns only records that are not already in the output file; that would require joining data from two different sources (the input file and the output file). Log Parser is planned to support multiple inputs in the same query in version 3.0, which will ship at some unknown point in the future. However, you may be able to get the required results with one of two different hacks. First, if the output is a SQL table, you can set a unique index on the table and just let inserts that violate the uniqueness constraint fail. Second, you may be able to use a sub-select if you have a different file that already contains the records that were previously inserted:

SELECT * FROM NewLog.LOG 
TO Dest 
WHERE (datetime field) > (SELECT Max(datetime field) FROM OldLog.log)

Use Windows authentication with SQL Server ouput

To use Windows authentication when using SQL Server as the output target for Log Parser, just leave out the username and password parameters in the Log Parser command line. Log Parser will default to integrated authentication.

Source of Query and Template Samples

Worth reading is Mark Burnett's article for SecurityFocus, Forensic Log Parsing with Microsoft's Log Parser, showing how to use Log Parser to hunt through IIS logs looking for suspicious patterns.

Use an Access Database for Log Parser output

Here's a full example of how to use a Microsoft Access database as the target for Log Parser output:

Create your Access database.
Create the target table in your Access database. In Log Parser 2.0, the -createtable switch has a problem with Access databases (see the related article)You may need to play around some to get the data types right, but fortunately the error messages if you get it wrong are pretty clear. For this example, I created a table named Files with three fields: FName (Text, 255), Size (Number, Long Integer), and Attributes (Text, 255).
Create an ODBC DSN to point to the database. On Windows 2000, you do this from Programs, Administrative Tools, Data Sources (ODBC). I created a System DSN named LPtest that refers to my Access database. You'll find when you create the DSN that you get to select a driver (Microsoft Access, of course) and then browse to the database (the "Select" button on the setup dialog box will do this).
Now you can run a Log Parser query that uses the DSN to get to the database. For this example, I used:

logparser "SELECT Name AS FName, Size, Attributes 
FROM c:\winnt\system32\*.* TO Files" -i:FS -o:SQL -dsn:LPtest

Escape commas in CSV output

When outputting query results to -o:CSV format, any commas that are embedded in the data are not escaped. Consequently, reading the results into Excel will be done improperly.

Workaround for 2.0:

You can use the REPLACE_CHR function, as in:

"SELECT ...., REPLACE_CHR(cs-uri-query, ',', '+'), ..."

which will automatically replace any occurrence of ',' with '+'.

If double quotes are preferred, you can use the STRCAT function:

"SELECT ...., STRCAT('"', STRCAT(cs-uri-query, '"')), ..."

New switch in 2.1:

In version 2.1, the CSV and W3C output formats have a new "encodeDelim" option (default=false), which will automatically encode ',' (for CSV) and space (for W3C) as '+'.

Include a column name that contains a space in the SELECT statement

Versions: 2.0, 2.1

Including column names containing spaces in your SQL statements requires special syntax. In Log Parser 2.0, you can use \u0020 to represent a space in Unicode:

logparser "SELECT Virus\u0020Type, Virus\u0020Name 
FROM log.csv" -i:CSV -headerRow:ON

In Log Parser 2.1, you can surround field names with the SQL quoting square brackets instead:

logparser "SELECT [Virus Type], [Virus Name] 
FROM log.csv" -i:CSV -headerRow:ON

Use event logs with spaces in their names

Some event logs (such as Directory Service, DNS Server, and File Replication Service) have spaces in their names. To get Log Parser to recognize these files, you need to do two things:

Specify the -i EVT switch to tell LogParser specifically that this is an Event Log
Replace spaces in the log name with the Unicode-escaped space \u0020

So, for example, this query will give you the EventIDs for ten FRS events:

logparser -i EVT "SELECT TOP 10 EventID 
FROM File\u0020Replication\u0020Service"

Use remote event logs with spaces in their names

Version: 2.1
Fixed in: 2.2

Attempting to retrieve information from an event log such as File Replication Services on a remote server may fail. For instance, this command line:

LogParser -i:EVT -o:CSV 
"SELECT * FROM \\myNetworkServer\File\u0020Replication\u0020Service 
TO test.csv"

may return the following error:

ERROR: Error while looking for files: Error searching for files in folder \\myNetworkServer\File Replication Service: The network path was not found.

The problem isn't actually the spaces in the name. Log Parser checks the registry to verify event log names before issuing the query. There's a bug in all versions of Log Parser through 2.1 that causes it to incorrectly check the local registry rather than the remote registry, so if the event log you're trying to retrieve doesn't exist on the local computer, the query will fail. The workaround is to create a registry key for the desired event log in HKLM\System\CurrentControlSet\Services\EventLog. To make the example work, just add a File Replication Services subkey on your local machine.

There's an additional problem if your local computer has a different %systemroot% than the remote computer; in this case, it may not be able to retrieve message descriptions for remote event log entries. The only workaround for this is to copy the EventMessageFile registry keys, and the files they point to, from the remote computer to your own computer, placing the files in a known location.

Read Windows 2000 Event logs saved as CSV

You may encounter difficulties when trying to retrieve information from Windows 2000 event logs saved as CSV files. Here's an example to show how to pull out events by ID number from such files.

The first hurdle when using a CSV generated from an event log is that it doesn't include field names -- LogParser will simply call them Field1, Field2, and so on. I took a look at the standard format for an exported event log and it looks like this to me:

Field1 - Date
Field2 - Time
Field3 - Source
Field4 - Type
Field5 - Category
Field6 - Event
Field7 - User
Field8 - Computer
Field9 - Description

So, given a file named security.csv, you could extract all of the 560 Events with this line:

logparser "SELECT Field1 AS Date, Field2 AS Time, Field7 AS User, Field9 AS Description 
FROM c:\Temp\Securiy.csv WHERE Field6=560" -i:csv -headerRow:Off

Cannot Find 'Fields' Directive when parsing IIS log file

Attempting to run a query against an IIS log file may return an unexpected error. For example, you attempt to run this query:

logparser -i:IISW3C -o:CSV "SELECT cs-uri-stem FROM iislog.log"

You receive this error:

Cannot Find 'Fields' directive

This indicates that the files you are trying to parse are in NCSA format, rather than native IIS format. Use the -i:NCSA switch with the query. Change the field names into those displayed by running

logparser -h -i:NCSA

Timestamp basics

Timestamp is a Log Parser data type that can contain a date, a time, or both. Timestamps have two basic functions. Firtst, they can hold an entire date and time. For example, returning the current system time as a timestamp:

SYSTEM_TIMESTAMP()

Note that SYSTEM_TIMESTAMP() returns UTC time.

You can create such a timestamp by combining two timestamps, one of which contains a date and one of which contains a time. For example, you can get the current system date as a timestamp with SYSTEM_DATE() and the current system time as a timestamp with SYSTEM_TIME(), so a longwinded replacement for SYSTEM_TIMESTAMP() is

TO_TIMESTAMP(SYSTEM_DATE(), SYSTEM_TIME())

Timestamps can also be used to hold a span of time. For this, you can use the second form of TO_TIMESTAMP(), which takes a string value indicating a date or time (or both), and a formatting string. You can also use the equivalent TIMESTAMP() data type, which is constructed the same way. For example, returning a 12-hour timestamp (holds twelve hours in the first day of the year zero):

TIMESTAMP('12:00:00', 'hh:mm:ss')

Returning a 24-hour timestamp:

TIMESTAMP('2', 'd')

(Why not TIMESTAMP('1', 'd')? Because the origin of the date values is day 1 of year zero. So you need to ask for the start of the second day to get 24 hours of time.)

Returning a 48-hour timestamp (2 days in year zero):

TIMESTAMP('3', 'd')

You can use timestamp math to move around timezones. For example, to move a time from GMT to GMT-6, you'd subtract a six-hour timestamp:

SUB( FieldWithGMTTimestamp, TIMESTAMP('06:00:00', 'hh:mm:ss'))

If you're trying to go from GMT to your computer's local time, there's a shortcut TO_LOCALTIME(). The reverse shortcut is TO_UTCTIME().

You can also use timestamp math in WHERE clauses to get recent events. For example, to get events in the last two hours, you'd look to see whether a particular time was more recent than a time created by subtracting a two-hour timestamp fro the current time:

[.....] WHERE FieldWithGMTTimestamp >= SUB(SYSTEM_TIMESTAMP(), TIMESTAMP('02:00:00', 'hh:mm:ss'))

To extract just the date from a timestamp into another timestamp, use TO_DATE(). To extract just the time from a timestamp into another timestamp, use TO_TIME().

Retrieve data from file with a space in the filename

Version: 2.1

If you have a filename such as "Copy of MyFile.csv", you may have trouble retrieving data from the file. A simple query such as "SELECT * FROM Copy of MyFile.csv" will fail with a syntax error.

The solution is to Unicode-encode the space characters: "SELECT * FROM Copy\u0020of\u0020MyFile.csv".

In general, you can use \u followed by hexadecimal numbering to represent any arbitrary characters in Log Parser queries.

Note: In Log Parser 2.2, you can also use single quotes to delimit the FROM clause. So beginning in 2.2 you can say "SELECT * FROM 'Copy of MyFile.csv'"

Failure to parse comma-separated FROM clause

Version: 2.2

Log Parser 2.2 may fail to correctly parse a comma-separated list of elements in the FROM clause. If a comma character in the FROM clause is followed by whitespace (space/tab/carriage-return/line-feed), parsing will fail.

Workaround: Remove all spaces after commas in the FROM clause.

Can't direct output to database other than SQL Server

You may encounter difficulties exporting to an ODBC data source that doesn't support the SQL datatypes that LogParser is using. One possible workaround (reported to be successful with Oracle) is to convert all columns to strings with the TO_STRING function, and to provide a table in your datasource whose columns are predefined as varchar2 columns.

Another (and usually superior, if you have direct administrative access to the database) is to create the target table yourself before you perform the export. That way you can use any data type that LogParser itself can handle in updating an existing table, without being limited to string fields.

Retrieve events in the last calendar month

There's no clean way to write SQL to retrieve only log entries for the last calendar month - that is, from today minus the number of day's in today's month, so if today is March 4 you want to see events from February 5 to March 4. Here's a hack suggested by Gabriele Giuseppini:

SELECT ....

USING TO_TIMESTAMP([your stuff]) AS Now,

TO_TIMESTAMP(

CASE TO_STRING(Now, 'M')

WHEN '1' THEN '02-01' // 31 days

WHEN '2' THEN '01-29' // Broken on leap years

WHEN '3' THEN '02-01' // 31 days

WHEN '4' THEN '01-31' // 30 days

......

END, 'MM-dd') AS DaysInMonth

... WHERE TimeGenerated >= SUB( Now, DaysInMonth)

What's new in Log Parser 2.2

Changes to Log Parser 2.2 include:

New input formats (XML, TSV, ADS, COM, REG, NETMON, and ETW)
New output formats (CHART, TSV, SYSLOG)
GROUP BY WITH ROLLUP
DISTINCT in aggregate functions
PROPSUM and PROPCOUNT
A whole bunch of new functions including MOD, ROUND, FLOOR, EXTRACT_FILENAME, HEX_TO_ASC, and more
USING clause for temporary field expressions
BETWEEN in WHERE or HAVING clauses
SELECT CASE
New date/time formats: milliseconds, nanoseconds, AM/PM
Many new parameters for input and output formats
Parameters in stored SQL files
Permanent overrides to default global options

These are just the highlights. See the Log Parser 2.2 help file for full details.

Strange results when inserting data into MySQL database

When inserting data into a MySQL database, you may find strange or nonsensical data in the resulting table. It doesn't matter whether you let Log Parser create the table, or whether you create it yourself. This is due to a bug in the MyODBC driver that occurs in (at least) versions 3.51.04 through 3.51.11.

Currently there is no resolution or workaround.

pulling out a string with SUBSTR()

daniel45 — Fri, 26 Dec 2008 18:48:19 GMT

I would like to pull a string out from a field of a variable length, where the length is defined by index_of("string2") - index_of("string1"), that is the field looks like this "some stuff string1 string string2"

I've tried doing it like this SUBSTR(field, INDEX_OF('string1'), INDEX_OF('string2') - INDEX_OF('string1'))

but logparser is refusing to accept this, and it is the "algebraic" part that is the problem.

Does anyone have any ideas to solve this.

Regards Daniel.

Re: Log Parsing help for a beginer

jellis_ms — Mon, 08 Sep 2008 14:00:34 GMT

There is a book. Search Amazon.com for "LogParser"

There is also a compiled list from this website on http://linuxlore.blogspot.com/

Search the page for "LogParser"

Link not working

thompsonson — Wed, 28 May 2008 11:25:00 GMT

Hi Chaps,

the link to the LogParser Documentation is coming back with a 404...

http://www.iis.net/Downloads/files/LogParser/LogParser-ENU.chm

Anyone know where else this can be found?

Cheers,

Matt