How to search for events based on another event generated time

[apujari]

I have an event that is monitoring a host and triggers an alert when the host is down. I have used below search and its working fine:

index=main url=hosturl title=hostname response_code!=200

Now, I need to generate another alert when the host comes up next. For this, I am tracking the alert (for down host) time when it got triggered and then searching for the host monitoring event which got triggered after that to check if the host is up/down. Below is the search query which I tried and need help on.

index="_internal" sourcetype="scheduler" thread_id="AlertNotifier*" alert_actions="email" savedsearch_name="Host-Tracking" | stats latest(_time) as alerttime | append [search index=main title=hostname response_code=200 earliest=alerttime]

[apujari · Accepted solution]

Thanks everyone for the suggestions. This is how I configured the search query:

index=main url=hosturl title=hostname response_code=200 earliest=[search index="_internal" sourcetype="scheduler" thread_id="AlertNotifier\" alert_actions="email" savedsearch_name="Host-Tracking" | stats latest(_time) as alerttime | append [| makeresults | eval alerttime = now()] | return $alerttime]*

which is working fine as per my requirements.

[to4kawa]

good job.
please accept yours.

p.s. have you read my blog?

[apujari]

Yes, it was really helpful for me and gave enough idea to configure the above query. Thank you so much for the help.

[to4kawa]

@apujari
great! It was hard to read.

Happy splunking!

[DalJeanis]

Here's one way. Since we're not certain from your code what the host name might be in either kind of record, or how often you might want to run this, we wrote this is pseudocode for you.

(index="_internal" sourcetype="scheduler" thread_id="AlertNotifier*" alert_actions="email" savedsearch_name="Host-Tracking" 
earliest = however long you want to go back for alerts)

OR  (index=main title=hostname response_code=200
earliest = however often you want to run this )  

| eval myHost=case(this/is/a/scheduler/event, host field from scheduler record, 
                   this/is/a/main/index/event, host field from main record)
| eval alert_time=case(this/is/a/scheduler/event, _time)
| eval hostup_time=case(this/is/a/main/index/event, _time)
| stats max(alert_time) as alert_time max(hostup_time) as hostup_time by myHost 
| where hostup_time > alert_time

[DalJeanis]

What are the fields that need to be matched between the records of each kind?

Does "title" the actual name of the host?

[apujari]

Any suggestions please..

[to4kawa]

https://qiita.com/toshikawa/items/38e57c6f2b0514db109b
sorry, all text is japanese.
please translate them.
but SPL is useful for you.