Metricbeat SQL Module : Unable to extract data from a custom query

Hi Team,

Seek your expert advise on this.

I have recently enabled Metricbeat sql module to extract data from a custom query.
I have followed the instructions given in sql.yml file (SQL module | Metricbeat Reference [7.9] | Elastic) and hit below error.

Can you please kindly help on this asap?

Jul 21 16:56:56 <hostname> metricbeat[120547]: 2021-07-21T16:56:56.712+0800 INFO module/wrapper.go:266 Error fetching data for metricset sql.query: error opening connection: testing connection: pq: password authentication failed for user <db_user>```

Hi Team, Please help to check and advise on the above reported issue asap? Thank you!

Looks like a bad connection string / password
What does your connection string look like?
What kind of Database?
Are you using 7.9?
Why not a newer version?

What kind of Database? Postgres
Are you using 7.9? Yes
Why not a newer version? I am sorry. This is organizational direction.
Below are the list of strings I tried and all giving same error.

hosts: ["user=<<Username>> password=<<Password>> dbname='<<Hostname>>:<<Port>>/<<DBName>>' sslmode=disable"]

hosts: ["<<Username>>:<<Password>>@tcp(<<Hostname>>:<<Port>>)/<<DBName>>?sslmode=disable"]

hosts: ["postgres://<<Hostname>>:<<Port>>/<<DBName>>?sslmode=disable&user=<<Username>>&password=<<Password>>"]

hosts: ["user=<<Username>> password=<<Password>> dbname=postgres://<<Hostname>>:<<Port>>/<<DBName>> sslmode=disable"]

hosts: ["postgres://<<Username>>:<<Password>>@<<Hostname>>:<<Port>>/<<DBName>>?sslmode=disable"]

This worked for me

- module: sql
  metricsets:
    - query
  period: 10s
  hosts: ["postgres://myuser:mypassword@192.168.2.205:5432/mydb?sslmode=disable"]

  driver: "postgres"
  sql_query: "select now()"
  sql_response_format: table

I tried with 7.9.3

If your password has special characters perhaps that could be causing escape issues....

Hi Stephen,
Noted with thanks.
Yes. My password is combination of

  1. One capital letter followed by three or more small letters
  2. One special character
  3. Four or more digits

Ex: Abcd#2021

How to use this kind of passwords in the string? Kindly advice.

I am not saying that is the issue just maybe the issue... Maybe try a user with a simple password to see if you can connect.

If you can then that would help diagnose.