I have a PriorityEngines.csv lookup file like this -
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
I am not able to set the value of earliest dynamically from lookup file. Any help would be much appreciated.
This answer is loosely based on this Q&A:
Run this for
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
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.