Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[FileBeat][Postgresql] Parse <unnamed> query log format #13496

Closed
MarcinAman opened this issue Sep 4, 2019 · 3 comments · Fixed by #13701
Closed

[FileBeat][Postgresql] Parse <unnamed> query log format #13496

MarcinAman opened this issue Sep 4, 2019 · 3 comments · Fixed by #13701
Assignees
Labels
Filebeat Filebeat Team:Integrations Label for the Integrations team

Comments

@MarcinAman
Copy link

Describe the enhancement:
As far as i can tell postgresql module doesn't support format with parse <unnamed>:

2019-09-04 15:52:38.004 CEST [31136] user@host LOG:  duration: 12.437 ms  parse <unnamed>: select * from table

It requires a statement keyword which is not always present so values are not parsed correctly

Describe a specific use case for the enhancement or feature:
Right now users are forced to create custom injests and it takes time...

@MarcinAman MarcinAman changed the title [FileBeat][Postgresql] Exec <unnamed> query log format [FileBeat][Postgresql] Parse <unnamed> query log format Sep 4, 2019
@kaiyan-sheng kaiyan-sheng added Team:Integrations Label for the Integrations team Filebeat Filebeat labels Sep 4, 2019
@ziksi
Copy link

ziksi commented Sep 6, 2019

Hello,
it does not support also execute, bind and so on.
Example slow queries:
duration: 256.251 ms execute dbdpg_p44583_29: ...
duration: 739.010 ms execute pdo_stmt_00000002: ...
For temporay fix you can use: ( in modules/postgresql/log/ingest/pipeline.json)
"patterns": [
"^%{LOCALDATETIME:postgresql.log.timestamp} %{WORD:postgresql.log.timezone} \[%{NUMBER:postgresql.log.thread_id}(-%{BASE16FLOAT:postgresql.log.core_id})?\] ((\[%{USERNAME:postgresql.log.user}\]@\[%{POSTGRESQL_DB_NAME:postgresql.log.database}\]|%{USERNAME:postgresql.log.user}@%{POSTGRESQL_DB_NAME:postgresql.log.database}) )?%{WORD:postgresql.log.level}: (duration: %{NUMBER:postgresql.log.duration} ms .: %{GREEDYDATA:postgresql.log.query}|%{GREEDYDATA:postgresql.log.message})"
],
in which "ms statement:" is only changed to "ms .
:"

In that way slow queries will be matched and parsed properly and will be shown correctly in both PostgreSQL dashboards.

@MarcinAman
Copy link
Author

Thanks, it works :)

For those that will google that issue later:
If you change a ingest definition (this .json file) you need to setup a pipeline once again (use filebeat setup --pipelines -modules=postgresql and disable/enable module).

jsoriano added a commit to jsoriano/beats that referenced this issue Sep 16, 2019
Slow logs in PostgreSQL can log information per query step, parse and
collect the query step and the assigned query name if present.

Fix elastic#13496
@jsoriano
Copy link
Member

@MarcinAman @ziksi thanks for reporting this and providing some examples!
I have opened #13701 to address this issue. It'd be great if you could take a look to the PR or provide some additional example log files that contain slow log entries with these query steps.

jsoriano added a commit that referenced this issue Sep 17, 2019
Slow logs in PostgreSQL can log information per query step. Parse and
collect the query step and the assigned query name if present.

Fix #13496
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Filebeat Filebeat Team:Integrations Label for the Integrations team
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants