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
Legend

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
Legend

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
Legend

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
Legend

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
Legend

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!

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...