Splunk Search

Lookup based on range of value

Motivator

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..

0 Karma
1 Solution

Champion

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.

View solution in original post

Champion

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.

View solution in original post

Builder

Good answer mate, +1

0 Karma

Motivator

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)

0 Karma

Champion

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.

0 Karma