locked
Using LogParser from C# - Extract_Path errors RRS feed

  • Question

  • User-1099223507 posted

    Hi,

    I am trying to use LogParser from a C# .NET 4 console application and have it working for nearly all of what I wanted but I now need to get just a list of URLs back with a count of hits for each of them.

    When I run my query through LogParser in the native command-line way then it works and I get my results, when running the SQL query through my C# app however, I get an error back as below:

    Error: CLogQueryClass: Error 80f20650: Execute: error parsing query: SELECT clause: Syntax Error: unknown field 'EXTRACT_PATH(To_Lowercase(cs-uri-stem))' [ Record field does not exist. ]

    My query is as below (yes, I know it is a long query)

    Select STRCAT(EXTRACT_PATH(To_Lowercase(cs-uri-stem)), '/'), Count(*) From E:\IISLogs\ex121004.log where cs-uri-stem not like '%.gif' and cs-uri-stem not like '%.jpg' and cs-uri-stem not like '%.jpeg' and cs-uri-stem not like '%.class' and cs-uri-stem not like '%.css' and cs-uri-stem not like '%.inc' and cs-uri-stem not like '%.shtm' and cs-uri-stem not like '%.shtml' and cs-uri-stem not like '%.js' and cs-uri-stem not like '%css.asp' and cs-uri-stem not like '%toolbar.asp' and cs-uri-stem not like '%/!G/%' and cs-uri-stem not like '%/!G_%' and cs-uri-stem not like '%!WIP%' and cs-uri-stem not like '%default.asp' and cs-uri-stem not like '%default.htm' and cs-uri-stem not like '%.gif' and cs-uri-stem not like '%robot%' and cs-uri-stem not like '%cgi-bin%' and sc-status < 400 Group By STRCAT(EXTRACT_PATH(To_Lowercase(cs-uri-stem)), '/')

    Wednesday, October 17, 2012 7:49 AM

