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
Hi @charbaugh77,
you have two choices:
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
Hi @charbaugh77,
you have two choices:
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
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
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 😉
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 🙂
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
@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.
I think if I use NOT [subsearch] this will work.