topic Re: Using a lookup file to run queries in Splunk Search

Using a lookup file to run queries

gferreira — Wed, 25 Apr 2018 19:21:00 GMT

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

Re: Using a lookup file to run queries

somesoni2 — Wed, 25 Apr 2018 20:16:08 GMT

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

Re: Using a lookup file to run queries

elliotproebstel — Wed, 25 Apr 2018 20:19:34 GMT

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