How To Get First and Last Events within a Set of Events

[Traer001]

Hi, I'm having trouble grabbing the first event of a specific type and the last consecutive event after that with the same type. For instance, my events for a user might look like this:

2021-04-02 14:45:11  User: 1 Network Error Happens

2021-04-02 14:42:57  User: 1 Error Recover

2021-04-02 14:41:33  User: 1 error id: 02 Happens at Location 1

2021-04-02 14:41:21  User: 1 error id: 02 Happens at Location 1

2021-04-02 14:41:12  User: 1 Error Happens 

2021-04-02 14:40:52  User: 1 Software Error Recover

2021-04-02 14:40:24  User: 1 Software Error Happens

In this case, I am trying to grab the 2nd and 5th events for where the latest recovered event happens and recovers so that I can later calculate the duration of the error. Currently, my query looks like this:

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE
| rex field=_raw "User:\s(?<user_id>\d+)"
| rex field=_raw "(?<error_type>\w+)\sError"
| rex field=_raw "(?<error_type>\w+)\serror\sid:\s(?<error_id>\d+)"
| rex field=_raw "\d+\s(?<error_type>\w+)\sRecover"
| rex field=_raw "\d+\s(?<error_type>\w+)\sHappen"
| eval action=if((like(_raw, "%Happen%")), "Happen", (if(like(_raw, "%Recover%"), "Recover", null)))
| where isnotnull(action)
| eval latest_recover=if(action="Recover", _time, null)
| streamstats latest(error_type) as latest_error_type latest(latest_recover) as _time earliest(_time) as early_time values(action) as actions by user_id
| where mvcount(actions)=2
| stats latest(error_type) as last_error_type latest(_time) as recovered latest(early_time) as happened by user_id
| eval error_duration=tostring((recovered - happened), "duration")
| eval happened_time=strftime(happened, "%Y-%m-%d %H:%M:%S")
| eval recovered_time=strftime(recovered, "%Y-%m-%d %H:%M:%S")
| fields - recovered, happened

However, this query does not appear to be retrieving the appropriate events. Is there something I can do to get the two events (or the range of events --like the 2nd through the 5th events)?

[scelikok · Accepted solution]

Hi @Traer001,

You can try with transaction command;

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE
| rex field=_raw "User:\s(?<user_id>\d+)"
| rex field=_raw "(?<error_type>\w+)\sError"
| rex field=_raw "(?<error_type>\w+)\serror\sid:\s(?<error_id>\d+)"
| sort - _time
| transation user_id startswith="Happens" endswith="Recover"
| table _time duration user_id error_type error_id