How can i use a field value from lookup file to set my earliest time for search dynamically ?

[Upas02]

Hi,
I have a PriorityEngines.csv lookup file like this -
EngineName,TimePeriod
Engine1,5
Engine2,10
Engine3,12

I have written the query were I am checking the number of events for each engine for last 5 minutes
index=myindex earliest=-5m [ | inputlookup PriorityEngines | fields EngineName ] | stats count by EngineName

The requirement is that, instead of hard coding 5 minutes I need to pick the value of earliest from the lookup file TimePeriod field. That is I need to check number of event count for
Engine1 earliest=-5m,
Engine2 earliest=-10m,
Engine2 earliest=-12m

I am not able to set the value of earliest dynamically from lookup file. Any help would be much appreciated.

[woodcock]

This answer is loosely based on this Q&A:

https://answers.splunk.com/answers/668283/lookup-table-with-condition.html#answer-669117
Run this for All time:

index=myindex
[|inputlookup PriorityEngines
 | stats max(TimePeriod) AS search
 | eval search = "earliest=-" . search . "m" ]
[|inputlookup PriorityEngines |
 | eval TimePeriod = now() - (TimePeriod * 60)
 | table EngineName TimePeriod
 | rename TimePeriod AS time>
 | format
 | rex field=search mode=sed "s/\"//g s/EngineName=/EngineName=\"/g s/ AND/\" AND/g" ]
| stats count BY EngineName

[DalJeanis]

While @jkat54's answer would work, I REALLY prefer not to use map, since it runs like a wounded dog. You can do it that way if you have a small number of engines, like less than a dozen.

The easiest way to implement this for general systems is to hard code the earliest on your search as the highest number of minutes you are going to allow in the file.

Let's say 15m is your max.

Let's say that the Engine field in the event is called myEngine.

Let's also say that you leave the lookup in minutes - this code would be a little simpler if we didn't have to multiply by 60, but that's okay for now.

earliest=-15m@m index=foo your other search terms here
| lookup  PriorityEngines.csv EngineName as myEngine OUTPUT TimePeriod
| eval TimePeriod=coalesce(TimePeriod,1000)
| where _time + 60*TimePeriod >= now()

That will eliminate any events that are older than the number of minutes you have chosen for that Engine.

My preference would be to enter your lookup table as the number of seconds, and remove the 60* from the formula.

[jkat54]

|inputlookup lookupName.csv
| fields EngineName TimePeriod

| map [ search index=... earliest=$TimePeriod$ EngineName=$EngineName$]
| stats ...