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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...