SQL Server Metrics via Telegraf

This integration allows you to configure a Telegraf agent to send your metrics, in multiple formats, to Logit.io.

Telegraf is a flexible server agent equipped with plug-in support, useful for sending metrics and events from data sources like web servers, APIs, application logs, and cloud services.

To ship your metrics to Logit.io, we will integrate the relevant input and outputs.http plug-in into your Telegraf configuration file.

Choose the install for your operating system below to get started:

Windows

wget https://dl.influxdata.com/telegraf/releases/telegraf-1.19.2_windows_amd64.zip

Download and extract to: C:\Program Files\Logitio\telegraf\

Configuration file: C:\Program Files\Logitio\telegraf\

MacOS

brew install telegraf

Configuration file x86_64 Intel: /usr/local/etc/telegraf.conf Configuration file ARM (Apple Silicon): /opt/homebrew/etc/telegraf.conf

Ubuntu/Debian

wget -q https://repos.influxdata.com/influxdata-archive_compat.key
echo '393e8779c89ac8d958f81f942f9ad7fb82a25e133faddaf92e15b16e6ac9ce4c influxdata-archive_compat.key' | sha256sum -c && cat influxdata-archive_compat.key | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/influxdata-archive_compat.gpg > /dev/null
echo 'deb [signed-by=/etc/apt/trusted.gpg.d/influxdata-archive_compat.gpg] https://repos.influxdata.com/debian stable main' | sudo tee /etc/apt/sources.list.d/influxdata.list

sudo apt-get update
sudo apt-get install telegraf

Configuration file: /etc/telegraf/telegraf.conf

RedHat and CentOS

cat <<EOF | sudo tee /etc/yum.repos.d/influxdata.repo
[influxdata]
name = InfluxData Repository - Stable
baseurl = https://repos.influxdata.com/stable/\$basearch/main
enabled = 1
gpgcheck = 1
gpgkey = https://repos.influxdata.com/influxdata-archive_compat.key
EOF

sudo yum install telegraf

Configuration file: /etc/telegraf/telegraf.conf

SLES & openSUSE

zypper ar -f obs://devel:languages:go/ go
zypper in telegraf

Configuration file: /etc/telegraf/telegraf.conf

FreeBSD/PC-BSD

sudo pkg install telegraf

Configuration file: /etc/telegraf/telegraf.conf

Read more about how to configure data scraping and configuration options for Telegraf

The configuration file below is pre-configured to scrape the system metrics from your hosts, add the following code to the configuration file /etc/telegraf/telegraf.conf from the previous step.

# # Read metrics from SQL queries
[[inputs.sql]]
#   ## Database Driver
#   ## See https://github.com/influxdata/telegraf/blob/master/docs/SQL_DRIVERS_INPUT.md for
#   ## a list of supported drivers.
    driver = "mysql"

#   ## Data source name for connecting
#   ## The syntax and supported options depends on selected driver.
    dsn = "username:password@mysqlserver:3307/dbname?param=value"

#   ## Timeout for any operation
#   ## Note that the timeout for queries is per query not per gather.
#   # timeout = "5s"

#   ## Connection time limits
#   ## By default the maximum idle time and maximum lifetime of a connection is unlimited, i.e. the connections
#   ## will not be closed automatically. If you specify a positive time, the connections will be closed after
#   ## idleing or existing for at least that amount of time, respectively.
#   # connection_max_idle_time = "0s"
#   # connection_max_life_time = "0s"

#   ## Connection count limits
#   ## By default the number of open connections is not limited and the number of maximum idle connections
#   ## will be inferred from the number of queries specified. If you specify a positive number for any of the
#   ## two options, connections will be closed when reaching the specified limit. The number of idle connections
#   ## will be clipped to the maximum number of connections limit if any.
#   # connection_max_open = 0
#   # connection_max_idle = auto

    [[inputs.sql.query]]
#     ## Query to perform on the server
      query="SELECT user,state,latency,score FROM Scoreboard WHERE application > 0"
