Grokparsefailure PostgreSQL

Elastic Stack Logstash
1

Hi, im trying to build a filter for PostgreSQL and I have managed to make it work with the debugger. But when I receive it in my application I see that logstash failed to parse it.

Log example:

2018-05-29 11:09:44 -03 [9708]: [1-1] user=postgres,db=postgres,app=pgAdmin3 LTS by BigSQL - Browser,client=192.168.13.149 LOG: statement: SELECT version();

and my grok pattern, this is setup in the postgresql pattern file under the name POSTGRESQL2:

%{TIMESTAMP_ISO8601:timestamp} %{NUMBER:time_zone} [%{NUMBER:sin_identificar}]: %{NOTSPACE} user=%{DATA:username},db=%{DATA:db},app=%{DATA:cliente},client=%{IP:ip_cliente} %{DATA:log_level}: statement: %{GREEDYDATA:operacion}

grok
grok.PNG1093×275 10.1 KB

My Logstash configuration:

input {
beats {
port => 5044
}
}
filter {
grok {
match => { "message" => "^%{POSTGRESQL2}" }
}
}

output {
http {
url => "http://192.168.13.149:8080/logserver-web/webresources/logs/BD"
http_method => "post"
}
}

Im new to working with grok patterns and logstash, so I would love to receive any help I can get to make this work. Thanks in advance.

2

I think you should point custom pattern directory.

https://www.elastic.co/guide/en/logstash/current/plugins-filters-grok.html#plugins-filters-grok-patterns_dir

If you set logstash log level to debug, it will print all loaded grok patterns. You can check if your pattern is loaded or not.

3

I see the following entry:

[2018-05-30T12:25:53,839][DEBUG][logstash.filters.grok ] Grok compiled OK {:pattern=>"^%{POSTGRESQL2}", :expanded_pattern=>"...(regexp pattern here)

And if a change to a random name it says that it cant recognize it, so I assume it is loading it right.

In debug mode I also see an exception in java.lang.UnsatisfiedLinkError: no netty_tcnative_linux_x86_64 in java.library.path with a kilometric stacktrace, dont know if that library is somewhat responsible for the parse failure.

4

I think square brackets require escaping.

POSTGRESQL2 %{TIMESTAMP_ISO8601:timestamp} %{NUMBER:time_zone} \[%{NUMBER:sin_identificar}\]: %{NOTSPACE} user=%{DATA:username},db=%{DATA:db},app=%{DATA:cliente},client=%{IP:ip_cliente} %{DATA:log_level}: statement: %{GREEDYDATA:operacion}

output:

output received {"event"=>{"timestamp"=>"2018-05-29 11:09:44", "path"=>"C:\\development\\elk\\logstash\\postgres.txt", "ip_cliente"=>"192.168.13.149", "host"=>"TR00200384", "log_level"=>"LOG", "db"=>"postgres", "@version"=>"1", "time_zone"=>"-03", "@timestamp"=>2018-05-30T18:44:03.831Z, "message"=>"2018-05-29 11:09:44 -03 [9708]: [1-1] user=postgres,db=postgres,app=pgAdmin3 LTS by BigSQL - Browser,client=192.168.13.149 LOG: statement: SELECT version();\r", "cliente"=>"pgAdmin3 LTS by BigSQL - Browser", "operacion"=>"SELECT version();\r", "username"=>"postgres", "sin_identificar"=>"9708"}}
{
          "timestamp" => "2018-05-29 11:09:44",
               "path" => "C:\\ericsson\\development\\elk\\logstash\\postgres.txt",
         "ip_cliente" => "192.168.13.149",
               "host" => "TR00200384",
          "log_level" => "LOG",
                 "db" => "postgres",
           "@version" => "1",
          "time_zone" => "-03",
         "@timestamp" => 2018-05-30T18:44:03.831Z,
            "message" => "2018-05-29 11:09:44 -03 [9708]: [1-1] user=postgres,db=postgres,app=pgAdmin3 LTS by BigSQL - Browser,client=192.168.13.149 LOG: statement: SELECT version();\r",
            "cliente" => "pgAdmin3 LTS by BigSQL - Browser",
          "operacion" => "SELECT version();\r",
           "username" => "postgres",
    "sin_identificar" => "9708"
}
5

Well, although it wasn't the missing escape to the square brackets it made me look closer around there. In my file I got the brackets escaped, even the : but pasting it here without the code frame deleted them.

The real problem apparently was that postgresql sends 2 spaces after "LOG:" and I was accounting for just one. Now they are getting filtered correctly. I really thank your time helping me.