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

[VinodKumarPotta]

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>```

[VinodKumarPotta]

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

[stephenb (Stephen Brown)]

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?

[VinodKumarPotta]

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"]

[stephenb (Stephen Brown)]

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....

[VinodKumarPotta]

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.

[stephenb (Stephen Brown)]

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.