Is there a way calculate the duration between the status=holding and status=end also?

[neerajs_81]

Hi All,  I am using transaction to group my DDOS appliance events based on a field called status which has values like starting, holding and end.  

| transaction eventID startswith=starting endswith=end maxspan=12h 

Raw events:  Notice there is a status value in every event.

Jun 20 13:58:05 172.x.x.x logtype=attackevent;datetime=2022-06-20 13:57:38+08:00;eventID=7861430818955774485;status=starting;dstip=10.x.x.x;eventType=DDoS Attack Alert;severity=high;description=pps=3450,bps=39006800;subtype=FIN/RST Flood;attackDirection=inbound;
Jun 20 13:59:05 172.x.x.x logtype=attackevent;datetime=2022-06-20 13:58:07+08:00;eventID=7861430818955774485;status=holding;dstip=14.x.x.x;eventType=DDoS Attack Alert;severity=high;description=pps=0,bps=0;subtype=FIN/RST Flood;attackDirection=inbound;
Jun 20 14:00:07 172.x.x.x logtype=attackevent;datetime=2022-06-20 13:59:07+08:00;eventID=7861430818955774485;status=end;dstip=14.x.x.x;eventType=DDoS Attack Alert;severity=high;description=pps=0,bps=0;subtype=FIN/RST Flood;attackDirection=inbound;

As you know, there is a duration field created that has the duration of the entire transaction start to end.

Now, Is there a way calculate the duration between the status=holding and status=end also ?   Basically another transaction command in the same query but that will have startswith=holding and endswith=end ?

Requirement is to find out how long was the attack in holding status. I am assuming adding another sub transaction may help to meet this. 

[gcusello · Accepted solution]

Hi @neerajs_81,

you could separate events after the transaction grouping and use stats to calculate the second duration, something liek this:

your_search
| transaction eventID startswith=starting endswith=end maxspan=12h 
| streamstats prog
| rename duration AS full_duration
| fields _row prog _time eventID status duration
| mvexpand _row
| stats 
   values(eval(if(status="holding"),_time,"")) AS earliest
   values(eval(if(status="end"),_time,"")) AS latest
   values(full_duration) AS full_duration
   BY eventID prog
| eval duration=latest-earliest
| table event_id full_duration duration

Ciao.

Giuseppe