Splunk Search

Lookup based on range of value

melonman
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

jeffland
SplunkTrust
SplunkTrust

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

jeffland
SplunkTrust
SplunkTrust

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
Builder

Good answer mate, +1

0 Karma

melonman
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

jeffland
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...