Monitor MSSQL using OpenTelemetry receivers

8min
This document demonstrate the steps to scrape MSSQL metrics as well as logs to KloudMate using various receivers.
The sqlserver receiver grabs metrics about a Microsoft SQL Server instance. The receiver works by either using the Windows Performance Counters, or by directly connecting to the instance and querying it. Windows Performance Counters are only available when running on Windows.
To get the MSSQL logs the SQL Query Receiver used. It uses custom SQL queries to generate metrics or logs from a database connection.
Here hostmetrics receiver, SQL server receiver, and SQL query receiver are configured.
Prerequisite:
MSSQL server must be installed and running.
Install the OpenTelemetry collector on the server. See Installing and Configuring OpenTelemetry Collector.
Make sure to run the collector as administrator in order to collect all performance counters for metrics.
Step-1: Configure the Microsoft SQL Server Receiver along with the hostmetrics receiver scrape the MSSQL metrics and SQL Query Receiver along with the filelog receiver to pull required logs.
Linux Users: Open the file located at /etc/otelcol-contrib/config.yaml using your preferred text editor.
Windows Users: Create a new file called config.yaml in the C:\Program Files\OpenTelemetry Collector folder. You can use Notepad or any text editor to do this.
YAML
extensions:
  health_check:
  pprof:
    endpoint: 0.0.0.0:1777
  zpages:
    endpoint: 0.0.0.0:55679
  file_storage:
    create_directory: true

receivers:
  otlp:
    protocols:
      grpc:
        endpoint: 0.0.0.0:4317
      http:
        endpoint: 0.0.0.0:4318

  hostmetrics:
    collection_interval: 30s
    scrapers:
      cpu:
        metrics:
          system.cpu.utilization:
            enabled: true
      load:
        cpu_average: true
      memory:
        metrics:
          system.memory.utilization:
            enabled: true
      filesystem:
        metrics:
          system.filesystem.usage:
            enabled: true
          system.filesystem.utilization:
            enabled: true
      disk: {}
      network: {}
      processes: {}

  sqlserver:
    collection_interval: 30s
    username: <username>  #use the username
    password: <Password> #use the secure password
    server: 0.0.0.0
    port: 1433
    metrics:
      sqlserver.database.count:
        enabled: true
      sqlserver.database.io:
        enabled: true
      sqlserver.database.latency:
        enabled: true
      sqlserver.database.operations:
        enabled: true
      sqlserver.processes.blocked:
        enabled: true
      sqlserver.resource_pool.disk.throttled.read.rate:
        enabled: true
      sqlserver.resource_pool.disk.throttled.write.rate:
        enabled: true

  sqlquery/upmetrics:
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"  #modify the username and password 
    queries:
      # Query: Collect SQL Server Uptime as a Metric
      - sql: |
          SELECT
              sqlserver_start_time,
              DATEDIFF(MINUTE, sqlserver_start_time, GETDATE()) AS Minutes_Uptime
          FROM
              sys.dm_os_sys_info;
        metrics:
          - metric_name: sqlserver.uptime.minutes
            value_column: Minutes_Uptime
            attribute_columns: ["sqlserver_start_time"]
            static_attributes:
              dbinstance: mydbinstance
  filelog:
    include:
    # Ensure the path is appropriate for your server's operating system. Uncomment the line below if the server is running Windows.
      - /var/opt/mssql/log/errorlog*
      #- 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG*'   
    storage: file_storage
    multiline:
      line_start_pattern: ^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{2}
    retry_on_failure:
      enabled: true
# query to show client connections and IP
  sqlquery/connection:    
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"
    storage: file_storage
    queries:
      - sql: |
          SELECT
              c.session_id,
              c.client_net_address AS client_ip_address,
              s.host_name,
              s.login_name,
              s.program_name,
              s.login_time,
              s.last_request_start_time,
              s.last_request_end_time,
              'mssql_client_log' AS log_source
          FROM
              sys.dm_exec_connections AS c
          JOIN
              sys.dm_exec_sessions AS s
          ON
              c.session_id = s.session_id
          WHERE
              s.last_request_start_time > $$1
          ORDER BY
              s.last_request_start_time ASC;
        tracking_column: last_request_start_time
        tracking_start_value: "" # add today date
        logs:
          - body_column: client_ip_address
            attribute_columns: ["session_id", "host_name", "login_name", "program_name", "login_time", "last_request_start_time", "last_request_end_time", "log_source"]
