Splunk Search

How to build a lookup table with a condition?

Path Finder

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.

Tags (2)
1 Solution

Esteemed Legend

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)

View solution in original post

0 Karma

Esteemed Legend

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)

View solution in original post

0 Karma

Path Finder

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!