Lookup based on range of value

[melonman]

Hi

I am looking for a sample external lookup script or custom command that takes one field value from evens and compare 2 values, low/high in CSV to get a looked up field value. I would really appreciate if anyone could shed on my question below.

sample events:
2015/04/01T10:00:01 ip=10.0.0.5 temp=10
2015/04/01T10:00:01 ip=10.0.1.200 temp=13
2015/04/01T10:00:01 ip=10.0.1.30 temp=15

lookup file for ip (CSV):
ip_start, ip_end, group
10.0.0.1,10.0.0.10,G1
10.0.0.10,10.0.0.100,G2
10.0.0.200,10.0.1.50,G3

I want to do:
sourcetype=myevent | lookup ip_lookup ip RANGE ip_start ip_end OUTPUT group | table ip group

ip group

10.0.0.5 G1
10.0.1.200

10.0.1.30 G3

I also want to do temp range lookup with the sample events above.

Pointing me to the sample code would be really appreciated..

[jeffland · Accepted solution]

Why don't you use eval instead of lookup? You could simply
| eval group=case(...) | table ip group
You'd still have to come up with a smart way to compare your ips, for example

| rex field=ip "\d+.\d+.(?<b>\d+).(?<a>\d+)"

Then you could combine that for your case above, such as ((a == 0 AND b < 11), "G1", (a == 0 AND b < 100), "G2", ...)
Compared to an explicit lookup, this should be faster since there's no I/O. I'm not sure if this is the best solution though, you could possibly work with cidrmatch but I don't know much about that, sorry.

[markthompson]

Good answer mate, +1

[melonman]

I could simply use case if there are a few pair of min/max.
The reason I can not use eval + case is :
- There will be 100s lines to test for the range and
- The list will change once in a few days, and provided in CSV format.
(I don't want to hard code the case statement for 100s of combination)

[jeffland]

That's a good point against eval and case.

Can you get your CSV output to explicitly output every IP and not just start and end? That way, you could lookup your exact IP. It's not a pretty way though. As I mentioned, this may be a case for cidrmatch but I'm not sure about that. Otherwise, this could be a case for a custom search script: http://docs.splunk.com/Documentation/Splunk/6.2.2/Search/Aboutcustomsearchcommands - if you know python, that might be a really easy one.