Changing field properties

Question

I am using packetbeat to monitor mysql port on 3306 and it is working very well. I can easily search for any word on discovery tab. For e.g.

method:SET

This works as expected. But If I change it to

query:SET

then it does not return the documents with the word "SET" in query field. Is the query field indexed differently? How do I make "query" field searchable?

Update:

Is this because of parameter "ignore_above" that is used for all string fields? I checked the mapping using this API...

GET /packetbeat-2018.02.01/_mapping/mysql/

How do I remove this restriction and make all future beats to index query field?

Update 2:

If I mention the entire string in the search based on "query" field, it works as expected...

query:"SELECT name, type, comment FROM mysql.proc WHERE name like 'residentDetails_get' and db <=> 'portal' ORDER BY name, type"

This returns all 688 records in the last 15 minutes. When I search the following, I expect to get more...

query:"SELECT"

But I do not get a single record. I guess this is because the way document is indexed. I will prefer to get back equivalent of SQL : query like '%SELECT%'

https://github.com/elastic/beats-dashboards/blob/master/dashboards/index-pattern/packetbeat.json — shantanuo, Feb 10 '18 at 00:37
The output of the mapping API is here.... https://gist.github.com/shantanuo/e80a6eb9f316e811d341a7d94efd9110 — shantanuo, Feb 10 '18 at 02:05
Thanks, now can you show a document that should match `query:SET`? — Val, Feb 10 '18 at 03:25
That's the correct behavior, given the query and the mapping of the field. And it's not about the `1024` limit. You can either omit `query:` part so that Elasticsearch will use the `_all` field (which will be removed in the near future) but here it depends on the version of the Stack you use. Or, better and more correct approach, is to configure the `query` field differently in the packetbeat template (so that next indices will use the new mapping) to be like this: `"query": {"type": "text","fields": {"raw": {"type": "keyword","ignore_above": 1024}}}`. — Andrei Stefan, Feb 10 '18 at 09:52
The main idea is that ES is not splitting the values in the `query` field (since it's `keyword`) and you need a way to do this. You could use wildcards, but ES doesn't like them (especially the leading wildcards) and you could have performance issue when running such a query. The "correct" approach from ES point of view is the one I mentioned in my previous comment: make the field analyzed, keep a `raw` version of it (for sorting and aggregations) and the simple version of it for searches. — Andrei Stefan, Feb 10 '18 at 09:54
@AndreiStefan yes, you are right. Can you post the comment as answer so that I can accept it? — shantanuo, Feb 10 '18 at 17:03

score 1 · Answer 1 · answered Feb 11 '18 at 06:05

That's the correct behavior, given the query and the mapping of the field. And it's not about the 1024 limit. You can either omit query: part so that Elasticsearch will use the _all field (which will be removed in the near future) but here it depends on the version of the Stack you use.

Or, better and more correct approach, is to configure the query field differently in the packetbeat template (so that next indices will use the new mapping) to be like this:

    "query": {
      "type": "text",
      "fields": {
        "raw": {
          "type": "keyword",
          "ignore_above": 1024
        }
      }
    }

The main idea is that ES is not splitting the values in the query field (since it's keyword) and you need a way to do this. You could use wildcards, but ES doesn't like them (especially the leading wildcards) and you could have performance issue when running such a query. The "correct" approach from ES point of view is the one I already mentioned: make the field analyzed, keep a raw version of it (for sorting and aggregations) and the simple version of it for searches.

score 0 · Accepted Answer · answered Feb 10 '18 at 17:01

The query field of packetbeat is declared as "keyword". Therefore you can search the entire query only. For e.g.

query: "select * from mytable"

But what if we need to search for query: "mytable" ? You need to make the query field searchable by modifying fields.yml file. Add the type:text parameter to query field of MySQL section of fields.yml file found in /etc/packetbeat

The relevant section of the file will look like this...

- name: query
  type: text
  description: >
    The query in a human readable format. For HTTP, it will typically be
    something like `GET /users/_search?name=test`. For MySQL, it is
    something like `SELECT id from users where name=test`.

Changing field properties

2 Answers2