How to Do "Where Not Exists" Type Filtering in Kibana/elk?

In Kibana and Elasticsearch, you can perform a "WHERE NOT EXISTS" type of filtering (i.e., finding documents where a field does not exist) by using a must_not clause in an Elasticsearch query or applying the appropriate filter in Kibana's interface.

1. Using the Kibana Discover Tab

Steps:

1. Open the Discover Tab:
   • In Kibana, go to the Discover tab.
2. Add a Filter for Non-Existent Fields:
   • At the top of the Discover tab, click Add a filter.
   • In the Field dropdown, select the field you want to filter by (e.g., user_id, email, etc.).
   • In the operator dropdown, select "is not".
   • In the value dropdown, select "exists".

This will filter out all documents where the field exists, effectively returning documents where the field does not exist.

Example:

• If you want to filter documents where the field email does not exist, you would select:
  • Field: email
  • Operator: is not
  • Value: exists

This creates a "WHERE NOT EXISTS" condition for the field email.

2. Using KQL (Kibana Query Language) in Discover/Visualizations

You can use the Kibana Query Language (KQL) in the search bar to filter out documents where a specific field doesn't exist.

KQL Syntax:

To find documents where a field does not exist, you can use the following syntax:

!field_name: *

Example:

To find documents where the field email does not exist, you would type:

!email: *

This will filter out all documents where the email field is present, leaving only those where the field does not exist.

3. Using Lucene Query Syntax in Discover/Visualizations

Alternatively, if you're using Lucene query syntax in Kibana, you can perform a similar operation by using the following query:

-_exists_:field_name

Example:

To filter documents where the field email does not exist, you would type:

-_exists_:email

This filters out all documents where the email field exists.

4. Using Elasticsearch Query DSL in Dev Tools

If you want to perform the query directly using the Elasticsearch Query DSL via Dev Tools, you can use the must_not clause to achieve the "WHERE NOT EXISTS" filter.

Query Syntax:

GET /your_index/_search
{
  "query": {
    "bool": {
      "must_not": {
        "exists": {
          "field": "your_field_name"
        }
      }
    }
  }
}

Example:

To retrieve documents where the field email does not exist:

GET /your_index/_search
{
  "query": {
    "bool": {
      "must_not": {
        "exists": {
          "field": "email"
        }
      }
    }
  }
}

This query will return all documents where the email field does not exist.

Summary:

• In Kibana Discover: Use filters with "is not" exists to filter documents where the field does not exist.
• Using KQL: Use the !field_name: * syntax to exclude documents where the field exists.
• Using Lucene: Use _exists_:field_name to filter out documents where the field exists.
• In Elasticsearch DSL: Use the must_not clause with an exists query in Elasticsearch's Query DSL for more advanced queries.

These methods give you flexibility to filter documents in Kibana or Elasticsearch where specific fields are missing, which is equivalent to a SQL-style "WHERE NOT EXISTS" condition.

Got an article suggestion? Let us know

Explore more

Kibana Logstash ELK

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.