how to Combine results of a lookup and an index?

[snipedown21]

Hi There.
I have a lookup like below.
end_date activity description start_date
1496325600 run XYZ 1488293100
1488293100 run ABC 1483279200

the activities have corresponding descriptions.

I have an index which contains a field 'activity', which has value either run or walk. In this case "run" only.
date_time activity person
1483279260 run john
1488293040 run george
1495979100 run abraham
1496325540 run derrick

The result I'm looking for, is to combine the lookup and the index rows, to get:

activity person description
run john ABC
run george ABC
run abraham XYZ
run derrick XYZ

How this should happen is, first the activity is matched, and if date_time lies between start_date and end_date, the value of the description is retrieved and stitched on the row of the corresponding date_time.
P.S. The lookup will be updated on a day to day basis, and the description might change. The start_date and end_date are indicative of validity of the description for the period.
Hence, ABC is valid between 1483279200 and 1488293100. As soon as a change in description is made, ABC is no longer valid, but events in the index belonging to that time range must have the description as "ABC"
The "run " activity is updated with XYZ and any event in the index between 1488293100 and 1496325600 must get associated to the description "XYZ".

I don't have a clue where to start this query off.
Thank you for your efforts.
Cheers.

[micahkemp]

You may be able to use time-based lookups (configured in transforms.conf).

time_field = <string>
* Used for temporal (time bounded) lookups. Specifies the name of the field
  in the lookup table that represents the timestamp.
* Defaults to an empty string, meaning that lookups are not temporal by
  default.

time_format = <string>
* For temporal lookups this specifies the 'strptime' format of the timestamp
  field.
* You can include subseconds but Splunk will ignore them.
* Defaults to %s.%Q or seconds from unix epoch in UTC an optional milliseconds.

max_offset_secs = <integer>
* For temporal lookups, this is the maximum time (in seconds) that the event
  timestamp can be later than the lookup entry time for a match to occur.
* Default is 2000000000 (no maximum, effectively).

min_offset_secs = <integer>
* For temporal lookups, this is the minimum time (in seconds) that the event
  timestamp can be later than the lookup entry timestamp for a match to
  occur.
* Defaults to 0.

In this case I think your transforms.conf might look something like this:

[lookupname]
filename = lookupfile.csv
timefield = start_date

This would allow you to run a search like

<base search> | lookup lookupname activity

The caveat is this doesn't limit results to those between start_date and end_date, only those that are at or after start_date. You may be able to tune the max/min_offset_secs sufficiently for your needs to accomplish what you want. If not the solution would be a bit more involved, potentially by comparing _time to end_time that your lookup emitted.