How to write a search to implement NOT IN functionality in SQL along with eval?

[ShubhamWanne]

I am new to splunk and working on a complex query where;
I am supposed to implement NOT IN functionality in SQL along with eval

I want to skip all the IN-PROGRESS events who later went into COMPLETED state, and display all the events which are still in IN-PROGRESS state.

For example
COMPLETED events:

event1
event5
event4
event7

IN-PROGRESS events:

event3
event1
event4

Expected result
event3

Given below are the queries to fetch COMPLETED and IN-PROGRESS events:

index=abc message="*COMPLETED*" | eval splitStr=split(message, ",") | eval eventName=mvindex(splitStr,1) | table eventName
index=abc message="*IN-PROGRESS*" | eval splitStr=split(message, ",") | eval eventName=mvindex(splitStr,1) | table eventName


Thank you in advance.

[richgalloway]

Collect all IN-PROGRESS and COMPLETED events.  Keep only the most recent event for each unique identifier.  Discard the COMPLETED events and what is left will be those IN-PROGRESS and not COMPLETED.

index=abc (message="*IN-PROGRESS*" OR message="*COMPLETED*") 
| eval splitStr=split(message, ",") 
| eval eventName=mvindex(splitStr,1) 
| dedup eventName
| where NOT match(message, "COMPLETED")
| table eventName

[ShubhamWanne]

I appreciate the quick reponse @richgalloway 

but this query fails in scenario where:
COMPLETED (C)
Event1, Event2, Event3 and

IN-PROGRESS (I-P)
Event1, Event2, Event2, Event2

Then

index=abc (message="*IN-PROGRESS*" OR message="*COMPLETED*") 
| eval splitStr=split(message, ",") 
| eval eventName=mvindex(splitStr,1) 

 This query will list
Event1(C), Event1(I-P), Event2(I-P),Event2(I-P),Event2(I-P),Event2(C),Event3(C)

and  

| dedup eventName

will return
 Event1(C), Event2(I-P),Event3(C)

and

| where NOT match(message, "COMPLETED")

will return 

Event2


Ideally, result should be 0.

Thank you.

[richgalloway]

My query assumes events are in reverse time order, which is the default.  If that is not the case for your data then please advise so I can revise the query.

[ShubhamWanne]

IN-PROGRESS events are random and can have duplicate events name, they don't follow any kind of order as such.

Thank you.

[richgalloway]

If IN-PROGRESS arrives after COMPLETE then I would think the event is now in progress.  OTOH, if the logic is if COMPLETE is seen at any time then IN_PROGRESS must be ignored then try this query.

index=abc (message="*IN-PROGRESS*" OR message="*COMPLETED*") 
| eval splitStr=split(message, ",") 
| eval eventName=mvindex(splitStr,1) 
| stats values(message) as messages by eventName
| where isnull(mvfind(messages, "COMPLETED"))
| table eventName