Using a lookup file to run queries

[gferreira]

I'm trying to create a search based on error strings in a lookup file and i'd like the output to include stats based on the individual strings in the lookup file.

The following query works where I can search for all the query strings but i'm unable to create fields based on the Error so that I can use it in a stats or timechart query

index=syslogs [| inputlookup ErrorString.csv | table Error | rename Error as search | format]

Example ErrorString.csv with a header of Error and the logs strings below

Error
Invalid response from hook
com.atlassian.applinks.api.CredentialsRequiredException
response com.atlassian.sal.api.net.ResponseStatusException: Unexpected response received
ORA-00001: unique constraint * violated
SqlExceptionHelper bitbucket - Connection is not available, request timed out
JDBCConnectionException: Could not open connection
SqlExceptionHelper bitbucket - Connection is not available
org.hibernate.exception.JDBCConnectionException: Could not open connection

[elliotproebstel]

I've confronted issues like this before, and it's not totally straightforward. If the strings you're searching for all fall into the same field in the base events, you have a shot. First, you'll need to either change your existing lookup table into a wildcard lookup or duplicate your lookup table so you have two - your current table and another that is a wildcard lookup. Then you'd do something like this, assuming your error strings all appear in a field called message, for example:

index=syslogs 
    [| inputlookup ErrorString.csv 
    | table Error 
    | rename Error AS search 
    | format ] 
| lookup ErrorStringWildcard.csv Error AS message OUTPUT Error AS matching_Error 
| eval matching_Error=trim(matching_Error, "*")

A few major gotchas:
1. The lookup will be case-sensitive, so an event with "INVALID response from hook" will not match a wildcard lookup entry of "*Invalid response from hook*". (There are some ways to get around this by making your lookup case-insensitive and having all of your wildcard lookup entries be in lower-case.)
2. This won't work if the Error string can appear in multiple fields. If that's the case, you'll need to either use coalesce to get them into a single field or run the lookup many times over multiple field names.
3. This really won't work well if the Error string can appear in the _raw data and isn't being extracted into a field.

If you've never set up a wildcard lookup before, this is a good guide:
https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html

[somesoni2]

Do the error messages appean on specific place in your raw data? If yes, you could setup field extraction for it.

Your other option would to 1) Add wildcard asterist *at either side of values of column Error in the lookup ErrorString.csv, 2) setup a lookup definition with wildcard match for your ErrorString.csv, 3) use lookup command using that lookup definition to match _raw and output Error Column, on which you should be able to do stats/timechart (after cleaning up *)
https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html