Metricbeat sql module - specify database name when connecting to MSSQL

Elastic Stack Beats
1

According to the documentation, it doesn't appear to be possible to specify the database name when connecting to Microsoft SQL. I'd like to use this module to connect to Azure SQL in order to run a query against a database which isn't master, in order to extract data.
How can I specify which. database to connect to?

I've tried a few different types of hosts connection strings in order to connect, but it doesn't work.

["sqlserver://${SQL_USERNAME}:${SQL_PASSWORD}@${SQL_HOST}:1433;database=${SQL_DATABASE}"]

["sqlserver://${SQL_USERNAME}:${SQL_PASSWORD}@${SQL_HOST}:1433/${SQL_DATABASE}"]

I just want to run a query every minute to extract a count from a table so I can use it as a metric and monitor it.

2

Hello,

Can't you specify the database name in the FROM statement?

e.g of the documentation:

sql_query: 'SELECT * FROM sys.dm_db_log_space_usage'

They're querying the table dm_db_log_space_usage of the database sys.

3

Seems to not be supported.

2022-05-10T12:02:49.556Z	ERROR	go-mssqldb@v0.0.0-20200206145737-bbfc9a55622e/net.go:167	recovered from panic while fetching 'sql/query' for host 'mydatabasename.database.windows.net'. Recovering, but please report this.	{"panic": "Not implemented", "stack": "github.com/elastic/beats/v7/libbeat/logp.Recover\n\t/go/src/github.com/elastic/beats/libbeat/logp/global.go:102\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:1038\ngithub.com/denisenkom/go-mssqldb.passthroughConn.SetWriteDeadline\n\t/go/pkg/mod/github.com/denisenkom/go-mssqldb@v0.0.0-20200206145737-bbfc9a55622e/net.go:167\ncrypto/tls.(*Conn).SetWriteDeadline\n\t/usr/local/go/src/crypto/tls/conn.go:151\ncrypto/tls.(*Conn).closeNotify\n\t/usr/local/go/src/crypto/tls/conn.go:1361\ncrypto/tls.(*Conn).Close\n\t/usr/local/go/src/crypto/tls/conn.go:1331\ngithub.com/denisenkom/go-mssqldb.(*Conn).Close\n\t/go/pkg/mod/github.com/denisenkom/go-mssqldb@v0.0.0-20200206145737-bbfc9a55622e/mssql.go:361\ndatabase/sql.(*driverConn).finalClose.func2\n\t/usr/local/go/src/database/sql/sql.go:646\ndatabase/sql.withLock\n\t/usr/local/go/src/database/sql/sql.go:3396\ndatabase/sql.(*driverConn).finalClose\n\t/usr/local/go/src/database/sql/sql.go:644\ndatabase/sql.(*DB).Close\n\t/usr/local/go/src/database/sql/sql.go:904\ngithub.com/elastic/beats/v7/x-pack/metricbeat/module/sql/query.(*MetricSet).Fetch\n\t/go/src/github.com/elastic/beats/x-pack/metricbeat/module/sql/query/query.go:91\ngithub.com/elastic/beats/v7/metricbeat/mb/module.(*metricSetWrapper).fetch\n\t/go/src/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:263\ngithub.com/elastic/beats/v7/metricbeat/mb/module.(*metricSetWrapper).startPeriodicFetching\n\t/go/src/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:224\ngithub.com/elastic/beats/v7/metricbeat/mb/module.(*metricSetWrapper).run\n\t/go/src/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:208\ngithub.com/elastic/beats/v7/metricbeat/mb/module.(*Wrapper).Start.func1\n\t/go/src/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:147"}
4

Also, I believe sys in that query is the schema name, not the database name.

SELECT count (*) FROM [sys].[all_objects]
5

Yes, I mean, it was an example. You can pass the database name in the FROM sentence also. In this case you should pass the database where you wanna do the query.

Can you check the following thread? MSSQL monitoring with Metricbeat?

It seems that he could do the query against a mssql.

6

The above error came from a query which was:

