How to use stats instead of transaction command ?

[appu]

log1 : user_id , status=interrupt,

log2 : user_id, status = success

Hi All,

I want to find user_ids that failed due to an interrupt after initial success state for a period last 30days.

I tried transaction command, the query runs slow and same with subsearch. In Stats, i am not able to figure out how to make sure that I search only "interrupt" that comes after the success and not the ones that occurred before it.

In my base query i take only the logs with two states. Since I run for last 30days, i'm not able to make sure if the interrupt has occurred after the success or it occurred separately. Time duration between these two events mostly wont be more than 30s.

What I've right now is : time_duration not more than 30s, User_id and interrupt should come after success.

Need some advice on how to achieve this with Stats command.

with transaction i've tried something like this.

base_query status="success" OR status="interrupt"  | transaction user_id startswith=(status="success") endswith=(status="interrupt") maxspan=30s | stats count by user_id

I had checked other answers with respect to Transaction command, but i did not find those as satisfactory to what I'm looking for.

[kamlesh_vaghela]

@appu 

I'm not sure but can you please try this?

base_query status="success" OR status="interrupt"  
| bin _time span=30s
| stats min(eval(if(status="success",_time,null()))) as success_time max(eval(if(status="interrupt",_time,null()))) as interrupt_time by _time, user_id
| where success_time=interrupt_time
| stats count by user_id

For playing with sample data.

| makeresults | timechart span=5s count | eval count=1 | accum count | eval status=case(count%3==1,"success",count%3==0,"interrupt"),user_id= ceil(count/3) , user_id= ceil(count/100)
| rename comment as "Upto Now is sample data only" 
| bin _time span=30s
| stats min(eval(if(status="success",_time,null()))) as success_time max(eval(if(status="interrupt",_time,null()))) as interrupt_time by _time, user_id
| where success_time=interrupt_time
 | stats count by user_id

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.