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
Thanks for sharing. I would suggest raising a GitHub issue specifying the issue in case it's an issue with the default templates.
© 2020. All Rights Reserved - Elasticsearch
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant logo are trademarks of the Apache Software Foundation in the United States and/or other countries.