SELECT count (*) from [database].[schema].[table] WHERE columnname = 0

I'll try some other variants, but the few that i've tried results in that panic with no specific details as to why

7

The go-mssqldb driver does allow for the database name to be specified: see here but it seems that the elastic module implementation doesn't allow the database name to be passed through.

This is really a required feature in order to be able to query Azure SQL specifically.

8

I believe i've got it to work now by using the go-mssqldb documentation.

You can specify the database name if you do the connection string in this format:

hosts: ["sqlserver://${SQL_USERNAME}:${SQL_PASSWORD}@${SQL_HOST}:1433?database=${SQL_DATABASE}"]

The data does actually come through, and I get a field for sql.metrics.numeric.count which contains the result of the query.

However...
It only works once. You only get one event.
The query is set to run every 30 seconds, and despite the fact that the first event makes it through to Elasticsearch, and i can see the data in kibana, this error is still always thrown:

2022-05-10T13:24:42.809Z	ERROR	go-mssqldb@v0.0.0-20200206145737-bbfc9a55622e/net.go:167	recovered from panic while fetching 'sql/query' for host 'prod-db-ufurnish-ie.database.windows.net:1433'. Recovering, but please report this.	{"panic": "Not implemented", "stack": "github.com/elastic/beats/v7/libbeat/logp.Recover\n\t/go/src/github.com/elastic/beats/libbeat/logp/global.go:102\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:1038\ngithub.com/denisenkom/go-mssqldb.passthroughConn.SetWriteDeadline\n\t/go/pkg/mod/github.com/denisenkom/go-mssqldb@v0.0.0-20200206145737-bbfc9a55622e/net.go:167\ncrypto/tls.(*Conn).SetWriteDeadline\n\t/usr/local/go/src/crypto/tls/conn.go:151\ncrypto/tls.(*Conn).closeNotify\n\t/usr/local/go/src/crypto/tls/conn.go:1361\ncrypto/tls.(*Conn).Close\n\t/usr/local/go/src/crypto/tls/conn.go:1331\ngithub.com/denisenkom/go-mssqldb.(*Conn).Close\n\t/go/pkg/mod/github.com/denisenkom/go-mssqldb@v0.0.0-20200206145737-bbfc9a55622e/mssql.go:361\ndatabase/sql.(*driverConn).finalClose.func2\n\t/usr/local/go/src/database/sql/sql.go:646\ndatabase/sql.withLock\n\t/usr/local/go/src/database/sql/sql.go:3396\ndatabase/sql.(*driverConn).finalClose\n\t/usr/local/go/src/database/sql/sql.go:644\ndatabase/sql.(*DB).Close\n\t/usr/local/go/src/database/sql/sql.go:904\ngithub.com/elastic/beats/v7/x-pack/metricbeat/module/sql/query.(*MetricSet).Fetch\n\t/go/src/github.com/elastic/beats/x-pack/metricbeat/module/sql/query/query.go:98\ngithub.com/elastic/beats/v7/metricbeat/mb/module.(*metricSetWrapper).fetch\n\t/go/src/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:263\ngithub.com/elastic/beats/v7/metricbeat/mb/module.(*metricSetWrapper).startPeriodicFetching\n\t/go/src/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:224\ngithub.com/elastic/beats/v7/metricbeat/mb/module.(*metricSetWrapper).run\n\t/go/src/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:208\ngithub.com/elastic/beats/v7/metricbeat/mb/module.(*Wrapper).Start.func1\n\t/go/src/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:147"}

I have this containerised, so if i terminate the container, and run it again, I get 1 more event containing the data, and then nothing.

9

Configuration:

- module: sql
      metricsets:
        - query
      period: 30s
      hosts: ["sqlserver://${SQL_USERNAME}:${SQL_PASSWORD}@${SQL_HOST}:1433?database=mydatabasename"]
      driver: "mssql"
      sql_query: 'SELECT count (*) AS count FROM schema.tablename where columnname = 0'
      sql_response_format: table
10

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.