# Can't get delete by query to actually delete

**URL:** <https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323>\
**Category:** Elasticsearch\
**Created:** [October 25, 2019, 8:09pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323 "2019-10-25T20:09:25Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [October 25, 2019, 8:09pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/1 "2019-10-25T20:09:25Z")

</div>

I can't seem to get a delete query (ANY delete query) to actually delete anything. I've even tried the basic ones in the documentation (edited for my test index) and they don't work either. What am I overlooking?

I'm running the query below:

POST incode\_index/\_delete\_by\_query  
{  
"query": {  
"bool": {  
"must": [  
{ "term" : { "EventID" : "5156"}},  
{ "term" : { "DestAddress" : "172.xx.xx.xx"}},  
{ "term" : { "Application" : "\device\harddiskvolume3\program files (x86)\nxlog\nxlog.exe"}}  
]  
}  
}  
}

I'm getting the following results:

{  
"took" : 4,  
"timed\_out" : false,  
"total" : 0,  
"deleted" : 0,  
"batches" : 0,  
"version\_conflicts" : 0,  
"noops" : 0,  
"retries" : {  
"bulk" : 0,  
"search" : 0  
},  
"throttled\_millis" : 0,  
"requests\_per\_second" : -1.0,  
"throttled\_until\_millis" : 0,  
"failures" :   
}

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 26, 2019, 12:23pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/2 "2019-10-26T12:23:17Z")

</div>

It's probably because of the mapping you are using. What is it for the field you are searching for?

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [October 29, 2019, 6:50pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/3 "2019-10-29T18:50:27Z")

</div>

The search needs to be for all of those terms. The logs searched are Windows event logs. The field names are taken from a Kibana search. I didn't specify in the OP but I'm running the delete query in Kibana also.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 29, 2019, 10:41pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/4 "2019-10-29T22:41:07Z")

</div>

That's not the question I asked.

What is the mapping?

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [October 30, 2019, 12:54pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/5 "2019-10-30T12:54:48Z")

</div>

Sorry.

EventID - number  
DestAddress - text  
Application - text

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 4, 2019, 11:56am UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/6 "2019-11-04T11:56:30Z")

</div>

Ok. Without the complete mapping, I can only do some guesses. So you seem to be using the default analyzer. Which explains why a `term` query with `\device\harddiskvolume3\program files (x86)\nxlog\nxlog.exe` or `172.xx.xx.xx` does not match.

Most likely `172.xx.xx.xx` has been indexed as `172`, `xx`. So the terms query can not find `172.xx.xx.xx` in the inverted index.

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [November 4, 2019, 4:24pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/7 "2019-11-04T16:24:15Z")

</div>

This re-index below works fine, which is why I'm confused the delete query isn't working.

POST \_reindex  
{  
"size": 1000,  
"source": {  
"index": "xxx\_xxx\_1280",  
"type": "message",  
"query": {  
"bool": {  
"must": [  
{ "term" : { "EventID" : "5156"}},  
{ "term" : { "DestAddress" : "172.20.10.250"}},  
{ "term" : { "Application" : "\device\harddiskvolume3\program files (x86)\nxlog\nxlog.exe"}}  
]  
}  
}  
},  
"dest": {  
"index": "test\_index"  
}  
}

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [November 4, 2019, 4:31pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/8 "2019-11-04T16:31:55Z")

</div>

This is one of the documents of one of these logs.

| t Application | |
| --- | --- |
| | \device\harddiskvolume3\program files (x86)\nxlog\nxlog.exe |
| t Category | |
| | Filtering Platform Connection |
| t Channel | |
| | Security |
| t DestAddress | |
| | 172.20.10.250 |
| t DestPort | |
| | 12201 |
| t Direction | |
| | %%14593 |
| # EventID | |
| | 5,156 |
| t EventReceivedTime | |
| | 2019-10-21 06:35:44 |
| t EventType | |
| | AUDIT\_SUCCESS |
| t FilterRTID | |
| | 65774 |
| # Keywords | |
| | -9,214,364,837,600,034,816 |
| t LayerName | |
| | %%14611 |
| t LayerRTID | |
| | 48 |
| t Opcode | |
| | Info |
| # OpcodeValue | |
| | 0 |
| # ProcessID | |
| | 4 |
| t Protocol | |
| | 17 |
| t ProviderGuid | |
| | {54849625-5478-4994-A5BA-3E3B0328C30D} |
| # RecordNumber | |
| | 28,462,998,473 |
| t RemoteMachineID | |
| | S-1-0-0 |
| t RemoteUserID | |
| | S-1-0-0 |
| t Severity | |
| | INFO |
| # SeverityValue | |
| | 2 |
| t SourceAddress | |
| | 172.20.10.8 |
| t SourceModuleName | |
| | in |
| t SourceModuleType | |
| | im\_msvistalog |
| t SourceName | |
| | Microsoft-Windows-Security-Auditing |
| t SourcePort | |
| | 62563 |
| # Task | |
| | 12,810 |
| # ThreadID | |
| | 132 |
| # Version | |
| | 1 |
| t \_id | |
| | 892d3c82-f3ee-11e9-8304-005056857e23 |
| t \_index | |
| | xxx\_xxx\_1280 |
| # \_score | |
| | - |
| t \_type | |
| | message |
| t full\_message | |
| | The Windows Filtering Platform has permitted a connection. |

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [November 4, 2019, 4:37pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/9 "2019-11-04T16:37:54Z")

