topic Re: Splunk Query in Splunk Enterprise

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

ShubhamWanne — Wed, 21 Dec 2022 15:02:09 GMT

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.

Re: Splunk Query

richgalloway — Wed, 21 Dec 2022 14:30:55 GMT

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.

Re: Splunk Query

ShubhamWanne — Wed, 21 Dec 2022 15:58:16 GMT

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.

Re: Splunk Query

richgalloway — Wed, 21 Dec 2022 20:12:48 GMT

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.

Re: Splunk Query

ShubhamWanne — Thu, 22 Dec 2022 02:28:54 GMT

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

Thank you.

Re: Splunk Query

richgalloway — Thu, 22 Dec 2022 14:41:49 GMT

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.