SQL "where not exists" equivalent for Elastic

Elastic Stack Kibana
1

Hello,

I have a set of stages I am logging. A GUID is on each record to tie the stage together.
Example:

Guid Stage
1 Stage 1
1 Stage 2
1 Stage 3
1 Stage 4
1 Stage 5
2 Stage 1
2 Stage 2
2 Stage 3
2 Stage 4
2 Stage 5

I am looking to put a visual together on any GUID which has any missing stages.
In SQL I would use something along the lines of:

select * from tbl as a
where stage = 1 and
not exists (
select * from tbl as b where
stage = 5 and a.guid = b.guid
)

Any advice on how to achieve this in elastic/kibana?

2

You can use the exists to filter for fields that do not have values defined for a field.

You can use the "Add filter" UI to create a does not exist filter.

15%20PM
Screen Shot 2019-03-05 at 1.10.15 PM.png962×686 45.8 KB

3

Hi Nathan,

Thanks for replying to my query.
For my example, there are no missing fields within each document. Each log has GUID and a Stage. Rather, I'm looking for a method of searching for documents where a corresponding document does not exist.
So for example If I have 3 logs:
GUID Stage
1 1
1 2
2 1

I'm looking for a way of reporting that GUID 2 does not have logs for all stages.

Any advice you can help me with?

Thanks,
Craig