Setting up SQL Server Monitoring

Grafana InfluxDB
1

I am having some problems setting up grafana with influxdb

I am looking at setting up https://grafana.com/dashboards/409 I have installed influxdb on the grafana server, and I have followed this article: https://github.com/influxdata/telegraf/tree/master/plugins/inputs/sqlserver

I have edited my test sql environment and created a telegraf user, and then I went into influxdb’s config and at the end of the added the lines:

> # Read metrics from Microsoft SQL Server
> [[inputs.sqlserver]]
>  # servers = [
>   #  "Server=MY_SQL_SERVER_IP;Port=1433;User Id=<user>;Password=<pw>;app name=telegraf;log=1;",
>   # ]

User Id, can i put the telegraf user i created earlier in there? and what is app name?

Thanks!

2

Hi,

Yes user ID and password of the user you created earlier should be used in the connectionstring in telegraf. App name is a name that will be used when telegraf connecting to mssql. If you run a trace in mssql you’ll be able to see what specific application that’s executing queries in question.

Please inspect the telegraf agent log for errors to investigate what may be the problem.

Marcus

3

Thanks for the reply, I’m still having some issues.

So i added the user to SQL, installed InfluxDb and telegraf on my grafana server (ubunto).

In the telegraf.conf I then added the connection string:

# Read metrics from Microsoft SQL Server
# servers = [
#  "Server=10.x.x.x;Port=1443;User Id=telegraf;Password=Password1234!;app name = telegraf;log1;",
# ]

I still see no instances showing up when i load up module 409…what am i doing wrong? Where are the logs that i can check to see if any issues/errors?

4

You have to remove the #-characters - they’re comments!

5

Well that makes too much sense doesnt it lol…

So i’ve taken out the #'s and I’m still not seeing the instances show up in the grafana.
Server1 = grafana/influxdb
Server2 = MS SQL

the connection string looks like
vim telegraf.conf
(added to the end of the file)

# Read metrics from Microsoft SQL Server
[[inputs.sqlserver]]
 servers = [
  "Server=SERVER2;Port=1443;User Id=telegraf;Password=Password1234!;app name = telegraf;log1;",
 ]

I want to put the MS SQL server’s ip in the “Server=” tag right?

I tried that but I’m still not seeing anything

In the data source tab on Grafana I added a influxDB datasource with the HTTP pointing to http://GRAFANASERVER:8086 // Access: Direct

InfluxDB Details:
Database: SQL_Metrics (database i created in influx)
User: blank
Password: blank

6

When trying to start the telegraf service I see i’m getting:

Error in plugin[inputs.sqlserver]: unable to open tcp connection with host
SERVER2:1443: dial tcp SERVER2:1443: getsockopt: connection refused

May be a networking issue then…although the firewalls seem to be off on that server (windows firewall on the SQL server)

Edit:

So I’m now failing on getting telegraf to authenticate.

I want to use a service account on our domain rather than the telegraf user but I cant seem to get the connection string to work with a domain.

I’m trying

servers = [
  "Server=SQLSERVER;Port=1433;User Id=DOMAIN\user;Password=Password;app name=telegraf;log=1;",
]

But it gives me an error of:
Error parsing /etc/telegraf/telegraf.conf, toml: line 3410: parse error

line 3410 is the line with the domain\user in User Id=

It would appear this is supported via the article at GitHub - denisenkom/go-mssqldb: Microsoft SQL server driver written in go language

7

I’m not sure telegraf supports windows authentication - please start with validating this!

Please note that you can start telegraf with just console output enabled (no data will be sent to influx) which can help you verify that telegraf at least can extract correct metrics. See telegraf console arguments for more information.

Marcus

8

Hi

I have this in the telegraf.conf (ip and pw are changed here):

Read metrics from Microsoft SQL Server

[[inputs.sqlserver]]

Specify instances to monitor with a list of connection strings.

All connection parameters are optional.

By default, the host is localhost, listening on default port, TCP 1433.

for Windows, the user is the currently running AD user (SSO).

See https://github.com/denisenkom/go-mssqldb for detailed connection

parameters.

servers = [
“Server=my_server_ip;Port=1433;User Id=telegraf;Password=my_pwd;app name=telegraf;log=1;”,
]
query_version = 2

If you would like to exclude some of the metrics queries, list them here

Possible choices:

- PerformanceCounters

- WaitStatsCategorized

- DatabaseIO

- DatabaseProperties

- CPUHistory

- DatabaseSize

- DatabaseStats

- MemoryClerk

- VolumeSpace

exclude_query = [ ‘DatabaseIO’ ]

but dashboard https://grafana.com/dashboards/409 does not show anything, cant select any instance, and SHOW TAG VALUES WITH KEY = “servername” on the influxdb returns nothing, changing it to SHOW TAG VALUES WITH KEY = “my host that sends data” does not make the dashboard work either

what am i missing?

9

Have you verified that you get any data into influxdb? Maybe the dashboard is outdated? And as I wrote above:

10

I’ve tested the data flow and from what i can see i get the data in Influx, but what the dashboard is looking for does not look to match what is there, do you know a working dashboard?

11

Please include example data queried directly from influxdb. Maybe dashboard is outdated and you’ll need to manually update it?

12

in your telegraf config file, in the sql outputs section, you might change the query_type to 1 instead of 2…worked for me!

1 Like
13

the query_version = 1 did it, thank you

14

Glad to hear it and to be of service!

15

Hi,

I have loaded 409 dashboard and i see telegraph is pushing data to influxdb but still i see data in dashboard is empty. . i followed all the steps “https://github.com/influxdata/telegraf/tree/master/plugins/inputs/sqlserver” but still its empty . can you let me know what would be the reason ?
configuration of influxdb says test connection is successfull inside grafana.

16

hi

how to sepcify the sqll instance running on sql server ?

thanks

17

See GitHub - denisenkom/go-mssqldb: Microsoft SQL server driver written in go language

18

I have been following this thread, and after trying what you have suggested for Windows Auth, I still see in the Telegraf console output:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication

I am running this on Docker from my computer.
The DB runs on qaDB_XYZ.

My connection string looks like:

  servers = [
 "Server=qaDB_XYZ;Port=1433;Database=SomeDBName;Integrated Security=SSPI;app name=telegraf;log=1;user id=CORP\\my_user_name"

]

Do I need to ask my DBA to add a SQL Server login, as I saw on one of the GitHub README files - i.e., add a special ‘telegraf’ login?