Filebeat Can't parse MSSQL Logs

Elastic Stack Beats
1

I have installed filebeat 7.15.1 on windows server with mssql logs module. When I run filebeat in foreground with .\filebeat.exe -c .\filebeat.yml -e -d "*" , it can not parse the logs.

Filebeat Config:

filebeat.inputs:

- type: log
  enabled: false

  # Paths that should be crawled and fetched. Glob based paths.
  paths:
    - /var/log/*.log
    #- c:\programdata\elasticsearch\logs\*


filebeat.config.modules:
  path: ${path.config}/modules.d/*.yml
  reload.enabled: false

output.logstash:
  # The Logstash hosts
  hosts: ["logstash.internal:5044"]
  ssl.enabled: true

processors:
  - add_host_metadata: ~
  - add_cloud_metadata: ~
  - add_docker_metadata: ~
  - add_kubernetes_metadata: ~

MSSQL Module:


# Module: mssql
# Docs: https://www.elastic.co/guide/en/beats/filebeat/7.x/filebeat-module-mssql.html

- module: mssql
  # Fileset for native deployment
  log:
    enabled: true
    encoding: utf-16le-bom
    # Set custom paths for the log files. If left empty,
    # Filebeat will choose the paths depending on your OS.
    var.paths: ['C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQL\MSSQL\Log\ERRORLOG']
    fields:
        log_type: mssql

Filebeat Logs:

2022-10-17T07:44:43.230-0400    DEBUG   [input] log/input.go:279        input states cleaned up. Before: 1, After: 1, Pending: 0        {"input_id": "f16badce-8dff-40b8-99dc-8f0d5879c68a"}
2022-10-17T07:44:44.215-0400    DEBUG   [input.harvester]       log/log.go:111  End of file reached: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQL\MSSQL\Log\ERRORLOG; Backoff now.       {"input_id": "f16badce-8dff-40b8-99dc-8f0d5879c68a", "source": "C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQL\\MSSQL\\Log\\ERRORLOG", "state_id": "native::655360-86423-2886361991", "finished": false, "os_id": "655360-86423-2886361991", "old_source": "C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQL\\MSSQL\\Log\\ERRORLOG", "old_finished": true, "old_os_id": "655360-86423-2886361991", "harvester_id": "0171af12-a085-4bbd-a3c4-7715c8930e20"}
2022-10-17T07:44:46.220-0400    DEBUG   [reader_multiline]      multiline/pattern.go:170        Multiline event flushed because timeout reached.
2022-10-17T07:44:46.220-0400    DEBUG   [processors]    processing/processors.go:203    Publish event: {
  "@timestamp": "2022-10-17T11:44:41.206Z",
  "@metadata": {
    "beat": "filebeat",
    "type": "_doc",
    "version": "7.15.1",
    "pipeline": "filebeat-7.15.1-mssql-log-pipeline"
  },
  "message": "\u00002\u00000\u00002\u00002\u0000-\u00001\u00000\u0000-\u00001\u00007\u0000 \u00000\u00007\u0000:\u00004\u00004\u0000:\u00002\u00000\u0000.\u00008\u00005\u0000 \u0000L\u0000o\u0000g\u0000o\u0000n\u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000E\u0000r\u0000r\u0000o\u0000r\u0000:\u0000 \u00001\u00008\u00004\u00005\u00006\u0000,\u0000 \u0000S\u0000e\u0000v\u0000e\u0000r\u0000i\u0000t\u0000y\u0000:\u0000 \u00001\u00004\u0000,\u0000 \u0000S\u0000t\u0000a\u0000t\u0000e\u0000:\u0000 \u00005\u0000.\u0000\r\u0000\n\u00002\u00000\u00002\u00002\u0000-\u00001\u00000\u0000-\u00001\u00007\u0000 \u00000\u00007\u0000:\u00004\u00004\u0000:\u00002\u00000\u0000.\u00008\u00005\u0000 \u0000L\u0000o\u0000g\u0000o\u0000n\u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000L\u0000o\u0000g\u0000i\u0000n\u0000 \u0000f\u0000a\u0000i\u0000l\u0000e\u0000d\u0000 \u0000f\u0000o\u0000r\u0000 \u0000u\u0000s\u0000e\u0000r\u0000 \u0000'\u0000e\u0000e\u0000r\u0000e\u0000r\u0000e\u0000e\u0000r\u0000'\u0000.\u0000 \u0000R\u0000e\u0000a\u0000s\u0000o\u0000n\u0000:\u0000 \u0000C\u0000o\u0000u\u0000l\u0000d\u0000 \u0000n\u0000o\u0000t\u0000 \u0000f\u0000i\u0000n\u0000d\u0000 \u0000a\u0000 \u0000l\u0000o\u0000g\u0000i\u0000n\u0000 \u0000m\u0000a\u0000t\u0000c\u0000h\u0000i\u0000n\u0000g\u0000 \u0000t\u0000h\u0000e\u0000 \u0000n\u0000a\u0000m\u0000e\u0000 \u0000p\u0000r\u0000o\u0000v\u0000i\u0000d\u0000e\u0000d\u0000.\u0000 \u0000[\u0000C\u0000L\u0000I\u0000E\u0000N\u0000T\u0000:\u0000 \u0000<\u0000l\u0000o\u0000c\u0000a\u0000l\u0000 \u0000m\u0000a\u0000c\u0000h\u0000i\u0000n\u0000e\u0000>\u0000]\u0000\r\u0000\n\u00002\u00000\u00002\u00002\u0000-\u00001\u00000\u0000-\u00001\u00007\u0000 \u00000\u00007\u0000:\u00004\u00004\u0000:\u00002\u00005\u0000.\u00009\u00003\u0000 \u0000L\u0000o\u0000g\u0000o\u0000n\u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000E\u0000r\u0000r\u0000o\u0000r\u0000:\u0000 \u00001\u00008\u00004\u00005\u00006\u0000,\u0000 \u0000S\u0000e\u0000v\u0000e\u0000r\u0000i\u0000t\u0000y\u0000:\u0000 \u00001\u00004\u0000,\u0000 \u0000S\u0000t\u0000a\u0000t\u0000e\u0000:\u0000 \u00005\u0000.\u0000\r\u0000\n\u00002\u00000\u00002\u00002\u0000-\u00001\u00000\u0000-\u00001\u00007\u0000 \u00000\u00007\u0000:\u00004\u00004\u0000:\u00002\u00005\u0000.\u00009\u00003\u0000 \u0000L\u0000o\u0000g\u0000o\u0000n\u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000L\u0000o\u0000g\u0000i\u0000n\u0000 \u0000f\u0000a\u0000i\u0000l\u0000e\u0000d\u0000 \u0000f\u0000o\u0000r\u0000 \u0000u\u0000s\u0000e\u0000r\u0000 \u0000'\u0000e\u0000e\u0000r\u0000e\u0000r\u0000e\u0000e\u0000r\u0000'\u0000.\u0000 \u0000R\u0000e\u0000a\u0000s\u0000o\u0000n\u0000:\u0000 \u0000C\u0000o\u0000u\u0000l\u0000d\u0000 \u0000n\u0000o\u0000t\u0000 \u0000f\u0000i\u0000n\u0000d\u0000 \u0000a\u0000 \u0000l\u0000o\u0000g\u0000i\u0000n\u0000 \u0000m\u0000a\u0000t\u0000c\u0000h\u0000i\u0000n\u0000g\u0000 \u0000t\u0000h\u0000e\u0000 \u0000n\u0000a\u0000m\u0000e\u0000 \u0000p\u0000r\u0000o\u0000v\u0000i\u0000d\u0000e\u0000d\u0000.\u0000 \u0000[\u0000C\u0000L\u0000I\u0000E\u0000N\u0000T\u0000:\u0000 \u0000<\u0000l\u0000o\u0000c\u0000a\u0000l\u0000 \u0000m\u0000a\u0000c\u0000h\u0000i\u0000n\u0000e\u0000>\u0000]\u0000\r\u0000\n\u00002\u00000\u00002\u00002\u0000-\u00001\u00000\u0000-\u00001\u00007\u0000 \u00000\u00007\u0000:\u00004\u00004\u0000:\u00003\u00001\u0000.\u00000\u00003\u0000 \u0000L\u0000o\u0000g\u0000o\u0000n\u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000E\u0000r\u0000r\u0000o\u0000r\u0000:\u0000 \u00001\u00008\u00004\u00005\u00006\u0000,\u0000 \u0000S\u0000e\u0000v\u0000e\u0000r\u0000i\u0000t\u0000y\u0000:\u0000 \u00001\u00004\u0000,\u0000 \u0000S\u0000t\u0000a\u0000t\u0000e\u0000:\u0000 \u00005\u0000.\u0000\r\u0000\n\u00002\u00000\u00002\u00002\u0000-\u00001\u00000\u0000-\u00001\u00007\u0000 \u00000\u00007\u0000:\u00004\u00004\u0000:\u00003\u00001\u0000.\u00000\u00003\u0000 \u0000L\u0000o\u0000g\u0000o\u0000n\u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000 \u0000L\u0000o\u0000g\u0000i\u0000n\u0000 \u0000f\u0000a\u0000i\u0000l\u0000e\u0000d\u0000 \u0000f\u0000o\u0000r\u0000 \u0000u\u0000s\u0000e\u0000r\u0000 \u0000'\u0000e\u0000e\u0000r\u0000e\u0000r\u0000e\u0000e\u0000r\u0000'\u0000.\u0000 \u0000R\u0000e\u0000a\u0000s\u0000o\u0000n\u0000:\u0000 \u0000C\u0000o\u0000u\u0000l\u0000d\u0000 \u0000n\u0000o\u0000t\u0000 \u0000f\u0000i\u0000n\u0000d\u0000 \u0000a\u0000 \u0000l\u0000o\u0000g\u0000i\u0000n\u0000 \u0000m\u0000a\u0000t\u0000c\u0000h\u0000i\u0000n\u0000g\u0000 \u0000t\u0000h\u0000e\u0000 \u0000n\u0000a\u0000m\u0000e\u0000 \u0000p\u0000r\u0000o\u0000v\u0000i\u0000d\u0000e\u0000d\u0000.\u0000 \u0000[\u0000C\u0000L\u0000I\u0000E\u0000N\u0000T\u0000:\u0000 \u0000<\u0000l\u0000o\u0000c\u0000a\u0000l\u0000 \u0000m\u0000a\u0000c\u0000h\u0000i\u0000n\u0000e\u0000>\u0000]\u0000\r\u0000",
  "service": {
    "type": "mssql"
  },
  "event": {
    "dataset": "mssql.log",
    "timezone": "-04:00",
    "module": "mssql"
  },
  "fileset": {
    "name": "log"
  },
  "cloud": {
    "provider": "azure",
    "service": {
      "name": "Virtual Machines"
    },
    "region": "CanadaCentral",
    "account": {},
    "instance": {
      "id": "7c84bf9e-0717-4701-ab0f-028b3c1b51c0",
      "name": "TestFilebeat"
    },
    "machine": {
      "type": "Standard_D2s_v4"
    }
  },
  "log": {
    "file": {
      "path": "C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQL\\MSSQL\\Log\\ERRORLOG"
    },
    "flags": [
      "multiline"
    ],
    "offset": 22123
  },
  "input": {
    "type": "log"
  },
  "ecs": {
    "version": "1.11.0"
  },
  "host": {
    "mac": [
      "00:22:48:3d:d4:19"
    ],
    "hostname": "PP0921-Filebeat",
    "architecture": "x86_64",
    "os": {
      "name": "Windows Server 2019 Datacenter",
      "kernel": "10.0.17763.3287 (WinBuild.160101.0800)",
      "build": "17763.3287",
      "type": "windows",
      "platform": "windows",
      "version": "10.0",
      "family": "windows"
    },
    "id": "41ad3b64-f2a1-47f7-9456-348f78eaf10f",
    "name": "Test-Filebeat",
    "ip": [
      "fe80::4592:46a9:4c54:d4d3",
      "10.1.0.23"
    ]
  },
  "agent": {
    "hostname": "Test-Filebeat",
    "ephemeral_id": "12831480-d922-4169-bfaa-893379b99966",
    "id": "f2e7ea3a-f35d-4737-b7a7-d6dad489943c",
    "name": "PP0921-Filebeat",
    "type": "filebeat",
    "version": "7.15.1"
  }
}

Do I need to add any additional configs here ? I have tried with different file encoding and also tried to use logs from filebeat.yml and got the same result. Can someone pls suggests what needs to be done here ? TIA.

2

Set encoding UTF-16. Default is UTF-8.
....

- type: log
  enabled: true
  encoding: utf-16

On Logstash side:
charset => "UTF-16"

3

Thanks, I tried to use encoding: utf-16 in filebeat config, ran the process in foreground, and it was still unable to read the log. Originally, I had tried with encoding: utf-16le-bom, this is encoding of ERRORLOG file as per notepad++.

4

Yes, Notepad++ will show you encoding.
Go step by step.
Make sure that filebeat read correctly. Should see in stdout what FB sends to LS.
filebat.ext -c filebeat.yml -e

This settings was working for me. Encoding in Notepad++: UCS-2 LE BOM

- module: mssql
  # Fileset for native deployment
  log:
    enabled: true
    var.paths: ["C:/bla bla/ERRORLOG*"]
    encoding: UTF-16

Since you are using the mssql modul, why are you sending to LS? Send directly to ES. It's simple format: date, spid, msg.

Anyway, if you still need LS...
After that, use simple LS conf, without filter, just to see correct characters. Should be like this:

input {
  beats {
    port => 5044
    codec => plain { charset=>"UTF-16" }  # Try this, not sure
  }
}
output {

    stdout {
        codec => rubydebug { metadata => true}
    }

}
5

I have tried this but getting then same response from filebeat. It can not decode the messages for some reason. :frowning_face:

6

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