All replies

  • User212506688 posted

    Hi,

    I was able to get this to run using just LogParser.  So the problem is not with your query or LogParser.  It's always a good idea to remove possibilities.

    Make sure that the IIS log file contains the cs-uri-stem column in the file.

    Have you tried running LogParser from the command prompt with this query on the same file?  Do you get the same error?

    HTH, Ben

    Thursday, November 15, 2012 3:55 AM
  • User-1099223507 posted

    Hi Ben,

    Yes, I have tried the same query against the same file from the command prompt and that works and returns the results. As such, I can also confirm that the cs-uri-stem field exists.

    It looks as though the additional functions to alter the value are not available from my C# application but I am confused as to whether this is just configuration on my part or if it just doesn't exist when using the DLL directly?!

    Thanks, Dave

    Thursday, November 15, 2012 8:08 AM
  • User-522963918 posted

    Have you verified if it is the text in the SELECT portion or the GROUP BY portion that is causing your problem ???

    Also, I'd recommend instead of: where cs-uri-stem not like '%.gif' and cs-uri-stem not like '%.jpg' and cs-uri-stem not like '%.jpeg' and cs-uri-stem not like '%.class' and cs-uri-stem not like '%.css' and cs-uri-stem not like '%.inc' and cs-uri-stem not like '%.shtm' and cs-uri-stem not like '%.shtml' and cs-uri-stem not like '%.js'

    You could use: WHERE NOT TO_LOWERCASE(EXTRACT_EXTENSION(cs-uri-stem)) IN ('class'; 'css'; 'gif'; 'inc'; 'jpg'; 'jpeg'; 'js'; 'shtm'; 'shtml')

    for better readability ...

    Monday, December 10, 2012 10:25 PM
  • User-1099223507 posted

    Hi ron_bo,

    From what I can tell, it doesn't matter whether it is in the select or the group by statement, I simply cannot get the function to work when using it from C# via the DLL...

    Dave

    Tuesday, December 11, 2012 9:06 AM
  • User-522963918 posted

    Can you show your code ??? I use command line and VB.NET but can probably help out with your C# code ... 

    Thursday, December 13, 2012 5:57 PM
  • User-1099223507 posted

    Here goes... I've had to strip it out of my existing application as there are a lot of other (not relevant) areas of code within this:

    //Run the query against the logfile
    ILogRecordset rsLP = null;
    ILogRecord rowLP = null;
    LogQueryClassClass LogParser = null;
    COMW3CInputContextClassClass W3Clog = null;

    string strSQL = null;
    LogParser = new LogQueryClassClass();
    W3Clog = new COMW3CInputContextClassClass();

    strSQL = @"Select STRCAT(EXTRACT_PATH(To_Lowercase(cs-uri-stem)), '/'), Count(*) From " + _location + GetFileNameFromDate(_dt) + " where cs-uri-stem not like '%.gif' and cs-uri-stem not like '%.jpg' and cs-uri-stem not like '%.jpeg' and cs-uri-stem not like '%.class' and cs-uri-stem not like '%.css' and cs-uri-stem not like '%.inc' and cs-uri-stem not like '%.shtm' and cs-uri-stem not like '%.shtml' and cs-uri-stem not like '%.js' and cs-uri-stem not like '%css.asp' and cs-uri-stem not like '%toolbar.asp' and cs-uri-stem not like '%/!G/%' and cs-uri-stem not like '%/!G_%' and cs-uri-stem not like '%!WIP%' and cs-uri-stem not like '%default.asp' and cs-uri-stem not like '%default.htm' and cs-uri-stem not like '%.gif' and cs-uri-stem not like '%robot%' and cs-uri-stem not like '%cgi-bin%' and sc-status < 400 Group By STRCAT(EXTRACT_PATH(To_Lowercase(cs-uri-stem)), '/')";

    rsLP = LogParser.Execute(strSQL, W3Clog);

    for (; !rsLP.atEnd(); rsLP.moveNext())
    {
    rowLP = rsLP.getRecord();

    //Do stuff here

    }

    Tuesday, February 19, 2013 7:53 AM
  • User-522963918 posted

    Hmmm ... I must admit I'm a bit stumped ... I created a file:

    #Fields: cs-uri-stem sc-status
    /blah/test.zzz 200

    And the following code ran fine in C# and returned one record:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                MSUtil.ILogRecordset rsLP = null;
                MSUtil.ILogRecord rowLP = null;
                MSUtil.LogQueryClass LogParser = null;
                MSUtil.COMW3CInputContextClass W3Clog = null;
                string strSQL = null;
                LogParser = new MSUtil.LogQueryClass();
                W3Clog = new MSUtil.COMW3CInputContextClass();
                strSQL = @"Select STRCAT(EXTRACT_PATH(To_Lowercase(cs-uri-stem)), '/'), Count(*) From D:\Data\ex121004.log where cs-uri-stem not like '%.gif' and cs-uri-stem not like '%.jpg' and cs-uri-stem not like '%.jpeg' and cs-uri-stem not like '%.class' and cs-uri-stem not like '%.css' and cs-uri-stem not like '%.inc' and cs-uri-stem not like '%.shtm' and cs-uri-stem not like '%.shtml' and cs-uri-stem not like '%.js' and cs-uri-stem not like '%css.asp' and cs-uri-stem not like '%toolbar.asp' and cs-uri-stem not like '%/!G/%' and cs-uri-stem not like '%/!G_%' and cs-uri-stem not like '%!WIP%' and cs-uri-stem not like '%default.asp' and cs-uri-stem not like '%default.htm' and cs-uri-stem not like '%.gif' and cs-uri-stem not like '%robot%' and cs-uri-stem not like '%cgi-bin%' and sc-status < 400 Group By STRCAT(EXTRACT_PATH(To_Lowercase(cs-uri-stem)), '/')";
                rsLP = LogParser.Execute(strSQL, W3Clog);
                for (; !rsLP.atEnd(); rsLP.moveNext())
                {
                    rowLP = rsLP.getRecord();
                }
            }
        }
    }

    The strSQL variable other than the path to the data file is a copy anddd paste from your code ... So I'm not sure I can duplicate your error ...


    Tuesday, February 19, 2013 11:17 AM
  • User-1099223507 posted

    Thanks for such a speedy response!

    OK, so it looks like the logic of what I am doing and the syntax is correct then... I presume therefore it must be down to how I have the project referencing the objects instead...

    I have a reference in my core project to "Interop.MSUtil" with "Embed Interop Types" set to False and "Specific Version" set to false as well. The project is a Visual Studio 2010 project in .NET 4.0.

    From an IISLog perspective, they are IIS 6 logfiles, an example first row entry (with header) is below for comparison:

    #Software: Microsoft Internet Information Services 6.0
    #Version: 1.0
    #Date: 2012-09-30 05:12:33
    #Fields: time cs-method cs-uri-stem cs-uri-query cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-bytes time-taken
    05:12:33 GET /images/header/file.gif - - 0.0.0.0 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+Trident/4.0;+.NET+CLR+1.0.3705;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30;+.NET+CLR+3.0.04506.648;+.NET+CLR+3.5.21022;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+.NET4.0C;+.NET4.0E) http://site/default.aspx 401 1872 953

    Tuesday, February 19, 2013 11:25 AM
  • User-522963918 posted

    What happens when you do something like:

    Select TOP 1 cs-uri-stem From E:\IISLogs\ex121004.log

    I would start there and work my way out to your full code to see where the error actually starts ... 

    Thursday, February 21, 2013 10:41 PM
  • User-1099223507 posted

    Yes, I've already gone through with that, it seems that the error occurs when I start to use either EXTRACT_PATH or STRCAT or To_Lowercase


    The really annoying part of it is that if I use the query from the console application directly it works and returns the data I need. I didn't want to have to run it like that and grab the output to process, I just wanted to run it properly via code...

    Friday, February 22, 2013 3:09 AM