Exclude multiple events based on a common field

[neerajs_81]

Hi All,  Below are 2 sets of raw events from my DDOS appliance.  The sets are separated based on the eventID field.  In the 1st set, there are 3 events for the eventID=7861430818955774485  while in the 2nd set the eventID=7861430818955774999 has 2 events.

Also, there is field called status which is a multi value field.  You will notice, in the 1st set the status has 3 values- starting, holding and end.   While in 2nd one, status has 2 values starting and hold.  

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;


Jun 20 13:58:05 64.x.x.x logtype=attackevent;datetime=2022-06-20 13:57:38+08:00;eventID=7861430818955774999;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 64.x.x.x logtype=attackevent;datetime=2022-06-20 13:58:07+08:00;eventID=7861430818955774999;status=holding;dstip=14.x.x.x;eventType=DDoS Attack Alert;severity=high;description=pps=0,bps=0;subtype=FIN/RST Flood;attackDirection=inbound;

My requirement is only to show (in table or stats) those eventIDs that do not have a status=end in their span.  Basically from the above raw events, the  search should filter out the 1st set completely and only show data about the 2nd set because there is no status=end for eventID=7861430818955774999 .  How to achieve this?

I am using below search,  but it still ends up listing the 1st eventID also.

...base search...
| eval start=if(in(status, "starting"), _time, NULL), end =if(in(status,"holding"), _time, NULL)
| stats earliest(start) as start_time , latest(end) as end_time, values(eventID) , values(status), values(eventType), values(severity), values(dstip), values(subtype) dc(status) as dc_status  BY eventID 
| where dc_status > 2
| eval duration=tostring((end_time-start_time), "duration")
| convert ctime(start_time)| convert ctime(end_time)
| rename values(*) as *

If i put status!=end in my base search,  it still shows the other events where status=starting or holding for that eventID.   I want to eliminate the eventID completely  ( as in all events for that eventID) from my results which have status=end. 

Any suggestions?

[ITWhisperer]

Shouldn't this be

| where dc_status = 2

[neerajs_81]

Thank you for responding to my question. My bad i should have mentioned, there are some events where status has more than 2 values excluding "end" also.  So if i use | where dc(status)=2,  that doesn't take into consideration those events.

[marysan · Accepted solution]

Hello
I changed your query : (you must use match command instead of  status!=end)

| stats values(status) as status BY eventID
| eval flag=if(match(status,"end"),1,0)
| where flag=0

[neerajs_81]

Thank you. That was helpful