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's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...