# To show slow query
  sqlquery/slowquery:
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"
    storage: file_storage
    queries:
      - sql: |
          SELECT
            *,
            SUBSTRING(
              st.text,
              (qs.statement_start_offset / 2) + 1,
              (
                (
                  CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset
                ) / 2
              ) + 1
            ) AS statement_text,
            'mssql_slow_query_log' AS log_source
          FROM
            sys.dm_exec_query_stats AS qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
          WHERE qs.last_execution_time > $$1
          ORDER BY qs.last_execution_time;
        tracking_column: last_execution_time
        tracking_start_value: " "  #add today's date
        logs:
          - body_column: statement_text
            attribute_columns: ["sql_handle", "execution_count", "total_worker_time", "last_execution_time", "creation_time", "total_elapsed_time", "statement_start_offset", "statement_end_offset", "log_source"]   
# To get audit log
  sqlquery/audit:
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"
    storage: file_storage
    collection_interval: 30s
    queries:
      - sql: "SELECT event_time, action_id, succeeded, session_server_principal_name AS user_name, server_principal_name AS login_name, database_name, schema_name, object_name, statement, additional_information FROM sys.fn_get_audit_file('/var/opt/mssql/log/Audit-*.sqlaudit', DEFAULT, DEFAULT) WHERE event_time > $$1 ORDER BY event_time ASC"
        tracking_column: event_time
        tracking_start_value: " " # modify today date
        logs:
          - body_column: statement
            attribute_columns: ["event_time", "action_id", "succeeded", "user_name", "login_name", "database_name", "schema_name", "object_name", "additional_information", "log_source"]

# Active query that is running
  sqlquery/activequery:
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"
    queries:
      - sql: |
          SELECT
              r.session_id,
              r.status,
              r.command,
              r.cpu_time,
              r.start_time,
              s.login_name,
              s.host_name,
              s.program_name,
              st.text AS query_text,
              'mssql_active_query' as log_source
          FROM
              sys.dm_exec_requests r
          INNER JOIN
              sys.dm_exec_sessions s
          ON
              r.session_id = s.session_id
          CROSS APPLY
              sys.dm_exec_sql_text(r.sql_handle) st
          WHERE
              s.is_user_process = 1
        logs:
          - body_column: query_text
            attribute_columns: ["session_id", "status", "command", "cpu_time", "start_time", "login_name", "host_name", "program_name", "log_source"]
# user session
  sqlquery/usersession:
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"
    queries:      
      - sql: |
          SELECT
              session_id,
              login_name,
              host_name,
              program_name,
              login_time,
              status,
              client_interface_name,
              cpu_time,
              memory_usage,
              reads,
              writes,
              'mssql_user_sessions' as log_source
          FROM
              sys.dm_exec_sessions
          WHERE
              is_user_process = 1;
        logs:
          - body_column: login_name
            attribute_columns: ["session_id", "host_name", "program_name", "login_time", "status", "client_interface_name", "cpu_time", "memory_usage", "reads", "writes", "log_source"]
extensions:
  health_check:
  pprof:
    endpoint: 0.0.0.0:1777
  zpages:
    endpoint: 0.0.0.0:55679
  file_storage:
    create_directory: true

receivers:
  otlp:
    protocols:
      grpc:
        endpoint: 0.0.0.0:4317
      http:
        endpoint: 0.0.0.0:4318

  hostmetrics:
    collection_interval: 30s
    scrapers:
      cpu:
        metrics:
          system.cpu.utilization:
            enabled: true
      load:
        cpu_average: true
      memory:
        metrics:
          system.memory.utilization:
            enabled: true
      filesystem:
        metrics:
          system.filesystem.usage:
            enabled: true
          system.filesystem.utilization:
            enabled: true
      disk: {}
      network: {}
      processes: {}

  sqlserver:
    collection_interval: 30s
    username: <username>  #use the username
    password: <Password> #use the secure password
    server: 0.0.0.0
    port: 1433
    metrics:
      sqlserver.database.count:
        enabled: true
      sqlserver.database.io:
        enabled: true
      sqlserver.database.latency:
        enabled: true
      sqlserver.database.operations:
        enabled: true
      sqlserver.processes.blocked:
        enabled: true
      sqlserver.resource_pool.disk.throttled.read.rate:
        enabled: true
      sqlserver.resource_pool.disk.throttled.write.rate:
        enabled: true

  sqlquery/upmetrics:
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"  #modify the username and password 
    queries:
      # Query: Collect SQL Server Uptime as a Metric
      - sql: |
          SELECT
              sqlserver_start_time,
              DATEDIFF(MINUTE, sqlserver_start_time, GETDATE()) AS Minutes_Uptime
          FROM
              sys.dm_os_sys_info;
        metrics:
          - metric_name: sqlserver.uptime.minutes
            value_column: Minutes_Uptime
            attribute_columns: ["sqlserver_start_time"]
            static_attributes:
              dbinstance: mydbinstance
  filelog:
    include:
    # Ensure the path is appropriate for your server's operating system. Uncomment the line below if the server is running Windows.
      - /var/opt/mssql/log/errorlog*
      #- 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG*'   
    storage: file_storage
    multiline:
      line_start_pattern: ^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{2}
    retry_on_failure:
      enabled: true