#     ## Alternatively to specifying the query directly you can select a file here containing the SQL query.
#     ## Only one of 'query' and 'query_script' can be specified!
#     # query_script = "/path/to/sql/script.sql"
#
#     ## Name of the measurement
#     ## In case both measurement and 'measurement_col' are given, the latter takes precedence.
#     # measurement = "sql"
#
#     ## Column name containing the name of the measurement
#     ## If given, this will take precedence over the 'measurement' setting. In case a query result
#     ## does not contain the specified column, we fall-back to the 'measurement' setting.
#     # measurement_column = ""
#
#     ## Column name containing the time of the measurement
#     ## If ommited, the time of the query will be used.
#     # time_column = ""
#
#     ## Format of the time contained in 'time_col'
#     ## The time must be 'unix', 'unix_ms', 'unix_us', 'unix_ns', or a golang time format.
#     ## See https://golang.org/pkg/time/#Time.Format for details.
#     # time_format = "unix"
#
#     ## Column names containing tags
#     ## An empty include list will reject all columns and an empty exclude list will not exclude any column.
#     ## I.e. by default no columns will be returned as tag and the tags are empty.
#     # tag_columns_include = []
#     # tag_columns_exclude = []
#
# 		## Column names containing fields (explicit types)
#     ## Convert the given columns to the corresponding type. Explicit type conversions take precedence over
# 		## the automatic (driver-based) conversion below.
# 		## NOTE: Columns should not be specified for multiple types or the resulting type is undefined.
#     # field_columns_float = []
#     # field_columns_int = []
# 		# field_columns_uint = []
# 		# field_columns_bool = []
# 		# field_columns_string = []
#
#     ## Column names containing fields (automatic types)
#     ## An empty include list is equivalent to '[*]' and all returned columns will be accepted. An empty
#     ## exclude list will not exclude any column. I.e. by default all columns will be returned as fields.
#     ## NOTE: We rely on the database driver to perform automatic datatype conversion.
#     # field_columns_include = []
#     # field_columns_exclude = []

Replace username:password with the correct details for your SQL database.

Read more about how to configure data scraping and configuration options for SQL Server

Once you have generated the configuration file, you need to set up the output plug-in to allow Telegraf to transmit your data to Logit.io in Prometheus format. This can be accomplished by incorporating the following code into your configuration file:

[[outputs.http]]
  
  url = "https://<your-metrics-username>:<your-metrics-password>@<your-metrics-stack-id>-vm.logit.io:0/api/v1/write"
  data_format = "prometheusremotewrite"

  [outputs.http.headers]
    Content-Type = "application/x-protobuf"
    Content-Encoding = "snappy"

Windows

telegraf.exe --service start

MacOS

telegraf --config telegraf.conf

Linux

sudo service telegraf start

for systemd installations

systemctl start telegraf

Data should now have been sent to your Stack.

View my data

If you don't see metrics take a look at How to diagnose no data in Stack below for how to diagnose common issues.

If you don't see data appearing in your Stack after following the steps, visit the Help Centre guide for steps to diagnose no data appearing in your Stack or Chat to support now.

To effectively monitor and analyze SQL Server metrics in a distributed environment, organizations require a reliable and efficient metrics management solution. Telegraf, an open-source metrics collection agent, can gather SQL Server metrics from various sources, including operating SQL Server, databases, and other applications.

With a wide range of input plugins, Telegraf enables users to collect metrics from different sources, such as CPU usage, memory usage, network activity, and more. To store and query the collected metrics, organizations can use Prometheus, an open-source monitoring and alerting tool that supports a flexible querying language and graphical visualization of data.

By configuring Telegraf to output metrics in the Prometheus format and using Prometheus to scrape the metrics from the Telegraf server, organizations can ship SQL Server metrics from Telegraf to Prometheus. This process involves setting up Telegraf to collect SQL Server metrics, outputting them in the Prometheus format, configuring Prometheus to scrape the metrics from the Telegraf server, and visualizing the data using Prometheus' flexible querying and graphical visualization capabilities.

Using Telegraf to ship SQL Server metrics to Prometheus is a reliable and efficient metrics management solution for distributed environments. It enables organizations to gain insights into SQL Server performance, optimize their distributed SQL Server, and troubleshoot any issues that arise.

If you need any further assistance with shipping your log data to Logit.io we're here to help you get started. Feel free to get in contact with our support team by sending us a message via live chat & we'll be happy to assist.