How to search for events before a given time if the event time is dynamically generated?

[Kanesol]

I have a search that finds bad events and I want to use the results to look back in time (a day for example) and see if any of those events exist in the past but are not logged as "bad".

For example:
Proxy blacklists a hostname based on an external feed and now I would like to go back from this event and investigate if there was activity before this time.

My attempts have followed the bellow:

index="proxy" proxyblock=false  [search index="the proxy" "badness" earliest=-1d | dedup hostname | return 1000 hostname]

The problem with this is that it will return events after as well as before (I am not so interested in events after the time of the first badness being detected because a) they were blocked b) I have other searches for that c) the feed is dynamic so something might be listed as bad for 2hrs and then get cleaned and then be listed as ok and I don't want to waste time on "good" events)

Thanks in advance

[martin_mueller · Accepted solution]

Try this:

  index="the proxy" "badness" earliest=-1d | dedup hostname | eval start = relative_time(_time, "-d")
| map maxsearches=42 search="search earliest=$start$ latest=$_time$ index=proxy proxyblock=false hostname=$hostname$"

That'll kick off a search for each returned hostname, using that event as the (non-inclusive) end of the one-day timerange.

[HeinzWaescher]

What about an approach like this:

 index="proxy" proxyblock=false  [search index="the proxy" "badness" earliest=-1d | dedup hostname | return 1000 hostname]
| stats earliest(_time) AS earliest_time, earliest(eval(if(flag="badness", _time, null()))) AS earliest_badness_time BY hostname
| where earliest_time<earliest_badness_time

The results should only show hostnames with activitiy before flagged as "bad"