GROK filter for multi-line Postgresql log

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

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)?

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

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

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

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;

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.

Hi Eugene,

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

Thank you

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.