{"id":14538,"date":"2022-10-22T14:15:59","date_gmt":"2022-10-22T11:15:59","guid":{"rendered":"https:\/\/kifarunix.com\/?p=14538"},"modified":"2024-03-09T22:54:57","modified_gmt":"2024-03-09T19:54:57","slug":"delete-specific-records-from-elasticsearch-index","status":"publish","type":"post","link":"https:\/\/kifarunix.com\/delete-specific-records-from-elasticsearch-index\/","title":{"rendered":"Delete Specific Records from Elasticsearch Index"},"content":{"rendered":"\n<p>This is a simple tutorial on how to search and delete specific records from Elasticsearch index. Elasticsearch ships with a <a href=\"https:\/\/www.elastic.co\/guide\/en\/elasticsearch\/reference\/current\/docs-delete-by-query.html\" target=\"_blank\" rel=\"noreferrer noopener\">delete_by_query<\/a> API that enables you to search Elasticsearch index for records that matches a specified query and delete them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Delete Specific Records from Elasticsearch Index<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Required Permissions<\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>read<\/code><\/li>\n\n\n\n<li><code>delete<\/code>&nbsp;or&nbsp;<code>write<\/code><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Delete Specific Records from an Index<\/h3>\n\n\n\n<p>Consider our demo index, show in the screenshot below with a total of 8646 event records in a period of 3 days<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1909\" height=\"870\" src=\"https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/record-counts-elasticsearch-index.png\" alt=\"\" class=\"wp-image-14539\" title=\"\" srcset=\"https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/record-counts-elasticsearch-index.png?v=1666419721 1909w, https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/record-counts-elasticsearch-index-768x350.png?v=1666419721 768w, https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/record-counts-elasticsearch-index-1536x700.png?v=1666419721 1536w\" sizes=\"(max-width: 1909px) 100vw, 1909px\" \/><\/figure>\n\n\n\n<p>Checking the count from the command line (<strong>modsec<\/strong>, is our index);<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>curl -XGET http:\/\/elk.kifarunix-demo.com:9200\/modsec*\/_count?pretty<\/code><\/pre>\n\n\n\n<p>Sample output;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>{\n  \"count\" : <strong>8646<\/strong>,\n  \"_shards\" : {\n    \"total\" : 1,\n    \"successful\" : 1,\n    \"skipped\" : 0,\n    \"failed\" : 0\n  }\n}<\/code><\/pre>\n\n\n\n<p>Out of these records, there are some <strong>notice<\/strong> events that I do not really want from my index.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>curl -XGET -H 'Content-Type: application\/json' http:\/\/elk.kifarunix-demo.com:9200\/modsec*\/_count?pretty -d '{\n\"query\" : {\n\"match\" : { \"message\": \"notice\" }\n}\n}'<\/code><\/pre>\n\n\n\n<p>Sample output;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>{\n  \"count\" : 69,\n  \"_shards\" : {\n    \"total\" : 1,\n    \"successful\" : 1,\n    \"skipped\" : 0,\n    \"failed\" : 0\n  }\n}<\/code><\/pre>\n\n\n\n<p>We have 69 such records;<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>For example, to delete the above notice messages, the field is <strong>message<\/strong> and the keyword is &#8220;<strong>:notice<\/strong>&#8220;.<\/p>\n\n\n\n<p>You can run the <strong>delete_by_query<\/strong> from <strong>Kibana &gt; Management &gt; Dev Tools &gt; Console<\/strong> or from the terminal command line.<\/p>\n\n\n\n<p>To delete from Kibana DevTools console, paste the query below and press the play button to execute the query command;<\/p>\n\n\n\n<p><strong>NOTE: modsec*<\/strong> is our index with (wildcard, * to match all indices whose names starts with modsec}.<\/p>\n\n\n\n<p>To search all data streams and indices, omit this parameter or use&nbsp;<code>*<\/code>&nbsp;or&nbsp;<code>_all<\/code>.<\/p>\n\n\n\n<pre class=\"scroll-box\"><code>\nPOST \/modsec*\/_delete_by_query\n{\n  \"query\": {\n    \"bool\": {\n      \"must\": [\n        {\n          \"match\": {\n            \"message\": \":notice\"\n          }\n        }\n      ]  \n    }\n  }\n}\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1914\" height=\"617\" src=\"https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/delete_by_query_kibana-devtools.png\" alt=\"\" class=\"wp-image-14540\" title=\"\" srcset=\"https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/delete_by_query_kibana-devtools.png?v=1666422234 1914w, https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/delete_by_query_kibana-devtools-768x248.png?v=1666422234 768w, https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/delete_by_query_kibana-devtools-1536x495.png?v=1666422234 1536w\" sizes=\"(max-width: 1914px) 100vw, 1914px\" \/><\/figure>\n\n\n\n<p>From command line, you can use a command;<\/p>\n\n\n\n<pre class=\"scroll-box\"><code>\ncurl -XPOST \"http:\/\/elk.kifarunix-demo.com:9200\/modsec*\/_delete_by_query?pretty\" -H 'Content-Type: application\/json' -d '{\n  \"query\": {\n    \"bool\": {\n      \"must\": [\n        {\n          \"match\": {\n            \"message\": \":notice\"\n          }\n        }\n      ]  \n    }\n  }\n}'\n<\/code><\/pre>\n\n\n\n<p>It is also possible to further refine the query, for example, to delete specific event data record from specific host.<\/p>\n\n\n\n<p>Consider the query below;<\/p>\n\n\n\n<pre class=\"scroll-box\"><code>\nPOST \/test*\/_delete_by_query\n{\n  \"query\": {\n    \"bool\": {\n      \"must\": [\n        {\n          \"match\": {\n            \"hostname\": \"sales.kifarunix.com\"\n          }\n        },\n        {\n          \"match\": {\n            \"source_ip\": \"xx.xx.xx.xx\"\n          }\n        }\n      ]\n    }\n  }\n}\n<\/code><\/pre>\n\n\n\n<p>When executed, it will search for events sent by the host <strong>sales.kifarunix.com<\/strong> (field is <strong>hostname<\/strong>)and contain<strong> source_ip<\/strong>: xx.xx.xx.xx and delete them;<\/p>\n\n\n\n<p>You can run the above command on Kibana dev tools console or from command line.<\/p>\n\n\n\n<p>You can also delete documents with a missing field.<\/p>\n\n\n\n<p>For example, to delete records from the index <strong>logstash*<\/strong> sent by the host sales.kifarunix-demo.com and doesnt have a field called <strong>status_code<\/strong>, you could use such a delete query;<\/p>\n\n\n\n<p>The search query below give total number of search events.<\/p>\n\n\n\n<pre class=\"scroll-box\"><code>\nPOST \/logstash*\/_search\n{\n  \"query\": {\n    \"bool\" : {\n      \"must\" : {\n        \"match\" : { \"host.name\" : \"sales.kifarunix-demo.com\" }\n      },\n      \"must_not\" : {\n        \"exists\" : { \"field\" : \"status_code\" }\n      }\n    }\n  }\n}\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1916\" height=\"586\" src=\"https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/search-records.png\" alt=\"\" class=\"wp-image-14541\" title=\"\" srcset=\"https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/search-records.png?v=1666426458 1916w, https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/search-records-768x235.png?v=1666426458 768w, https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/search-records-1536x470.png?v=1666426458 1536w\" sizes=\"(max-width: 1916px) 100vw, 1916px\" \/><\/figure>\n\n\n\n<p>To delete these events;<\/p>\n\n\n\n<pre class=\"scroll-box\"><code>\nPOST \/logstash*\/_delete_by_query\n{\n  \"query\": {\n    \"bool\" : {\n      \"must\" : {\n        \"match\" : { \"host.name\" : \"sales.kifarunix-demo.com\" }\n      },\n      \"must_not\" : {\n        \"exists\" : { \"field\" : \"status_code\" }\n      }\n    }\n  }\n}\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1907\" height=\"564\" src=\"https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/delete_events_query.png\" alt=\"\" class=\"wp-image-14542\" title=\"\" srcset=\"https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/delete_events_query.png?v=1666426762 1907w, https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/delete_events_query-768x227.png?v=1666426762 768w, https:\/\/kifarunix.com\/wp-content\/uploads\/2022\/10\/delete_events_query-1536x454.png?v=1666426762 1536w\" sizes=\"(max-width: 1907px) 100vw, 1907px\" \/><\/figure>\n\n\n\n<p>You can play around with query. Be careful not to delete your important records!!<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Further Readings;<\/h3>\n\n\n\n<p><a href=\"https:\/\/www.elastic.co\/guide\/en\/elasticsearch\/reference\/current\/docs-delete-by-query.html\" target=\"_blank\" rel=\"noreferrer noopener\">Delete by query API<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.elastic.co\/guide\/en\/elasticsearch\/reference\/current\/query-dsl-bool-query.html#query-dsl-bool-query\" target=\"_blank\" rel=\"noreferrer noopener\">Elasticsearch Boolean Queries<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Other Tutorials<\/h3>\n\n\n\n<p><a href=\"https:\/\/kifarunix.com\/setup-multinode-elasticsearch-8-x-cluster\/\" target=\"_blank\" rel=\"noreferrer noopener\">Setup Multinode Elasticsearch 8.x Cluster<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/kifarunix.com\/enable-https-connection-between-elasticsearch-nodes\/\" target=\"_blank\" rel=\"noreferrer noopener\">Enable HTTPS Connection Between Elasticsearch Nodes<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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<\/p>\n","protected":false},"author":1,"featured_media":14546,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_lock_modified_date":false,"footnotes":""},"categories":[121,910,72],"tags":[5981,5978,5977,5979,5980],"class_list":["post-14538","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-howtos","category-elastic-stack","category-monitoring","tag-delete-documents-with-a-missing-field-in-elasticsearch","tag-delete-records-from-elasticsearch-indexes","tag-delete-specific-records-from-elasticsearch-index","tag-elasticsearch-curl-delete-records","tag-search-and-delete-elasticsearch-records","generate-columns","tablet-grid-50","mobile-grid-100","grid-parent","grid-50","resize-featured-image"],"_links":{"self":[{"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/posts\/14538"}],"collection":[{"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/comments?post=14538"}],"version-history":[{"count":4,"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/posts\/14538\/revisions"}],"predecessor-version":[{"id":20634,"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/posts\/14538\/revisions\/20634"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/media\/14546"}],"wp:attachment":[{"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/media?parent=14538"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/categories?post=14538"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kifarunix.com\/wp-json\/wp\/v2\/tags?post=14538"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}