topic Re: Determine numerical value belong to intervals which stored in static lookup in Splunk Search

Determine numerical value belong to intervals which stored in static lookup

ejpulsar — Wed, 29 Oct 2014 08:32:28 GMT

Hi!
We've "broken" our heads on this.

Let we have events with field

NUM=100
NUM=150

And static lookup with interval looks like this (yes, we can change it format if needed):

NAME START END
 A     100 120
 B     121 180

We need to produce evens like

NUM=100 NAME=A
NUM=150 NAME=B

Re: Determine numerical value belong to intervals which stored in static lookup

MuS — Wed, 29 Oct 2014 12:40:10 GMT

Hi ejpulsar,

I think this is not possible -- (speaking Splunk Version < 6.2 - I don't know if this is now possible).

But if those start and end values are static, you could use the the EVAL- function in props.conf for this:

EVAL-<fieldname> = <eval statement>
* Use this to automatically run the <eval statement> and assign the value of the output 
  to <fieldname>. This creates a "calculated field."

Something like this could do the trick for you:

EVAL-Name = case((NUM>"100" AND NUM<"120"), "A", (NUM>"121" AND NUM<"180"), "B", (NUM>"180"), "C")

You can use this run everywhere command to test it:

index=_internal | head 1 | eval NUM="182" | eval Name=case((NUM>"100" AND NUM<"120"), "A", (NUM>"121" AND NUM<"180"), "B", (NUM>"180"), "C") | table NUM Name

Hope this helps ...

cheers, MuS

Re: Determine numerical value belong to intervals which stored in static lookup

somesoni2 — Wed, 29 Oct 2014 14:45:03 GMT

If you can change the format of static lookup file, then you can change it to have just the fields NAME and NUM, where NUM will be all the integer values from START to END.

You can generate the new lookup file (with NAME and NUM) from existing lookup (NAME, START, END) using following splunk search

|inputlookup yourOldLookup | eval NUM=mvrange(START,END+1) | mvexpand NUM | table NAME, NUM | outputlookup youNewLookup

Re: Determine numerical value belong to intervals which stored in static lookup

ejpulsar — Thu, 30 Oct 2014 06:55:30 GMT

We've tried this way. Unfortunately intervals is so big and resulting lookup over 10-20GB in size.

Re: Determine numerical value belong to intervals which stored in static lookup

ejpulsar — Thu, 30 Oct 2014 07:40:52 GMT

Got it!

With expanding case argument by subsearch
Thanks to all for help!

* | eval NUM=150 | eval NAME=case([|inputlookup name_range.csv| eval argument="NUM>=".interval_begin." AND  NUM<=".interval_end.",\"".name."\"" | stats values(argument) as argument | eval argument=mvjoin(argument,",")|return $argument])

Re: Determine numerical value belong to intervals which stored in static lookup

ejpulsar — Thu, 30 Oct 2014 07:46:26 GMT

As you see below, its possible now 🙂

Re: Determine numerical value belong to intervals which stored in static lookup

MuS — Thu, 30 Oct 2014 08:20:41 GMT

nice - looks like I misunderstood your initial question, because doing this by using only a lookup command is not possible (I think) 😉