topic Re: Using a lookup in subsearch to derive new field in Splunk Search

Using a lookup in subsearch to derive new field

sr_dhinesh — Wed, 22 Jun 2016 12:10:54 GMT

index=xyz [|inputlookup error_strings  | table string | rename string as search | format]

In the lookup I have a list of string "error", "nullvalue",... etc

In the index xyz I have multiline events and have multiple strings, but not in a proper format to extract fields.

My requirement is whenever there is a string that matches to the lookup file in the event, I want the distinct count of the string. (Disclaimer: I do not have those strings in field as it cannot be extracted as they are uneven and each event has 240 lines.. these are java exceptions and errors)

Re: Using a lookup in subsearch to derive new field

jplumsdaine22 — Wed, 22 Jun 2016 12:29:38 GMT

Is there any whitespace in your list of strings ? Are your strings single words? eg just "error" or "nullvalue". Or are there strings like "threw exception" ?

Re: Using a lookup in subsearch to derive new field

sr_dhinesh — Wed, 22 Jun 2016 12:34:14 GMT

Yes it has spaces Below are the strings:

java.lang.OutOfMemoryError
Unable to send alert because queue is full
ContainerConfigPollingService Exception
java.lang.NullPointerException
com.digev.fw.exception.GException: Internal system error.
com.digev.fw.exception.GException: Unexpected error
com.digev.fw.exception.GException: Error occured while validating token null
com.digev.fw.exception.GException: Unrecognized echange pattern

The query that i mentioned on top gives me the events with these string however i am not able to get a count of those distinct strings, Thanks

Re: Using a lookup in subsearch to derive new field

sr_dhinesh — Wed, 22 Jun 2016 13:02:38 GMT

Yes it has white spaces

Re: Using a lookup in subsearch to derive new field

jplumsdaine22 — Wed, 22 Jun 2016 13:10:49 GMT

How many strings are we talking about? If its not too many you could basically do a separate search for each string.

Re: Using a lookup in subsearch to derive new field

sr_dhinesh — Wed, 22 Jun 2016 13:32:19 GMT

for now there are 10 strings but client might want to modify the lookup as and when required, so that s going to be hard

Re: Using a lookup in subsearch to derive new field

somesoni2 — Wed, 22 Jun 2016 14:42:52 GMT

Can a value from lookup appear in the event multiple times? If yes, should they be counted as one ?

Re: Using a lookup in subsearch to derive new field

jplumsdaine22 — Wed, 22 Jun 2016 14:49:57 GMT

I don't think Splunk is really the tool for this - you might be better off with some python or R package against the raw data if you want to do this in a scalable way

Re: Using a lookup in subsearch to derive new field

sundareshr — Wed, 22 Jun 2016 15:21:00 GMT

Try this

| inputlookup error_strings | table string  | map maxsearches=20 search="search index=xyz  $string$ | stats count"

Re: Using a lookup in subsearch to derive new field

woodcock — Wed, 22 Jun 2016 15:22:15 GMT

Let us assume that your lookup file has more than 1 field and that one of the other unique fields is called error_code. You need to make your lookup a WILDCARD lookup on field string and add an asterisk ( * ) as both the first and last character of every string. Then do this:

index=xyz [|inputlookup error_strings | table string | rename string AS query] | lookup error_strings string AS _raw OUTPUT error_code

And then either this:

| stats count BY error_code

Or this:

| stats dc(error_code) values(error_code)

Then, if you like, you can invert the lookup call to convert the error_code back to string.

Re: Using a lookup in subsearch to derive new field

jplumsdaine22 — Wed, 22 Jun 2016 15:29:24 GMT

Great solution

Re: Using a lookup in subsearch to derive new field

sr_dhinesh — Wed, 22 Jun 2016 16:04:28 GMT

i created another column in lookup as error_code and what value do you want me to insert here, is it the string value and convert the string value to asterik error asterik ?

Is this correct

Re: Using a lookup in subsearch to derive new field

sr_dhinesh — Wed, 22 Jun 2016 16:11:34 GMT

this gives some count only error !!

Re: Using a lookup in subsearch to derive new field

woodcock — Wed, 22 Jun 2016 16:24:00 GMT

You can put any unique string there. The simplest thing is to copy the string value, (without the asterisks).

Re: Using a lookup in subsearch to derive new field

woodcock — Wed, 22 Jun 2016 17:54:46 GMT

Be sure to upvote!

Re: Using a lookup in subsearch to derive new field

jplumsdaine22 — Thu, 23 Jun 2016 08:59:24 GMT

You have plenty of karma as it is 🙂

Re: Using a lookup in subsearch to derive new field

woodcock — Thu, 23 Jun 2016 14:22:16 GMT

But I am almost passing @duckfez (maybe today)!

Re: Using a lookup in subsearch to derive new field

jplumsdaine22 — Thu, 23 Jun 2016 15:19:31 GMT

lol so close!

Just keep me ahead of @Jeremiah

Re: Using a lookup in subsearch to derive new field

woodcock — Fri, 24 Jun 2016 16:35:48 GMT

Did you add the WILDCARD setting? This can only be done in CLI (not the GUI)?

Re: Using a lookup in subsearch to derive new field

sr_dhinesh — Tue, 28 Jun 2016 16:09:38 GMT

Apologies for the delay. Awesome solution. Worked like a gem, THANKS