GROK filter for multi-line Postgresql log

Elastic Stack Logstash
1

Hi All,

I am having hard times trying to merge multi-line Postgresql logs with GROK into one Logstash event.

My log file look like this:

Jan 22 09:45:01 myhost postgres[19015]: [2-1] postgres postgres 19015 ::1 ERROR: column "procpid" does not exist at character 8
Jan 22 09:45:01 myhost postgres[19015]: [2-2] postgres postgres 19015 ::1 STATEMENT: select procpid,backend_start,xact_start,pg_terminate_backend(procpid)
Jan 22 09:45:01 myhost postgres[19015]: [2-3] #011 from pg_stat_activity
Jan 22 09:45:01 myhost postgres[19015]: [2-4] #011 where procpid <> pg_backend_pid() and
Jan 22 09:45:01 myhost postgres[19015]: [2-5] #011 text(client_addr) not like '172.16.20.%' and
Jan 22 09:45:01 myhost postgres[19015]: [2-6] #011 text(client_addr) not like '172.16.21.%' and
Jan 22 09:45:01 myhost postgres[19015]: [2-7] #011 extract(epoch from (now() - backend_start)) > 60 * 60 * 2;

I'm trying to merge all parts (i.e. from [2-1] to [2-N]) but it doesn't work for me and I'm wondering whether it is possible at all.

I would appreciate if someone can share any hints or his experience on this.

Thanks

1 Like
2

What distinguishes the first or last event in a sequence that you want to combine? That the second number in the number group inside the brackets is 1 (one)?

3

Yes, that would be a second number in a group.

... [2-1] ...
... [2-2] ...
... [2-3] ...
... [2-4] ...
... [2-5] ...
... [2-6] ...
... [2-7] ...

4

A multiline codec/filter similar to

multiline {
  pattern => "... \[\d+-1\]" # adjust pattern to be more exact
  negate => true
  what => "previous"
}

should work; lines that aren't the first message in a group should be joined with the previous line.

1 Like
5

Cool! It works! Thanks a lot.

One small question, is it possible to get rid of syslog prefixes of the joined events? What I mean is that currently I'm getting one big event message, like below, and I want to skip the parts in bold:

Jan 22 09:45:01 myhost postgres[19015]: [2-1] postgres postgres 19015 ::1 ERROR: column "procpid" does not exist at character 8 Jan 22 09:45:01 myhost postgres[19015]: [2-2] postgres postgres 19015 ::1 STATEMENT: select procpid,backend_start,xact_start,pg_terminate_backend(procpid) Jan 22 09:45:01 myhost postgres[19015]: [2-3] #011 from pg_stat_activity Jan 22 09:45:01 myhost postgres[19015]: [2-4] #011 where procpid <> pg_backend_pid() and Jan 22 09:45:01 myhost postgres[19015]: [2-5] #011 text(client_addr) not like '172.16.20.%' and Jan 22 09:45:01 myhost postgres[19015]: [2-6] #011 text(client_addr) not like '172.16.21.%' and Jan 22 09:45:01 myhost postgres[19015]: [2-7] #011 extract(epoch from (now() - backend_start)) > 60 * 60 * 2;

6

Perhaps the mutate filter's gsub option can help you remove this? It's probably easier doing that first and then join lines together with a multiline filter.

7

Hi Eugene,

is it possible to post the grok or if you can your logstash config file to parse Postgresql log messages ?

Thank you

8

Eugene, good morning.

Could you post your Grok solution to the Postgresql logs case, including the input and output area? I'm in the same situation.

Thank you very much.