# Metricbeat sql module - specify database name when connecting to MSSQL

**URL:** <https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336>\
**Category:** Beats\
**Tags:** metricbeat\
**Created:** [May 10, 2022, 10:44am UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336 "2022-05-10T10:44:57Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![ianufurnish.com](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ianufurnish.com/32/105484_2.png) [@ianufurnish.com](https://discuss.elastic.co/u/ianufurnish.com)\
**Post date:** [May 10, 2022, 10:44am UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336/1 "2022-05-10T10:44:57Z")

</div>

According to the [documentation](https://www.elastic.co/guide/en/beats/metricbeat/current/metricbeat-module-sql.html), 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.

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

```

```auto
["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.

---

<div class="post-metadata">

**Author:** ![adrianfusco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/adrianfusco/32/111798_2.png) [@adrianfusco](https://discuss.elastic.co/u/adrianfusco)\
**Post date:** [May 10, 2022, 11:55am UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336/2 "2022-05-10T11:55:51Z")

</div>

Hello,

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

e.g of the documentation:

```auto
sql_query: 'SELECT * FROM sys.dm_db_log_space_usage'

```

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

---

<div class="post-metadata">

**Author:** ![ianufurnish.com](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ianufurnish.com/32/105484_2.png) [@ianufurnish.com](https://discuss.elastic.co/u/ianufurnish.com)\
**Post date:** [May 10, 2022, 12:05pm UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336/3 "2022-05-10T12:05:05Z")

</div>

Seems to not be supported.

```auto
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"}

```

---

<div class="post-metadata">

**Author:** ![ianufurnish.com](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ianufurnish.com/32/105484_2.png) [@ianufurnish.com](https://discuss.elastic.co/u/ianufurnish.com)\
**Post date:** [May 10, 2022, 12:07pm UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336/4 "2022-05-10T12:07:55Z")

</div>

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

```auto
SELECT count (*) FROM [sys].[all_objects]

```

---

<div class="post-metadata">

**Author:** ![adrianfusco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/adrianfusco/32/111798_2.png) [@adrianfusco](https://discuss.elastic.co/u/adrianfusco)\
**Post date:** [May 10, 2022, 12:17pm UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336/5 "2022-05-10T12:17:30Z")

</div>

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?](https://discuss.elastic.co/t/mssql-monitoring-with-metricbeat/252733/5)

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

---

<div class="post-metadata">

**Author:** ![ianufurnish.com](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ianufurnish.com/32/105484_2.png) [@ianufurnish.com](https://discuss.elastic.co/u/ianufurnish.com)\
**Post date:** [May 10, 2022, 1:03pm UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336/6 "2022-05-10T13:03:15Z")

</div>

The above error came from a query which was:

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

---

<div class="post-metadata">

**Author:** ![ianufurnish.com](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ianufurnish.com/32/105484_2.png) [@ianufurnish.com](https://discuss.elastic.co/u/ianufurnish.com)\
**Post date:** [May 10, 2022, 1:08pm UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336/7 "2022-05-10T13:08:44Z")

</div>

The go-mssqldb driver does allow for the database name to be specified: [see here](https://docs.microsoft.com/en-us/azure/azure-sql/database/connect-query-go#insert-code-to-query-the-database) 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.

---

<div class="post-metadata">

**Author:** ![ianufurnish.com](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ianufurnish.com/32/105484_2.png) [@ianufurnish.com](https://discuss.elastic.co/u/ianufurnish.com)\
**Post date:** [May 10, 2022, 1:31pm UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336/8 "2022-05-10T13:31:19Z")

</div>

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:

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

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

---

<div class="post-metadata">

**Author:** ![ianufurnish.com](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ianufurnish.com/32/105484_2.png) [@ianufurnish.com](https://discuss.elastic.co/u/ianufurnish.com)\
**Post date:** [May 11, 2022, 8:35am UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336/9 "2022-05-11T08:35:26Z")

</div>

Configuration:

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

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 8, 2022, 10:35am UTC](https://discuss.elastic.co/t/metricbeat-sql-module-specify-database-name-when-connecting-to-mssql/304336/10 "2022-06-08T10:35:52Z")

</div>

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