Filter postgresql ERROR, FATAL, PANIC messages from postgresql logs

[elk_newbie (Patrick)]

Hi community,

Is there any way to filter only ERROR, FATAL and PANIC messages from Postgresql logs?

appreciate any help!

Regards
Patrick

[Badger]

Assuming you have the log level at the front of the message followed by a colon you could do it using

dissect { mapping => { "message" => "%{[@metadata][loglevel]}: %{}" } }
if [@metadata][loglevel] not in [ "ERROR", "FATAL", "PANIC" ] { drop {} }

[elk_newbie (Patrick)]

Hi Badger, thanks for your tip!

The logs look like the following:

FATAL:
< 2021-03-14 20:51:01.683 CET ngapp sup1 [unknown] 18825 > FATAL: remaining connection slots are reserved for non-replication superuser connections

ERROR:
< 2021-03-30 00:18:59.623 CEST ngapp sup1 AppT 31253 > ERROR: column table.col6 does not exist at character 783
< 2021-03-30 00:18:59.623 CEST ngapp sup1 AppT 31253 > STATEMENT: select table.col1 as col_1_, table.col2 as col_2, table.col6 as col_6 from Table table where table.col4=$1

I would need to catch the STATEMENT as well.

[Badger]

OK, so I would probably adjust the dissect to be

dissect { mapping => { "message" => "< %{[@metadata][timestamp]} %{+[@metadata][timestamp]}  %{+[@metadata][timestamp]}  %{appname} %{field1} [%{field2}] %{number} > %{loglevel}: %{logMessage}"

If you want to keep the STATEMENT line then add "STATEMENT" to the array with the others

if [loglevel] not in [ "ERROR", "FATAL", "PANIC", "STATEMENT" ] { drop {} }

The joda timezone page does not list CEST as one it supports (it does support CET) so I would parse the date using

mutate {
    gsub => [
        "[@metadata][timestamp]", " CEST$", " +02:00",
        "[@metadata][timestamp]", " CET$", " +01:00" ]
    ]
}
date { match => [ "[@metadata][timestamp]", "YYYY-MM-dd HH:mm:ss.SSS ZZ" ] }

[elk_newbie (Patrick)]

Thanks a lot for your help!