Can't get delete by query to actually delete

Elastic Stack Elasticsearch
1

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" :
}

2

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

3

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.

4

That's not the question I asked.

What is the mapping?

5

Sorry.

EventID - number
DestAddress - text
Application - text

6

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.

7

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"
}
}

8

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.
9

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.

10

Please format your code, logs or configuration files using </> icon as explained in this guide 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
image764×92 16.4 KB

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

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.

11

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.

12

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

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

Gives:

{
  "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:

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

Gives

{
  "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.

13

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?

14

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

15

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.

16

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

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

17

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.

18

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.

19

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

20

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.