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)), '/')
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?
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?!
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')
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...
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();
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 ...
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
Dave Smith J...
6 Posts
Using LogParser from C# - Extract_Path errors
Oct 17, 2012 11:49 AM|LINK
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)), '/')
Perkinsville
109 Posts
Re: Using LogParser from C# - Extract_Path errors
Nov 15, 2012 07:55 AM|LINK
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
Dave Smith J...
6 Posts
Re: Using LogParser from C# - Extract_Path errors
Nov 15, 2012 12:08 PM|LINK
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
ron_bo
52 Posts
Re: Using LogParser from C# - Extract_Path errors
Dec 11, 2012 02:25 AM|LINK
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 ...
Dave Smith J...
6 Posts
Re: Using LogParser from C# - Extract_Path errors
Dec 11, 2012 01:06 PM|LINK
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
ron_bo
52 Posts
Re: Using LogParser from C# - Extract_Path errors
Dec 13, 2012 09:57 PM|LINK
Can you show your code ??? I use command line and VB.NET but can probably help out with your C# code ...
Dave Smith J...
6 Posts
Re: Using LogParser from C# - Extract_Path errors
Feb 19, 2013 11:53 AM|LINK
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
}
ron_bo
52 Posts
Re: Using LogParser from C# - Extract_Path errors
Feb 19, 2013 03:17 PM|LINK
Hmmm ... I must admit I'm a bit stumped ... I created a file:
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 ...
Dave Smith J...
6 Posts
Re: Using LogParser from C# - Extract_Path errors
Feb 19, 2013 03:25 PM|LINK
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
ron_bo
52 Posts
Re: Using LogParser from C# - Extract_Path errors
Feb 22, 2013 02:41 AM|LINK
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 ...