Delete Specific Records from Elasticsearch Index

0
35

This is a simple tutorial on how to search and delete specific records from Elasticsearch index. Elasticsearch ships with a delete_by_query API that enables you to search Elasticsearch index for records that matches a specified query and delete them. So, how can you use delete_by_query API to search and delete specific records from Elasticsearch index?

Delete Specific Records from Elasticsearch Index

Required Permissions

Note that if the security features are enabled on your Elasticsearch cluster, you need to have the the following permissions to use delete_by_query API, you need to delete records.

How To Delete Documents & Data ...
How To Delete Documents & Data on iPhone
  • read
  • delete or write

Delete Specific Records from Elasticsearch Index

Consider our demo index, show in the screenshot below with a total of 8646 event records in a period of 3 days

record counts elasticsearch

Checking the count from the command line (modsec, is our index);

curl -XGET http://elk.kifarunix-demo.com:9200/modsec*/_count?pretty

Sample output;

{
  "count" : 8646,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  }
}

Out of these records, there are some notice events that I do not really want from my index.

curl -XGET -H 'Content-Type: application/json' http://elk.kifarunix-demo.com:9200/modsec*/_count?pretty -d '{
"query" : {
"match" : { "message": "notice" }
}
}'

Sample output;

{
  "count" : 69,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  }
}

We have 69 such records;

So, if you know the Elasticsearch field that has the keyword that you want to delete, then it becomes easy to use the delete_by_query API.

For example, to delete the above notice messages, the field is message and the keyword is “:notice“.

You can run the delete_by_query from Kibana > Management > Dev Tools > Console or from the terminal command line.

To delete from Kibana DevTools console, paste the query below and press the play button to execute the query command;

NOTE: modsec* is our index with (wildcard, * to match all indices whose names starts with modsec}.

To search all data streams and indices, omit this parameter or use * or _all.

POST /modsec*/_delete_by_query
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "message": ":notice"
          }
        }
      ]  
    }
  }
}
delete by query kibana devtools

From command line, you can use a command;

curl -XPOST "http://elk.kifarunix-demo.com:9200/modsec*/_delete_by_query?pretty" -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "message": ":notice"
          }
        }
      ]  
    }
  }
}'

It is also possible to further refine the query, for example, to delete specific event data record from specific host.

Consider the query below;

POST /test*/_delete_by_query
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "hostname": "sales.kifarunix.com"
          }
        },
        {
          "match": {
            "source_ip": "xx.xx.xx.xx"
          }
        }
      ]
    }
  }
}

When executed, it will search for events sent by the host sales.kifarunix.com (field is hostname)and contain source_ip: xx.xx.xx.xx and delete them;

You can run the above command on Kibana dev tools console or from command line.

You can also delete documents with a missing field.

For example, to delete records from the index logstash* sent by the host sales.kifarunix-demo.com and doesnt have a field called status_code, you could use such a delete query;

The search query below give total number of search events.

POST /logstash*/_search
{
  "query": {
    "bool" : {
      "must" : {
        "match" : { "host.name" : "sales.kifarunix-demo.com" }
      },
      "must_not" : {
        "exists" : { "field" : "status_code" }
      }
    }
  }
}
search records

To delete these events;

POST /logstash*/_delete_by_query
{
  "query": {
    "bool" : {
      "must" : {
        "match" : { "host.name" : "sales.kifarunix-demo.com" }
      },
      "must_not" : {
        "exists" : { "field" : "status_code" }
      }
    }
  }
}
delete events query

You can play around with query. Be careful not to delete your important records!!

And that is just it on how to search and delete specific records from Elasticsearch index.

Further Readings;

Delete by query API

Elasticsearch Boolean Queries

Other Tutorials

Setup Multinode Elasticsearch 8.x Cluster

Enable HTTPS Connection Between Elasticsearch Nodes

LEAVE A REPLY

Please enter your comment!
Please enter your name here