Using a lookup in subsearch to derive new field

[sr_dhinesh]

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)

[woodcock · Accepted solution]

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.

[sr_dhinesh]

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

[sr_dhinesh]

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

[woodcock]

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

[woodcock]

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

[jplumsdaine22]

Great solution

[woodcock]

Be sure to upvote!

[jplumsdaine22]

You have plenty of karma as it is 🙂

[woodcock]

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

[jplumsdaine22]

lol so close!

Just keep me ahead of @Jeremiah

[sundareshr]

Try this

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

[sr_dhinesh]

this gives some count only error !!

[jplumsdaine22]

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" ?

[sr_dhinesh]

Yes it has white spaces

[jplumsdaine22]

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

[sr_dhinesh]

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

[somesoni2]

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

[jplumsdaine22]

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

[sr_dhinesh]

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