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:
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!
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.
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
If this reply helps you an upvote is appreciated.
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.