logparser samplesRSS

6 replies

Last post May 17, 2012 05:59 PM by ron_bo

  • pharr pharr

    137 Posts

    Microsoft

    logparser samples

    Feb 27, 2009 04:41 PM|pharr|LINK

    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:

    <div>
    	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.

    </div>

    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

    <div>Sample how to use COM-Object in VB6 with ODBC- DSN-Entry as SQL-Server Input</div> <div> </div> <div>
    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
    </div>

    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

    <div>

    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.  

    </div>

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

    logparser Documentation

  • Re: logparser samples

    Mar 04, 2009 04:18 PM|joelangley|LINK

    Very nice post...you have some good info here. Some of your zips seem not to be uploaded.

    <div menubottom="0" menuright="0" menutop="0" menuleft="0" activeid="-1" expanded="0" style="display: none;" id="divCleekiAttrib"></div>
  • pharr pharr

    137 Posts

    Microsoft

    Re: logparser samples

    Mar 09, 2009 11:45 AM|pharr|LINK

    Thanks - I repaired the zip download links.
    Pete.

  • Re: logparser samples

    Mar 09, 2009 01:42 PM|Ed Grossheim|LINK

    12-41 pm  03-09-09

       Nice,

       Thanks from me too. Good stuff to study!

     

    ..... Ed

     

  • Re: logparser samples

    Mar 17, 2009 06:12 PM|aureolin|LINK

     I'm trying out your logsplitter code and I get a SQL language syntax error on

    QUERY1 = "select * into ..." 

    The syntax parser does not like the "select into" construct and I get the following error:

    Unhandled Exception: System.Runtime.InteropServices.COMException (0x8007064F):
    CLogQueryClass: Error 8007064f: ExecuteBatch: error parsing query:
    Syntax Error:<from-clause>: expecting FROM keyword instead of token 'into'
    [ SQL query syntax invalid or unsupported. ]   at MSUtil.LogQueryClassClass.ExecuteBatch(String
    szQuery, Object pObjectInputContext, Object pObjectOutputContext)  
    at LogSplitter.Class1.Main(String[] args)

    Any ideas?

    Thanks,

    Steve G.

  • Re: logparser samples

    May 10, 2012 02:05 PM|nbraasch|LINK

    You have just wasted three days of my time. How? I've been playing with all these awesome tidbits!!! Thank you. Now, back to work.
  • Re: logparser samples

    May 17, 2012 05:59 PM|ron_bo|LINK

    Very nice ... I think I may be able to find a trick or two of use here ... Thanks !!!