I have this query in which I join with another query. I want to take the earliest event of the first query, go back a certain amount of time (days, let's say a day = 86400 seconds) and plug that value into the earliest function in the second query. I have tried reformatting the time as stated in the docs and I keep getting a "Invalid value start for time term earliest". Is there a way, or a simpler way, of doing this?
source1 | stats earliest(_time) AS start | eval start=start-86400 | eval start=strftime(start, "%m/%d/%Y :%H:%M:%S") | eval event=_time | . . . | join dest_ip [search source2 _time<event **earliest=start** | . . .| dedup dest_ip sortby -_time]
I would approach this with a subsearch. The subsearch (when filtering) is evaluated first, and its results are passed to the outer search, similar to "here documents" in a shell.
It looks to me like you want "find thing one, then go back up to a day and find thing two"? To make the search a bit easier, I'd actually turn the two around, maybe something like this.
source2 [ search source1 | stats earliest(_time) AS earliest, latest(_time) AS latest | eval earliest=earliest-86400 | return earliest, latest ] | join dest_ip [ search source1 ... ] | dedup dest_ip | sort - _time
My thinking here is that the time range picker will apply to the subsearch in the join, but our time math will govern the time range of the search on source2.
This seems to be closer to what I need. However, the reason source1 is the first query is so I can restrict source2's events to the ones that happened BEFORE each event in source1. Otherwise, current events from source1 will show up and distort the accuracy. (that's what the _timeLESSTHANevent was for) ---Sorry weird formatting when using the less than symbol.
Edited. However, seconds are not the problem. The fact that the search will not run is the focus.
Yeah, no worries, I was just in process typing up the search idea.
A day is 86400 seconds.