Splunk Enterprise

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

ShubhamWanne
Explorer

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.

Labels (1)
Tags (3)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

ShubhamWanne
Explorer

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.

Tags (3)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

ShubhamWanne
Explorer

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

Thank you.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...