Lookup within time range

[stephenmeyers]

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:

1. time falls between start_time and end_time in  the lookup table
2. 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!

[scelikok]

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. 

[scelikok]

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_t...

If this reply helps you an upvote is appreciated.

[stephenmeyers]

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.