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

Answer 1

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.