Append lookup to results with where clause

[dataisbeautiful]

Hi all

I am trying to append data to results based on a file.

Example temperature and pressure are stored at 1 sample per minute all the time. The times when a batch was in production is logged in a look up file. Batch IDs are generated and stored after logging of sensor values to Splunk.

Base search:

index=ndx sourcetype=srctp (sensor=temperature OR sensor=pressure) earliest=-1d@d latest=@d
| table _time sensor value

Result:

_time	sensor	value
...	temperature	75
...	pressure	100

Look up file has 3 columns, the start and finish time for a batch and the ID

startTime	finishTime	batchID
...	...	b1
...	...	b2
...	...	b3

For each row in the result table of the base search, I want to append the batch ID to give

_time	sensor	value	batchID
...	temperature	75	b2
...	pressure	100	b2

I have tried with

| lookup batch.csv _time >= startTime, _time <= finishTime OUTPUTNEW batchID 

The lookup needs to find a batch where the value of _time >= startTime AND _time<=finishTime

I can't see anything for lookup that works with this sort of conditions. Only where fields are a direct match.

Any ideas would be appreciated, thanks in advance.

[dataisbeautiful · Accepted solution]

I figured it out

| lookup batch.csv OUTPUT startTime finishTime
| eval startTime = max(mvmap(startTime, if(startTime <= _time, startTime, null())))
| eval finishTime = min(mvmap(finishTime, if(finishTime >= _time, finishTime, null())))
| lookup batch.csv startTime finishTime OUTPUT batchID 

[livehybrid]

Hi @dataisbeautiful 

Do the timings of batches overlap? Presumably if you had a batch starting each day at midnight, the batch you would be looking for would be the previous midnight?

If that is the case then you may be able to look at using a time-based lookup, just using the start time of the batches as the lookup field. This should work by returning the batch number where the _time of the event is after the start_time for that batch (and less than the start_time of the next batch) - if that makes sense?

Have a look at:

https://community.splunk.com/t5/Splunk-Search/How-to-configure-a-time-based-lookup-Temporal-lookup/m...

https://docs.splunk.com/Documentation/Splunk/9.4.0/Knowledge/Defineatime-basedlookupinSplunkWeb

Please let me know how you get on and consider accepting this answer or adding karma this answer if it has helped.
Regards

Will

[dataisbeautiful]

Hi @livehybrid thanks for the links.

I'll add more details about the batches. They can be 1min in length to several hours. They are not regular in length unfortunately, it depends on the process and numbers etc. There may be several batches in a day too, up to 50 on some days.

Looking at: https://docs.splunk.com/Documentation/Splunk/9.4.0/Knowledge/Defineatime-basedlookupinSplunkWeb

If we pre-set a lookahear time, this could be too short and not give an ID or too big and give mutiple IDs?

Looking at: https://community.splunk.com/t5/Splunk-Search/How-to-configure-a-time-based-lookup-Temporal-lookup/m...

I can do a search for a single result using | inputlookup and | addinfo, that works fine. It's doing this on a FOR loop for each result that I'm stuck with.

I've tried this, but feels very inefficient

• Add a column with just the date
• Lookup all ID for the date
• Use mvexpand to split multiple IDs into single events
• Lookup for start and finish times for id
• Where to filter on _time between start and finish