Splunk Search

Application Logging: Select events that don't have certain values, not exclude- but never had those events

charbaugh77
Explorer

I have a .net core application that logs various events with properties (WorkItem, EventName, etc).

I need to query WorkItems that have never had certain events kinda like a SQL NOT Exists.  I can filter out the events I don't want but I cannot select where they never existed.

WorkItem | Event
1234          | Task Created
1234          | Retrieval Ready
1234          | NIGO Completed
5678          | Retrieval Ready
9012          | Task Created
9012          | Retrieval Ready

The query should return all WorkItems with events that equal Retrieval Ready and not NIGO Completed...example result.

WorkItem | Event
5678          | Retrieval Ready
9012          | Retrieval Ready


Labels (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @charbaugh77,

you have two choices:

  • use a subsearch, following the hint from @charbaug77
  •  goup events anf filter them.

the first solution is easier but in general has the limitation of 50,000 results and probably it isn't your case.

Anyway, first solution:

index=your_index NOT [ search index=your_index "Task Created"="NIGO Completed" | fields WorkItem ]
| table WorkItem Event

second option:

index=your_index 
| stats values(Event) AS Event count(eval(if("Task Created"="NIGO Completed",1,0))) AS check BY WorkItem
| where check=0
| mvexpand Event
| table WorkItem Event

Ciao.

Giuseppe

 

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @charbaugh77,

you have two choices:

  • use a subsearch, following the hint from @charbaug77
  •  goup events anf filter them.

the first solution is easier but in general has the limitation of 50,000 results and probably it isn't your case.

Anyway, first solution:

index=your_index NOT [ search index=your_index "Task Created"="NIGO Completed" | fields WorkItem ]
| table WorkItem Event

second option:

index=your_index 
| stats values(Event) AS Event count(eval(if("Task Created"="NIGO Completed",1,0))) AS check BY WorkItem
| where check=0
| mvexpand Event
| table WorkItem Event

Ciao.

Giuseppe

 

charbaugh77
Explorer

Thank you @gcusello!

 

I'm trying to understand the second option.  This doesn't make sense to me.  How is Task Created=NIGO Completed?

count(eval(if("Task Created"="NIGO Completed",1,0)))


This does seem to work better but I would like to understand more for future reference.


Corey

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @charbaugh77,

the second option is very useful when you have to check a condition and you cannot use subsearches because the subsearch could have more than 50,000 results (this is the limit of subsearches).

It works grouping for the common key and identifying a condition in bot the main and secondary search (in your case the eval command).

Ciao.

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors 😉

charbaugh77
Explorer

Hello again @gcusello 🙂

I'm wonder how eval part works.  To me this would always be 0.  When would this condition every equal 1 and how does it work?

if("Task Created"="NIGO Completed",1,0)

 

Thanks again 🙂

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @charbaugh77,

in few words, the count in stats command runs only when the condition is matched.

I needed much time to understand how to use eval in stats!

In addition, a little hint: avoid field names (as e.g. "Task Created") using spaces or dots or "-", because you have always to use quotes and somethimes in the eval command it doesn't work.

Ciao.

Giuseppe

0 Karma

charbaugh77
Explorer

@gcusello - Task Created is not a field name.  Its a field value for the Events field.  Did you get confused?  See the original post, please.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @charbaugh77,

sorry! use "Event" as field name in all the eval statements!

Ciao.

Giuseppe

charbaugh77
Explorer

@gcusello Thank you...this was very confusing to me.

 

Cheers

0 Karma

charbaugh77
Explorer

I think if I use NOT [subsearch] this will work.

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

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 ...