LogViewPlus Support » LogViewPlus Support » Help & Support » Can't use SQL Filter on Custom Parser column

Can't use SQL Filter on Custom Parser column

DavidL — Mon, 21 Oct 2024 18:22:20 GMT

I created a Custom Parser for my log file and it seems to work well, except when I try to add a SQL filter. It never returns any results if I try to use a custom column in the WHERE clause. The custom column does show up in the "Available Columns" tab when I'm editing the filter, but when I click on the "Execution Result" tab, I get a warning that says, "No data found for field '<CustomFieldName>'". The "Test" button tells me that the SQL statemen parsed correctly.

What am I missing?

RE: Can't use SQL Filter on Custom Parser column

LogViewPlus Support — Mon, 21 Oct 2024 18:22:20 GMT

No problem David - glad that helped! Please do let me know if you have any further questions or issues.

RE: Can't use SQL Filter on Custom Parser column

DavidL — Mon, 21 Oct 2024 16:10:37 GMT

That makes sense. The problem I was trying to solve is that there's a lot of different information I'd like to extract from logs that don't have a consistent format (past a timestamp and log level). I'll have to re-think how I get at that data.

Thanks for your help. I greatly appreciate it!

RE: Can't use SQL Filter on Custom Parser column

LogViewPlus Support — Sun, 20 Oct 2024 14:38:54 GMT

Glad to hear the behaviour is looking better.

LogViewPlus doesn't really do anything fancy when trying to determine the data type. It tries to identify numeric data which can be parsed as a double or integer. If this check fails, the data will be treated as a string. For each column, LogViewPlus will check up to 2000 column entries regardless of the size of the log file. For large files, these entries will be checked randomly. For small files, all entries should be checked.

If LogViewPlus finds a lot of empty data values, the data type will be assumed to be a string. However, LogViewPlus tries to be fault tolerant. If a small number (currently this would be less than 5%) of empty values are found, these will default to a value of zero rather than treating the entire column as string data.

I suspect the problem here is with how your custom parser is providing data for the 'optional' columns. LogViewPlus makes a core assumption that columns are not optional and are consistent for a given parser configuration. Trying to work around this limitation may be causing the issue. I can suggest two alternatives to optional columns:

1. Provide a configuration flag to specify which columns should be included.
2. Include all columns for all requests. Default data here should match a consistent data type of the column. Data types should not change between instances.

Hope that helps,

Toby

RE: Can't use SQL Filter on Custom Parser column

DavidL — Thu, 17 Oct 2024 18:41:46 GMT

I rewrote the parser to remove any "optional" fields and ensured that every field had a value. It seems to work better.

I had a couple thoughts. If I define some columns, but treat them as optional, meaning for some log entries I add the appropriate number of strings, but others I don't add all the strings, could that result in the behavior I was seeing?

I would assume that if there is string data in the column, that all of data the column would be treated as strings. Is this not the case?

What if the log file starts with a bunch of lines that don't parse using all the columns. You said that LogViewPlus looks at the column data "statistically". Could it look at the first bunch of lines, not see data in the columns and assume there is no data at all?

Related, but how does the SQL filter handle null or empty values?

RE: Can't use SQL Filter on Custom Parser column

DavidL — Thu, 17 Oct 2024 18:40:23 GMT

That's an interesting thought. I do have somewhat of a mix of data where I tried to reuse columns. I'll try to rewrite my plugin into a set of plugins and see if I can get better results.

Yes, I am using the latest version of LogViewPlus.

RE: Can't use SQL Filter on Custom Parser column

LogViewPlus Support — Thu, 17 Oct 2024 07:48:32 GMT

Thanks for the update.

LogViewPlus attempts to resolve data types dynamically. It does this by looking at column data statistically to determine an appropriate data type. It could be that not all data in the target column matches the expected data type, although I would expect a different error message.

I think the last screen shot is a red herring. It looks like that code is part of the type determination used by the SQL statement parser. It does throw an error, but this is expected and handled at a higher level. We are testing to see if a constant value could be a Long.

Are you using the latest version of LogViewPlus?

RE: Can't use SQL Filter on Custom Parser column

DavidL — Wed, 16 Oct 2024 22:36:42 GMT

Sorry, I realized that I didn't answer your question directly. No, using 'Arg4' or any of the other custom defined columns in the query doesn't work either.

RE: Can't use SQL Filter on Custom Parser column

DavidL — Wed, 16 Oct 2024 22:21:40 GMT

Thanks for getting back!

I tried with all of the custom string fields and they all seem to have the same result. Here's how I've defined them:

[code language="c#"] List<FieldColumnInfo> IColumnManagement.GetSupportedTypes()
{
var retval = new List<FieldColumnInfo>();
retval.Add(new FieldColumnInfo(ElementType.Date, true));
retval.Add(new FieldColumnInfo(ElementType.Message, true));
retval.Add(new FieldColumnInfo(ElementType.Logger, true));

retval.Add(new FieldColumnInfo(ElementType.String, "Direction", true, 3));
retval.Add(new FieldColumnInfo(ElementType.String, "CallType", true, 4));
retval.Add(new FieldColumnInfo(ElementType.String, "Arg1", true, 5));
retval.Add(new FieldColumnInfo(ElementType.String, "Arg2", true, 6));
retval.Add(new FieldColumnInfo(ElementType.String, "Arg3", true, 7));
retval.Add(new FieldColumnInfo(ElementType.String, "Arg4", true, 8));

return retval;
}
[/code]

I can do a SQL Query for the standard fields, but seemingly not the custom strings.
For example, this works:

(Though if I click on the Execution Result tab it still says "No data found for field 'Message'".)

But this doesn't work:

Running the SQL Scratchpad (which I actually didn't realize existed until you pointed it out!) fails with just the default query.

I happend to be attached in VS when I tried to run the filter again and this is the exception that popped up:

RE: Can't use SQL Filter on Custom Parser column

LogViewPlus Support — Wed, 16 Oct 2024 19:00:30 GMT

Hi David,

Thanks for bringing this issue to our attention.

I have done some testing locally using our sample Custom Parser and I have not been able to recreate the issue. I am able to execute the query and the results seem correct.

In the sample parser, the custom 'Parser Arguments' column is defined as:

[code language="c#"]new FieldColumnInfo(ElementType.String, "Parser Arguments", true, 3)[/code]
I suspect the issue here has something to do with how the CallType field is being defined. Do you have any more information? Do other custom columns (such as Arg4) work? If you use the Sql Scratchpad and execute:

[code language="sql"]SELECT * FROM CV[/code]
Does the result show date from the CallType and Arg4 properties?

Thanks again,

Toby