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

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

charbaugh77 — Thu, 28 Apr 2022 20:56:08 GMT

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

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

charbaugh77 — Thu, 28 Apr 2022 21:08:10 GMT

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

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

gcusello — Fri, 29 Apr 2022 06:50:00 GMT

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

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

charbaugh77 — Mon, 02 May 2022 18:01:17 GMT

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

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

gcusello — Tue, 03 May 2022 06:35:10 GMT

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 😉

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

charbaugh77 — Tue, 03 May 2022 14:12:25 GMT

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 🙂

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

gcusello — Tue, 03 May 2022 14:23:27 GMT

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

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

charbaugh77 — Tue, 03 May 2022 14:57:25 GMT

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

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

gcusello — Tue, 03 May 2022 15:02:36 GMT

Hi @charbaugh77,

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

Ciao.

Giuseppe

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

charbaugh77 — Tue, 03 May 2022 15:06:00 GMT

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

Cheers