How to drop some fields with mysql module using filebeat

Elastic Stack Beats
1

i want to drop some fields in filebeat.yml when load mysql module, just like mysql.slowlog.id, but it doesn't affect when i use

processors:
- include_fields:
    fields: ["mysql.slow"]
- drop_fields:
    fields: ["mysql.slow.id"]

or

processors:
- include_fields:
    fields: ["mysql"]
- drop_fields:
    fields: ["mysql.slow.id"]

or

processors:
- drop_fields:
    fields: ["mysql.slowlog.id"]

how could i make it work?

my full configuration like below :

filebeat.modules:
- module: mysql
  error:
    enabled: true
    var.paths: ["/data/error.log*"]
  slowlog:
    enabled: true
    var.paths: ["/data/slow.log*"]
  prospector:
setup.template.enabled: false
fields_under_root: true
fields:
  ip: 192.168.1.233
processors:
- include_fields:
    fields: ["mysql.slow"]
- drop_fields:
    fields: ["mysql.slow.id"]
processors:
- drop_fields:
    fields: ["offset", "prospector.type", "fileset.module", "fileset.name", "beat.version", "beat.name"]
logging.to_files: true
logging.files:
  path: /tmp/filebeat
output.elasticsearch:
  hosts: ["192.168.1.11:9200"]
  index: "filebeat-%{[beat.version]}-%{+yyyy.MM.dd}"
2

I think the problem is that you defined processors twice. You should have only 1 entry in your config file.

3 
filebeat.modules:
- module: mysql
  error:
    enabled: true
    var.paths: ["/data/error.log*"]
  slowlog:
    enabled: true
    var.paths: ["/data/slow.log*"]
  prospector:
setup.template.enabled: false
fields_under_root: true
fields:
  ip: 192.168.1.233

processors:
- drop_fields:
    fields: ["offset", "prospector.type", "fileset.module", "fileset.name", "beat.version", "beat.name", "mysql.slow.id"]
logging.to_files: true
logging.files:
  path: /tmp/filebeat
output.elasticsearch:
  hosts: ["192.168.1.11:9200"]
  index: "filebeat-%{[beat.version]}-%{+yyyy.MM.dd}"

i change config file like this , but it doesn't work too. should i make it like this ?

processors:
- include_fields:
    fields: ["mysql.slow"]
- drop_fields:
    fields: ["offset", "prospector.type", "fileset.module", "fileset.name", "beat.version", "beat.name","mysql.slow.id"]
4

Does it drop no fields or it just doesn't drop the mysql.slow.id field?

Could you run filebeat with debug log enabled and share the output. I'm hoping to see in there some more details on what is happening.

5

my config :

setup.template.enabled: false
template.overwrite: false
filebeat.config.prospectors:
  enabled: true
  path: /home/elk/filebeat-5.5.0-linux-x86_64/conf/t.yml
  reload.enabled: true
  reload.period: 5s
filebeat.modules:
- module: mysql
  slowlog:
enabled: true
var.paths: ["/data/slow.log"]
prospector:
  fields:
    my_type: slow
processors:
- drop_fields:
fields: ["offset", "type", "mysql.slow.id"]

fields_under_root: true
fields:
  ip: 192.168.1.6
logging.to_files: true
logging.files:
  path: /tmp/filebeat
output.elasticsearch:
  hosts: ["121.31.2.108:9200"]  
  index: "filebeat-5.5.0-%{[fields.my_type]}-%{+yyyy.MM.dd}"

some debug log below

2018/05/18 06:42:13.510882 log_file.go:84: DBG  End of file reached: /data/slow.log; Backoff now.
2018/05/18 06:42:16.502401 spooler.go:89: DBG  Flushing spooler because of timeout. Events flushed: 1
2018/05/18 06:42:16.502884 processor.go:67: DBG  fail to apply processor drop_fields=offset, prospector.type, fileset.module, fileset.name, beat.version, beat.name, input_type, mysql.slow.id: key=prospector: key not found, key=fileset: key not found, key=fileset: key not found, key=mysql: key not found
2018/05/18 06:42:16.503067 client.go:214: DBG  Publish: {
  "@timestamp": "2018-05-18T06:42:06.509Z",
  "beat": {
    "hostname": "wo-sms"
  },
  "fields": {
    "my_type": "slow"
  },
  "ip": "192.168.1.6",
  "message": "# Time: 2018-05-16T07:07:51.824687Z\n# User@Host: root[root] @ localhost []  Id:    15\n# Query_time: 17.312086  Lock_time: 0.000174 Rows_sent: 0  Rows_examined: 1572864\nSET timestamp=1526454471;\ninsert into t (key1,key2) select key1,key2 from t;",
  "source": "/data/slow.log",
  "type": "log"
}

i found the debug log show that there has not key mysql. i confuse if the processor running before the mysql module , so when the processor running , only can see an unformat log inclusive in the message ??

one log formatted into the es i get like this

{
"_index": "filebeat-5.5.0-slow-2018.05.18",
"_type": "doc",
"_id": "AWNyE3O2pBUmG6P5NHn2",
"_score": 1,
"_source": {
"@timestamp": "2018-05-14T14:32:43.000Z",
"ip": "192.168.1.6",
"beat": {
"hostname": "wo-sms",
"name": "wo-sms",
"version": "5.5.0"
},
"input_type": "log",
"mysql": {
"slowlog": {
"lock_time": {
"sec": "0.000188"
},
"rows_sent": "1",
"rows_examined": "1000",
"query": "select sleep(0.2), count(*) from film;\n# Time: 2018-05-14T14:46:14.967003Z",
"host": "localhost",
"id": "14",
"user": "root",
"query_time": {
"sec": "0.200897"
},
"timestamp": "1526308363"
}
},

6

i review the doc again ,and find the answer why i can't drop the mysql.slow.id key with the processor.
because this key is actually generate on the ES ingest node before it stores into ES. on the filebeat node , it only record in a line which names messages. so the processor can't find the key .
sorry about waste your time . this question can close .
thank you.

7

Glad you found the issue. Should also have thought of that.

8

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.