</div>

The issue appears to be related to the line with "\device\harddiskvolume3\program files (x86)\nxlog\nxlog.exe" in it. If I remove that line, the other two terms will work to delete documents. I just don't see what's wrong with the third line, especially since that exact line works fine in the re-index query.

NOTE: The line listed above actually has double slashes but the extra are stripped out when the post is actually posted. Even now I'm editing, the line correctly shows both slashes. The same for that line in the re-index.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 5, 2019, 12:43pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/10 "2019-11-05T12:43:31Z")

</div>

> [@CityofRome](#):
>
> The line listed above actually has double slashes but the extra are stripped out when the post is actually posted.

Please format your code, logs or configuration files using `</>` icon as explained in [this guide](https://discuss.elastic.co/t/about-the-elasticsearch-category/21) and not the citation button. It will make your post more readable.

Or use markdown style like:

````
```
CODE
```

````

This is the icon to use if you are not using markdown format:

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/e/7e6e239431ec2d71cbf1beef741f2e93e7cc762c.jpg)

There's a live preview panel for exactly this reasons.

> [@CityofRome](#):
>
> If I remove that line, the other two terms will work to delete documents.

With the default analyzer, I'd expect this behavior. Either change the analyzer to be a `keyword` analyzer or use the `keyword` data type instead of `text`.

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [November 6, 2019, 2:49pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/11 "2019-11-06T14:49:16Z")

</div>

I'll have to research how to change the analyzer. But, I'm confused as how I can apply a filter in the Discover tab in Kibana for that exact query and get those exact documents without changing anything, yet I have to change something to get a manual query to work. This query works in the Discovery tab of Kibana to get the exact documents I need to return in the delete query but it doesn't work when I try to use it in the delete query. The 'Application' field returns nothing but the other two lines will delete documents.

```
  "query": {
    "bool": {
      "must": [
        {
          "match_phrase": {
            "source": {
              "query": "server01"
            }
          }
        },
        {
          "match_phrase": {
            "EventID": {
              "query": 5156
            }
          }
        },
        {
          "match_phrase": {
            "Application": {
              "query": "\\device\\harddiskvolume3\\program files (x86)\\nxlog\\nxlog.exe"
            }
          }
        },
        {
          "match_phrase": {
            "_index": {
              "query": "test_log_1"
            }
          }
        }

```

Do the delete queries parse differently than all of the other queries? I don't understand why I would need to change something for that one query. Thank you for your help on this. I really appreciate it.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 6, 2019, 3:59pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/12 "2019-11-06T15:59:42Z")

</div>

This is how your text is indexed with the default text analyzer:

```auto
POST _analyze
{
  "text": ["\\device\\harddiskvolume3\\program files (x86)\\nxlog\\nxlog.exe"]
}

```

Gives:

```auto
{
  "tokens" : [
    {
      "token" : "device",
      "start_offset" : 1,
      "end_offset" : 7,
      "type" : "<ALPHANUM>",
      "position" : 0
    },
    {
      "token" : "harddiskvolume3",
      "start_offset" : 8,
      "end_offset" : 23,
      "type" : "<ALPHANUM>",
      "position" : 1
    },
    {
      "token" : "program",
      "start_offset" : 24,
      "end_offset" : 31,
      "type" : "<ALPHANUM>",
      "position" : 2
    },
    {
      "token" : "files",
      "start_offset" : 32,
      "end_offset" : 37,
      "type" : "<ALPHANUM>",
      "position" : 3
    },
    {
      "token" : "x86",
      "start_offset" : 39,
      "end_offset" : 42,
      "type" : "<ALPHANUM>",
      "position" : 4
    },
    {
      "token" : "nxlog",
      "start_offset" : 44,
      "end_offset" : 49,
      "type" : "<ALPHANUM>",
      "position" : 5
    },
    {
      "token" : "nxlog.exe",
      "start_offset" : 50,
      "end_offset" : 59,
      "type" : "<ALPHANUM>",
      "position" : 6
    }
  ]
}

```

If you are using a `keyword` analyzer or a `keyword` datatype, here is what will happen:

```auto
POST _analyze
{
  "analyzer": "keyword", 
  "text": ["\\device\\harddiskvolume3\\program files (x86)\\nxlog\\nxlog.exe"]
}

```

Gives

```auto
{
  "tokens" : [
    {
      "token" : """\device\harddiskvolume3\program files (x86)\nxlog\nxlog.exe""",
      "start_offset" : 0,
      "end_offset" : 59,
      "type" : "word",
      "position" : 0
    }
  ]
}

```

So basically, if you are not using the "right" type or analyzer, it can not work.

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [November 6, 2019, 4:13pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/13 "2019-11-06T16:13:45Z")

</div>

Thank you for your response. I now understand what is happening, even though I still don't understand why the Discover query and the re-index query don't have the same issue. So, how do I use a different analyzer?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 6, 2019, 5:15pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/14 "2019-11-06T17:15:57Z")

</div>

> [@CityofRome](#):
>
> So, how do I use a different analyzer?

You create a new index with the proper mapping and reindex your data.

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [November 6, 2019, 6:41pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/15 "2019-11-06T18:41:06Z")

</div>

Wow. I did NOT expect that. I really like Elasticsearch. I created a cluster for our logging using version 5.0 and now I'm on version 6.6. But, it's mind-blowing there's not another solution to delete documents using a file path (or, something similar) as a query term other than re-indexing to change the analyzer. There was an issue on one server that caused it to begin a log loop (logging the fact it was logging). That caused it to puke about 220,000,000 logs per day on several occasions, instead of the 500-750 thousand per day it would normally generate. So, I've got a billion bogus logs I would like to delete.

I thought it would be relatively easy to delete the bogus logs and free up some space on my cluster. But, doing so isn't worth re-indexing 100 indexes and it's likely not an option anyway because many of the logs are sent to a Graylog server, which isn't going to appreciate indexes being replaced without it knowing about it. Re-indexing would likely cause searches in Graylog to stop working and may even stop Graylog from working at all. I'll just leave the bogus logs alone. It's simply not worth re-indexing 2-3 billion logs just to get rid of the bogus logs, even if it frees up space in the clusters.

I REALLY appreciate the time you've spent responding to me. Since you're a developer for Elasticsearch, maybe you could pass along this situation and see if there's interest in making easier resolutions to situations like I've got. I still don't understand how some searches and re-indexing queries work fine with that search term but the delete query requires such a drastic method to resolve. In order to show my department what Elasticsearch was capable of, I ran a search of 700 million documents that returned 29 results in 17ms and they were really blown away with the speed of Elasticsearch running on old hardware (three old ESX hosts). But, I feel confident they would also be blown away that a simple delete would require such a drastic resolution.

Thank you again for your time. If you think of another solution, I would be interested in hearing about it. Have a good one.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 6, 2019, 8:03pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/16 "2019-11-06T20:03:09Z")

</div>

> [@CityofRome](#):
>
> That caused it to puke about 220,000,000 logs per day on several occasions, instead of the 500-750 thousand per day it would normally generate. So, I've got a billion bogus logs I would like to delete.

If you have so many logs to remove, you should may be consider reindexing the logs you want to keep instead.

> [@CityofRome](#):
>
> But, I feel confident they would also be blown away that a simple delete would require such a drastic resolution.

You always need to think about the use case when you do things with elasticsearch. Mapping is really really important.

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [November 7, 2019, 4:01pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/17 "2019-11-07T16:01:26Z")

</div>

Unfortunately, I'm not in control of the mapping - the Graylog application is. Graylog expects to have control over the indexes it uses so changing anything on them would likely cause it to stop working. I know manually deleting an index causes Graylog go be...unhappy. I can't remember what happens, but it's not good. Graylog keeps it's own list of what indexes it uses and some other information about them. Deleting documents shouldn't cause any issues but re-indexing likely will.

I get the part about how ES creates the tokens. So, how does Kibana 'Discover' tab and a re-index query not have a problem with the same search term? I still can't wrap my feeble brain around that one.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 7, 2019, 4:23pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/18 "2019-11-07T16:23:46Z")

</div>

> [@CityofRome](#):
>
> So, how does Kibana 'Discover' tab and a re-index query not have a problem with the same search term?

I don't know. If you have a way to reproduce the problem with a sample script that creates a test index, index a document, run the delete by query and run the reindex, that could help.

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [November 7, 2019, 7:51pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/19 "2019-11-07T19:51:14Z")

</div>

I have the re-index query in one of my comments from 3 days ago.

---

<div class="post-metadata">

**Author:** ![CityofRome](https://avatars.discourse-cdn.com/v4/letter/c/a9adbd/32.png) [@CityofRome](https://discuss.elastic.co/u/CityofRome)\
**Post date:** [November 7, 2019, 8:27pm UTC](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323/20 "2019-11-07T20:27:32Z")

</div>

FINALLY! I knew it had to be something simple and I finally got it sorted out.

```
POST /test_index/_delete_by_query

```

{  
"query": {  
"bool": {  
"must": [  
{"term": {"EventID": 5156}},  
{"term" : { "DestAddress" : "172.xx.xx.xx"}},  
{"match\_phrase": {"Application": {"query": "\device\harddiskvolume3\program files (x86)\nxlog\nxlog.exe"}}}  
]  
}  
}  
}

All I needed to do was to match a phrase. I also found Graylog sets old indices to read-only. So, I had to do the following to disable the write blocks (enable write mode):

```
PUT /graylog_1342/_settings

```

{  
"index.blocks.write": "false"  
}

I also found that I only need to have Graylog recalculate the index ranges to make Graylog happy with the deleted documents. I began the query above and it's deleting documents now. At least now I know how. I appreciate your time.

[Next page](https://discuss.elastic.co/t/cant-get-delete-by-query-to-actually-delete/205323.md?page=2)