# query to show client connections and IP
  sqlquery/connection:    
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"
    storage: file_storage
    queries:
      - sql: |
          SELECT
              c.session_id,
              c.client_net_address AS client_ip_address,
              s.host_name,
              s.login_name,
              s.program_name,
              s.login_time,
              s.last_request_start_time,
              s.last_request_end_time,
              'mssql_client_log' AS log_source
          FROM
              sys.dm_exec_connections AS c
          JOIN
              sys.dm_exec_sessions AS s
          ON
              c.session_id = s.session_id
          WHERE
              s.last_request_start_time > $$1
          ORDER BY
              s.last_request_start_time ASC;
        tracking_column: last_request_start_time
        tracking_start_value: "" # add today date
        logs:
          - body_column: client_ip_address
            attribute_columns: ["session_id", "host_name", "login_name", "program_name", "login_time", "last_request_start_time", "last_request_end_time", "log_source"]
# To show slow query
  sqlquery/slowquery:
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"
    storage: file_storage
    queries:
      - sql: |
          SELECT
            *,
            SUBSTRING(
              st.text,
              (qs.statement_start_offset / 2) + 1,
              (
                (
                  CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset
                ) / 2
              ) + 1
            ) AS statement_text,
            'mssql_slow_query_log' AS log_source
          FROM
            sys.dm_exec_query_stats AS qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
          WHERE qs.last_execution_time > $$1
          ORDER BY qs.last_execution_time;
        tracking_column: last_execution_time
        tracking_start_value: " "  #add today's date
        logs:
          - body_column: statement_text
            attribute_columns: ["sql_handle", "execution_count", "total_worker_time", "last_execution_time", "creation_time", "total_elapsed_time", "statement_start_offset", "statement_end_offset", "log_source"]   
# To get audit log
  sqlquery/audit:
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"
    storage: file_storage
    collection_interval: 30s
    queries:
      - sql: "SELECT event_time, action_id, succeeded, session_server_principal_name AS user_name, server_principal_name AS login_name, database_name, schema_name, object_name, statement, additional_information FROM sys.fn_get_audit_file('/var/opt/mssql/log/Audit-*.sqlaudit', DEFAULT, DEFAULT) WHERE event_time > $$1 ORDER BY event_time ASC"
        tracking_column: event_time
        tracking_start_value: " " # modify today date
        logs:
          - body_column: statement
            attribute_columns: ["event_time", "action_id", "succeeded", "user_name", "login_name", "database_name", "schema_name", "object_name", "additional_information", "log_source"]

# Active query that is running
  sqlquery/activequery:
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"
    queries:
      - sql: |
          SELECT
              r.session_id,
              r.status,
              r.command,
              r.cpu_time,
              r.start_time,
              s.login_name,
              s.host_name,
              s.program_name,
              st.text AS query_text,
              'mssql_active_query' as log_source
          FROM
              sys.dm_exec_requests r
          INNER JOIN
              sys.dm_exec_sessions s
          ON
              r.session_id = s.session_id
          CROSS APPLY
              sys.dm_exec_sql_text(r.sql_handle) st
          WHERE
              s.is_user_process = 1
        logs:
          - body_column: query_text
            attribute_columns: ["session_id", "status", "command", "cpu_time", "start_time", "login_name", "host_name", "program_name", "log_source"]
