pgbadger for Azure PostgreSQL

[ali-akbar-786]

Hi,

Is it possible anyway to configure pgbadger over Azure Log Analytics workspace?

[darold]

Hi, I don't know anything about the Azure Log Analytics workspace. If you can obtain a text version of a log and post it here I could be able to answer more precisely.

[darold]

The Azure Log Analytics PostgreSQL log is a multi-line json format, this need more work for integration in pgbadger than other log format. I don't have much time for the moment so this is a low priority unless Microsoft wants to support pgBadger development.

[robinportigliatti]

Hello,

A workaroud I found. The tricky part is to get the DETAIL part of the LOG. They managed to make it ugly, you'll see.

With this log_line_prefix '%m [%p]: [%l-1] xact=%x,user=%u,db=%d,client=%h, app=%a' and by splitting with LOG: I managed to do someting:

Json file:

{
   "properties":{
      "timestamp":"2023-08-17 10:00:08.450 UTC",
      "processId":28218,
      "errorLevel":"LOG",
      "sqlerrcode":"00000",
      "message":"2023-08-17 10:00:08.448 UTC [28218]: [61567-1] xact=0,user=robin,db=dalibo,client=[local], app=[unknown]LOG:  duration: 1.611 ms  bind <unnamed>: SELECT * FROM human WHERE firstname = '$1' AND lastname = '$2'",
      "detail":"parameters: $1 = 'FR', $2 = '0201752211'"
   },
   "time":"2023-08-17T10:00:08.452Z",
   "resourceId":"XXX",
   "location":"XXX",
   "category":"PostgreSQLLogs",
   "operationName":"LogEvent"
}

By executing something like this and thanks to @ioguix for pointing out properties.level usage :

jq -r '
select(.properties? and .properties.message?)
| (.properties.errorLevel+":") as $lvl
| (.properties.message | split($lvl)[0]) as $prefix
| if $prefix then
      $prefix + " " + .properties.errorLevel + ":" + (.properties.message | split($lvl)[1])
    else
      ""
    end,
    if .properties.detail? then
      $prefix + " DETAIL: " + .properties.detail
    else
      ""
    end' PT1H.json > PT1H.log

You get the following output:

2023-08-17 10:00:08.448 UTC [28218]: [61567-1] xact=0,user=robin,db=dalibo,client=[local], app=[unknown]LOG:  duration: 1.611 ms  bind <unnamed>: SELECT * FROM human WHERE firstname = '$1' AND lastname = '$2'
2023-08-17 10:00:08.448 UTC [28218]: [61567-1] xact=0,user=robin,db=dalibo,client=[local], app=[unknown]DETAIL: parameters: $1 = 'FR', $2 = '0201752211'

The downside is that we add the DETAIL message by copying the %l placeholder from original message, without increasing it. It worth checking how %l usually behave in normal logs in this regard.

And now we can execute pgBdager:

pgbadger --jobs=4 -I --prefix="%m [%p]: [%l-1] xact=%x,user=%u,db=%d,client=%h, app=%a" PT1H.log -O /tmp/pgbadger_rapport

But pgBadger doesn't care about us not increasing %l and now we have details for each of our queries.

Hope that helps.

Robin,