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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...