# user session
  sqlquery/usersession:
    driver: mssql
    datasource: "sqlserver://<user>:<password>@localhost:1433?database=Kloudmate-test&encrypt=disable"
    queries:      
      - sql: |
          SELECT
              session_id,
              login_name,
              host_name,
              program_name,
              login_time,
              status,
              client_interface_name,
              cpu_time,
              memory_usage,
              reads,
              writes,
              'mssql_user_sessions' as log_source
          FROM
              sys.dm_exec_sessions
          WHERE
              is_user_process = 1;
        logs:
          - body_column: login_name
            attribute_columns: ["session_id", "host_name", "program_name", "login_time", "status", "client_interface_name", "cpu_time", "memory_usage", "reads", "writes", "log_source"]
﻿
Step 2: Configure the processor part to detect resource information from the host and append or override the resource value in telemetry data with this information. 
On-premise, non-cloud, or cloud
YAML
processors:
  batch:
    send_batch_size: 10000
    timeout: 30s
  resourcedetection:
    detectors: [system]
    system:
      resource_attributes:
        host.name:
          enabled: true
        host.id:
          enabled: true
        os.type:
          enabled: false
  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: host.name
processors:
  batch:
    send_batch_size: 10000
    timeout: 30s
  resourcedetection:
    detectors: [system]
    system:
      resource_attributes:
        host.name:
          enabled: true
        host.id:
          enabled: true
        os.type:
          enabled: false
  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: host.name
﻿
AWS EC2: 
Optional: To retrieve AWS EC2 instance tags along with logs and metrics, you need to associate an IAM role with the EC2 instance that includes the EC2:DescribeTags policy. The processor below needs to be added:
YAML
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/ec2:
    detectors:  ["ec2"]
    ec2:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$ 
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: ec2.tag.Name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/ec2:
    detectors:  ["ec2"]
    ec2:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$ 
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: ec2.tag.Name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
﻿
Azure Virtual Machines: 
YAML
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/azure:
    detectors: ["azure"]
    azure:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: azure.vm.name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/azure:
    detectors: ["azure"]
    azure:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: azure.vm.name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
﻿
Step 3: 
Set up the KloudMate Backend on the exporter part with the correct extensions of the OpenTelemetry configuration file and configure the pipeline. 
Change the pipeline as per your correct configuration.
YAML
exporters:
  debug:
    verbosity: detailed
  otlphttp:
    endpoint: 'https://otel.kloudmate.com:4318'
    headers:
      Authorization: xxxxxxxx  # Use the auth key

service:
  pipelines:
    metrics:
      receivers: [sqlserver, hostmetrics, sqlquery/upmetrics]
      processors: [batch, resourcedetection, resource] 
      exporters: [debug, otlphttp]
   
    log:
      receivers: [filelog, sqlquery/activequery, sqlquery/connection, sqlquery/audit, sqlquery/usersession, sqlquery/slowquery]
      processors: [batch, resourcedetection, resource] 
      exporters: [debug, otlphttp]
  extensions: [health_check, pprof, zpages, file_storage]
exporters:
  debug:
    verbosity: detailed
  otlphttp:
    endpoint: 'https://otel.kloudmate.com:4318'
    headers:
      Authorization: xxxxxxxx  # Use the auth key

service:
  pipelines:
    metrics:
      receivers: [sqlserver, hostmetrics, sqlquery/upmetrics]
      processors: [batch, resourcedetection, resource] 
      exporters: [debug, otlphttp]
   
    log:
      receivers: [filelog, sqlquery/activequery, sqlquery/connection, sqlquery/audit, sqlquery/usersession, sqlquery/slowquery]
      processors: [batch, resourcedetection, resource] 
      exporters: [debug, otlphttp]
  extensions: [health_check, pprof, zpages, file_storage]
﻿
Step 4: Restart the Opentelemetry collector service
Linux: 
Text
sudo systemctl restart otelcol-contrib
sudo systemctl status otelcol-contrib
sudo systemctl restart otelcol-contrib
sudo systemctl status otelcol-contrib
﻿
Windows: 
Go to the Windows service. You can directly search and open otherwise can go to Run ➝ type services.msc ➝ ok 
On service search for OpenTelemetry collector service and restart it.
﻿
Did this page help you?
MYSQL integration with KloudMate
Vcenter Integration with KloudMate