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

[charbaugh77]

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

[gcusello · Accepted solution]

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]

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

[gcusello]

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]

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 🙂

[gcusello]

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

[charbaugh77]

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

[gcusello]

Hi @charbaugh77,

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

Ciao.

Giuseppe

[charbaugh77]

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

Cheers

[charbaugh77]

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