Log Parser c# error using STRCAT with CASE

Question

I'm having trouble with the log parser, punctually on the use of the function STRCAT parameter with CASE, using log parser the query works perfectly and using a simple STRCAT without CASE the query works even using c#, the problem starts when i use CASE. Am I missing something?

Here's the error: CLogQueryClass: Error 8007064f: Execute: error parsing query: Syntax Error: : cannot find closing parenthesys for function STRCAT [ SQL query syntax invalid or unsupported. ]

string query = "SELECT " + " STRCAT('" + entry.Name +"'";
                query += @", CASE INDEX_OF(SUBSTR(cs-uri-stem,1), '/')
                            WHEN 'NULL' THEN 'DEFAULTAPPPOOL'
                            ELSE EXTRACT_TOKEN(cs-uri-stem,1,'/')
                            END";
                query += ") AS APPPOOL";
                query += ", '" + Environment.MachineName + "' as server";
                query += ", '" + entry.Name + "' as site";
                query += ", cs-uri-stem as csUriStem";
                query += ", c-ip as cIp, sc-status as scStatus";
                query += ", sc-bytes as scBytes";
                query += ", cs-bytes as csBytes";
                query += ", time-taken as timeTaken";
                query += " FROM " + logAddress + "\\" + yesterdayLogName;
                // Initialize a LogQuery object
                logQuery = new LogQueryClass();
                logRecordSet = logQuery.Execute(query,new COMIISW3CInputContextClass());

                //SHOWS RESULT
                for (; !logRecordSet.atEnd(); logRecordSet.moveNext())
                {
                    logrecord = logRecordSet.getRecord();
                    int i = 0;
                    while (i < 9)
                    {
                        Console.WriteLine(logrecord.getValue(i));
                        i++;
                    }

Thanks

Filter the title through `tr [[:upper:]] [[:lower:]]`, all-caps is annoying. — , Jan 02 '14 at 12:42

score 0 · Accepted Answer · edited May 23 '17 at 12:28

First, it looks like you are mixing types. The CASE INDEX_OF(SUBSTR(cs-uri-stem,1), '/') WHEN 'NULL' compares an integer to string. This should be:

CASE INDEX_OF(SUBSTR(cs-uri-stem,1), '/') 
   WHEN NULL THEN 'DEFAULTAPPPOOL'
   ELSE EXTRACT_TOKEN(cs-uri-stem,1,'/')
END

The error complains about finding the close parenthesis, but I've found that parsing errors can result in misleading error messages with LogParser.

Second, I've tested the following in C# targeted at .NET 3.5 (4.0 had an issue with embedded type. Similar to this...):

string logAddress = "C:\\Path\\to\\consolidatedFile";
string entryName = "blah";
string yesterdayLogName = "fileName.log";

string query = "SELECT " + " STRCAT('" + entryName + "'"
                + ", CASE INDEX_OF(SUBSTR(cs-uri-stem,1), '/') "
                    + "WHEN NULL THEN 'DEFAULTAPPPOOL' "
                    + "ELSE EXTRACT_TOKEN(cs-uri-stem,1,'/') "
                + "END"
             + ") AS APPPOOL"
             + ", '" + Environment.MachineName + "' as server"
             + ", '" + entryName + "' as site"
             + ", cs-uri-stem as csUriStem"
             + ", c-ip as cIp, sc-status as scStatus"
             + ", sc-bytes as scBytes"
             + ", cs-bytes as csBytes"
             + ", time-taken as timeTaken"
             + " FROM " + logAddress + "\\" + yesterdayLogName;

// Initialize a LogQuery object
COMW3CInputContextClassClass ctx = new COMW3CInputContextClassClass();
//LogQueryClass logQuery = new LogQueryClass();
LogQueryClass logQuery = new LogQueryClassClass();
//ILogRecordset logRecordSet = logQuery.Execute(query, new COMIISW3CInputContextClass());
ILogRecordset logRecordSet = logQuery.Execute(query, ctx);

//SHOWS RESULT
for (; !logRecordSet.atEnd(); logRecordSet.moveNext())
{
    ILogRecord logrecord = logRecordSet.getRecord();
    int i = 0;
    while (i < 9)
    {
        Console.WriteLine(logrecord.getValue(i));
        i++;
    }
}

This ran successfully and return results. I commented out the lines initially presented since when I used them nothing returned on the console. That might be a difference of the code not presented. Finally, I substituted a string entryName for the entry.Name object assuming that it returns a string.

I hope this helps.

Does your compiled query run from the LogParser commandline tool? I would recommend starting there and working backwards. Also, did the error end up changing at all or are you still getting the exact same one? — beavel, Jan 02 '14 at 19:14
@nathansantanna See the above edits. I have now successfully tested in C#. — beavel, Jan 03 '14 at 16:33
Sorry for the late response, i tried your solution again i copied the query and changed the strings values, and i'm still getting the same error with closing parenthesys. Thankf for the help im getting crazy with this error — nathansantanna, Jan 07 '14 at 18:05
@nathansantanna What version of the LogParser dll are you using? This was against 2.2.10. If you copied exactly as is, you must have an invalid character or a character that is opening up some part of the syntax in one of your variables then. What is in `entry.Name`? — beavel, Jan 07 '14 at 18:43
yes i'm using log parser 2.2.10, entry name is just the web site "id" on iis, anyway i substituted all variables for a simple string too :/. thanks for the efforts — nathansantanna, Jan 08 '14 at 10:37
got it, i tried the software in another server and worked perfectly, thanks for your help — nathansantanna, Jan 08 '14 at 12:54

Log Parser c# error using STRCAT with CASE

1 Answers1