topic Re: Lookup within time range in Splunk Search

Lookup within time range

stephenmeyers — Wed, 23 Dec 2020 20:10:32 GMT

I have data being fed to splunk in real time that I would like to tie to project IDs and budgets in a lookup table based on two criteria:

time falls between start_time and end_time in the lookup table
owner equals the owner in the lookup table

Here's the example data:

time	owner	Spent	Notes
2020-10-26 10:06:00	Bill	$30	Supplies
2020-10-26 12:16:41	Bill	$10	Food
2020-10-27 06:30:51	Jeff	$10	Food
2020-11-04 07:06:03	Bill	$15	Fuel
2020-11-04 08:01:19	Frank	$20	Fuel
2020-11-05 08:10:00	Bill	$20	Supplies
2020-11-05 08:12:21	Jeff	$10	Fuel

Here's the example lookup table:

project_id	owner	budget	start_time	end_time
1e	Bill	$200	2020-10-26 08:00:00	2020-11-04 12:00:00
2b	Jeff	$200	2020-10-21 08:00:00	2020-11-06 12:00:00
4a	Frank	$100	2020-11-04 08:00:00	2020-11-22 17:00:00
2a	Bill	$200	2020-11-05 08:00:00	2020-11-10 12:00:00

This is the output I am looking for:

time	project_id	budget	owner	Spent	Notes
2020-10-26 10:06:00	1e	$200	Bill	$30	Supplies
2020-10-26 12:16:41	1e	$200	Bill	$10	Food
2020-10-27 06:30:51	2b	$200	Jeff	$10	Food
2020-11-04 07:06:03	1e	$200	Bill	$15	Fuel
2020-11-04 08:01:19	4a	$100	Frank	$20	Fuel
2020-11-05 08:10:00	2a	$200	Bill	$20	Supplies
2020-11-05 08:12:21	2b	$200	Jeff	$10	Fuel

I'm not really sure how to use the lookup command on a range, or if it's possible. Any suggestions/solutions are welcome. Thanks in advance!

Re: Lookup within time range

scelikok — Thu, 24 Dec 2020 10:52:34 GMT

Hi @stephenmeyers, you should use time

transforms.conf [project_lookup] filename = project_lookup.csv time_field = start_time time_format = %Y-%m-%d %H:%M:%S props.conf [project_lookup] LOOKUP-project = project_lookup owner OUTPUT project_id budget

https://docs.splunk.com/Documentation/Splunk/8.1.1/Knowledge/Configureatime-boundedlookup#Defining_time-based_lookups

If this reply helps you an upvote is appreciated.

Re: Lookup within time range

stephenmeyers — Wed, 23 Dec 2020 20:09:17 GMT

It doesn't look like this solution would match by the time within start_time and end_time, but only by the owner field. In the example, owner Bill is on 2 different projects, but the time ranges are different.

The time range bit is the real hurdle I'm dealing with.

Re: Lookup within time range

scelikok — Thu, 24 Dec 2020 10:56:13 GMT

Hi @stephenmeyers, sorry, I edited my reply, the time field name was wrong. This way lookup will also check the _time of event to be bigger then start_time field in the lookup. Although it will not use the end_time field, it should show your desired result.