topic How to build a lookup table with a condition? in Splunk Search

How to build a lookup table with a condition?

joydeep741 — Tue, 29 Sep 2020 20:11:25 GMT

I have a lookup of epoch times:

epoch_time_lookup.csv

Start Time  End Time
1529737700  1529737800
1529737600  1529737750
1529737800  1529737900
1529737720  1529737722

I have a search which gives me results with 2 columns. EPOCH TIME and AVAILABILITY

EPOCH               AVAIL
1529737700  100
1529737600  100
1529737800  0
1529737720 100

Now I want to build a search, whenever AVAIL == 0 , check if the EPOCH is between any START and END times in epoch_time_lookup.csv lookup.

If yes, make availability 100.

Eg:

index= abc sourcetype=xyz | eval EPOCH = _time | eval AVAILABILITY = availability_field | **eval availability = (if EPOCH  in START and END of lookup) ,100, 0)**

Need help in building the logic.

Re: How to build a lookup table with a condition?

woodcock — Sat, 30 Jun 2018 16:30:06 GMT

This solution is based on this other Q&A:

https://answers.splunk.com/answers/648345/timechart-volume-per-hour-same-day-over-several-we.html
Try this:

index= abc sourcetype=xyz
| eval AVAILABILITY = availability_field
| eval availability = if([|inputlookup epoch_time_lookup.csv 
                          | rename "Start Time" AS time>, "End Time" AS time< 
                          | format 
                          | rex field=search mode=sed "s/time/_time/g s/\"//g"] ,100, 0)

Re: How to build a lookup table with a condition?

joydeep741 — Sun, 01 Jul 2018 13:52:39 GMT

@woodcock
I corrected it. Thanks a lot.
What a genius way of doing this.