Wrong prefix for SQL Server integration fields

I am using the elastic agent SQL Server integration. And the performance index and dashboard. The dashboard expects fields with a prefix: ‘mssql’. And according to the templates this prefix should be ‘mssql.’ But in my data streams all performance fields are preceeded by ‘sql.’

So dashboard stay empty. What’s wrong with ingestion?

Welcome back @hermlam!

Can you share your configuration and which version of the integration you are using? I don't see any known issues on GitHub on the integration, but if nothing looks weird in the configuration it might be worth raising a bug GitHub issue.

Let us know!

Hi Carly,

Thanks for your quick response.

The elastic-agent version is v9.1.4 and the SQL Server Integration is v2.13.0

I am using the default index templates (with ‘mssql.’ prefix

My config:

inputs:
  # Collect audit events from Windows event logs: Collecting audit events from Windows event logs
  - id: audit_logs-winlog
    type: winlog
    streams:
      # SQL Server audit events from Windows event logs: Collect SQL Server audit events from the Windows event logs
      - id: winlog-microsoft_sqlserver.audit
        name: Security
        data_stream:
          dataset: microsoft_sqlserver.audit
          type: logs
        condition: ${host.platform} == 'windows'
        event_id: 33205
        ignore_older: 72h
        tags:
          - preserve_original_event
          # - <TAGS> # Tags
        include_xml: true
        # language: <LANGUAGE> # Language ID: The language ID the events will be rendered in. The language will be forced regardless of the system language. A complete list of language IDs can be found https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-lcid/a9eac961-e77d-41a6-90a5-ce1a8b0cdb9c[here]. It defaults to `0`, which indicates to use the system language. E.g.: 0x0409 for en-US
  # Collect logs from Microsoft SQL Server instances: Collecting error logs from Microsoft SQL Server instances
  - id: audit_logs-logfile
    type: logfile
    streams:
      # Microsoft SQL Server error logs: Collect Microsoft SQL Server error logs
      - id: logfile-microsoft_sqlserver.log
        data_stream:
          dataset: microsoft_sqlserver.log
          type: logs
        paths:
          - /var/opt/mssql/log/error*
        allow_deprecated_use: true
        tags:
          - preserve_original_event
          - mssql-logs
        exclude_files:
          - .gz$
        multiline:
          pattern: ^\d{4}-\d{2}-\d{2}
          negate: true
          match: after
        # encoding: <ENCODING> # Encoding: The file encoding to use for reading data that contains international characters. Valid encoding names are listed [here](https://www.elastic.co/guide/en/beats/filebeat/current/filebeat-input-log.html#_encoding_3).
  # Collect Microsoft SQL Server performance and transaction_log metrics: Collecting performance and transaction_log metrics from Microsoft SQL Server instances
  - id: audit_logs-sql/metrics
    type: sql/metrics
    streams:
      # Microsoft SQL Server performance metrics: Collect Microsoft SQL Server performance metrics
      - id: sql/metrics-microsoft_sqlserver.performance
        data_stream:
          dataset: microsoft_sqlserver.performance
          type: metrics
        metricsets:
          - query
        hosts:
          - sqlserver://domain\username:verysecurepassword@localhost
        period: 60s
        raw_data.enabled: true
        merge_results: true
        dynamic_counter_name: Memory Grants Pend%
        driver: mssql
        sql_queries:
          - query: >-
              SELECT @@servername AS server_name, @@servicename AS instance_name;
            response_format: table
          - query: >-
              SELECT cntr_value As 'user_connections' FROM
              sys.dm_os_performance_counters WHERE counter_name= 'User
              Connections'
            response_format: table
          - query: >-
              SELECT cntr_value As 'active_temp_tables' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Active Temp
              Tables' AND object_name like '%General Statistics%'
            response_format: table
          - query: >-
              SELECT cntr_value As 'buffer_cache_hit_ratio' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache
              hit ratio' AND object_name like '%Buffer Manager%'
            response_format: table
          - query: >-
              SELECT cntr_value As 'page_splits_per_sec' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Page
              splits/sec'
            response_format: table
          - query: >-
              SELECT cntr_value As 'lock_waits_per_sec' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Lock
              Waits/sec' AND instance_name = '_Total'
            response_format: table
          - query: >-
              SELECT cntr_value As 'compilations_per_sec' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'SQL
              Compilations/sec'
            response_format: table
          - query: >-
              SELECT cntr_value As 'batch_requests_per_sec' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Batch
              Requests/sec'
            response_format: table
          - query: >-
              SELECT cntr_value As 'buffer_checkpoint_pages_per_sec' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Checkpoint
              pages/sec' AND object_name like '%Buffer Manager%'
            response_format: table
          - query: >-
              SELECT cntr_value As 'buffer_database_pages' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Database
              pages' AND object_name like '%Buffer Manager%'
            response_format: table
          - query: >-
              SELECT cntr_value As 'buffer_page_life_expectancy' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Page life
              expectancy' AND  object_name like '%Buffer Manager%'
            response_format: table
          - query: >-
              SELECT cntr_value As 'buffer_target_pages' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Target pages'
              AND  object_name like '%Buffer Manager%'
            response_format: table
          - query: >-
              SELECT cntr_value As 'connection_reset_per_sec' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Connection
              Reset/sec' AND object_name like '%General Statistics%'
            response_format: table
          - query: >-
              SELECT cntr_value As 'logins_per_sec' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Logins/sec'
              AND object_name like '%General Statistics%'
            response_format: table
          - query: >-
              SELECT cntr_value As 'logouts_per_sec' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Logouts/sec'
              AND object_name like '%General Statistics%'
            response_format: table
          - query: >-
              SELECT cntr_value As 'transactions' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'Transactions'
              AND object_name like '%General Statistics%'
            response_format: table
          - query: >-
              SELECT cntr_value As 're_compilations_per_sec' FROM
              sys.dm_os_performance_counters WHERE counter_name = 'SQL
              Re-Compilations/sec'
            response_format: table
          - query: >-
              SELECT counter_name, cntr_value FROM
              sys.dm_os_performance_counters WHERE counter_name like 'Memory
              Grants Pend%'
            response_format: variables
        tags:
          - preserve_sql_queries
      # Microsoft SQL Server transaction_log metrics: Collect Microsoft SQL Server transaction_log metrics
      - id: sql/metrics-microsoft_sqlserver.transaction_log
        data_stream:
          dataset: microsoft_sqlserver.transaction_log
          type: metrics
        metricsets:
          - query
        hosts:
          - sqlserver://domain\username:verysecurepassword@localhost
        period: 60s
        driver: mssql
        raw_data.enabled: true
        sql_queries:
          - query: >-
              SELECT @@servername AS server_name, @@servicename AS
              instance_name, DB_NAME() AS 'database_name', DB_ID() AS
              database_id;
            response_format: table
          - query: >-
              SELECT @@servername AS server_name, @@servicename AS
              instance_name, DB_NAME() AS 'database_name', l.database_id,
              l.total_log_size_mb,
              l.active_log_size_mb,l.log_backup_time,l.log_since_last_log_backup_mb,l.log_since_last_checkpoint_mb,l.log_recovery_size_mb
              from sys.dm_db_log_stats(DB_ID()) l INNER JOIN sys.databases s ON
              l.database_id = s.database_id WHERE s.database_id = DB_ID();
            response_format: table
          - query: >-
              SELECT @@servername AS server_name, @@servicename AS
              instance_name, name As 'database_name', l.database_id,
              l.total_log_size_in_bytes As total_log_size_bytes,
              l.used_log_space_in_bytes As used_log_space_bytes,
              l.used_log_space_in_percent As used_log_space_pct,
              l.log_space_in_bytes_since_last_backup from
              sys.dm_db_log_space_usage l INNER JOIN sys.databases s ON
              l.database_id = s.database_id WHERE s.database_id = DB_ID();
            response_format: table
        tags:
          - preserve_sql_queries
        # fetch_from_all_databases: <FETCH_FROM_ALL_DATABASES> # Fetch from all databases: Option to enable fetching transaction_logs metrics from all databases, including both system and user-defined databases. This option overrides any database names provided in the 'Databases' field and instead considers all databases.

part of my index:
sql.metrics.lock_waits_per_sec	37
	sql.metrics.logins_per_sec	1,080
	sql.metrics.logouts_per_sec	1,068
	sql.metrics.memory grants pending	0
	sql.metrics.page_splits_per_sec	118
	sql.metrics.re_compilations_per_sec	63
	sql.metrics.server_name	SQL-Server
	sql.metrics.transactions	0
	sql.metrics.user_connections	13