The Microsoft SQL Server integration package allows you to search, observe, and visualize the SQL Server audit logs, as well as performance and transaction log metrics, through Elasticsearch.

Data streams

The Microsoft SQL Server integration collects two types of data streams: logs and metrics.

Log data streams provide records of events happening in Microsoft SQL Server:

• audit: Events from the configured Windows event log channel, providing detailed auditing information. See SQL Server Audit.
• logs: Error logs created by the Microsoft SQL server for troubleshooting and system events.

Other log sources, such as files, are not supported.

Find more details in Logs.

Metrics data streams provide insights into SQL Server performance and health:

• performance: Comprehensive performance counters and objects available on the server.
• transaction_log: Usage statistics and space utilization metrics for transaction logs.

Find more details in Metrics.

Requirements

You need Elasticsearch for storing and searching your data and Kibana for visualizing and managing it. You can use our hosted Elasticsearch Service on Elastic Cloud, which is recommended, or self-manage the Elastic Stack on your hardware.

Microsoft SQL Server permissions

Before you can start sending data to Elastic, make sure you have the necessary Microsoft SQL Server permissions.

If you browse Microsoft Developer Network (MSDN) for the following tables, you will find a "Permissions" section that defines the permission needed for each table (for example, the "Permissions" section on the sys.dm_db_log_space_usage page).

1. transaction_log:
   • sys.databases
   • sys.dm_db_log_space_usage
   • sys.dm_db_log_stats (DB_ID) (Available on SQL Server (MSSQL) 2016 (13.x) SP 2 and later)
2. performance:
   • sys.dm_os_performance_counters

Please make sure the user has the permissions to system as well as user-defined databases. For the particular user used in the integration, the following requirements are met:

User setup options:

• Grant specific permissions as mentioned in the MSDN pages above.
• Alteratively, use sysadmin role (includes all required permissions): This can be configured via SQL Server Management Studio (SSMS) in Server Roles. Read more about joining a role in the SQL Server documentation.

User Mappings (using SQL Server Management Studio (SSMS)):

• Open SSMS and connect to your server.
• Navigate to "Object Explorer" > "Security" > "Logins".
• Right-click the user and select "Properties".
• In the "User Mapping" tab, select the appropriate database and grant the required permissions.

Setup

For step-by-step instructions on how to set up any integration, refer to the Getting started guide.

Below you'll find more specific details on setting up the Microsoft SQL Server integration.

Named Instance

Microsoft SQL Server has a feature that allows running multiple databases on the same host (or clustered hosts) with separate settings. Establish a named instance connection by using the instance name along with the hostname (e.g. host/instance_name or host:named_instance_port) to collect metrics. Details of the host configuration are provided below.

Query by Instance Name or Server Name in Kibana

The data can be visualized in Kibana by filtering based on the instance name and server name. The instance name can be filtered by mssql.metrics.instance_name and the server name by mssql.metrics.server_name fields.

Host Configuration

As part of the input configuration, you need to provide the user name, password and host details. The host configuration supports both named instances or default (no-name) instances, using the syntax below.

Note: This integration supports collecting metrics from a single host. For multi-host metrics, each host can be run as a new integration.

Connecting to Default Instance (host):

• host (e.g. localhost (Instance name is not needed when connecting to default instance))
• host:port (e.g. localhost:1433)

Connecting to Named Instance (host):

• host/instance_name (e.g. localhost/namedinstance_01)
• host:named_instance_port (e.g. localhost:60873)

Configuration

Audit

There are several levels of auditing for SQL Server, depending on government or standards requirements for your installation. The SQL Server Audit feature enables you to audit server-level and database-level groups of events and individual events.

For more information on the different audit levels, refer to SQL Server Audit Action Groups and Actions. Then to enable auditing for SQL Server, refer to these instructions.

Note: For the integration package to be able to read and send audit events the event target must be configured to be Windows event log.

Audit events

Collects SQL Server audit events from the specified windows event log channel.

Log

The SQL Server log contains user-defined events and certain system events you can use for troubleshooting.

Read more in View the SQL Server error log in SQL Server Management Studio.

Performance metrics

Collects the performance counter metrics. The dynamic counter feature provides flexibility to collect metrics by providing the counter as an input. This input can be a regular expression which will filter results based on pattern. For example, if %grant% is given as input, it will enable metrics collection for all of the counters with names like 'Memory Grants Pending', 'Active memory grants count' etc. MSSQL supports a limited set of regular expressions. For more details, refer to Pattern Matching in Search Conditions.

Note: Dynamic counters will go through some basic ingest pipeline post-processing to make counter names in lowercase and remove special characters and these fields will not have any static field mappings.

The feature merge_results has been introduced in 8.4 beats which creates a single event by combining the metrics in a single event. For more details, refer to SQL module.

Read more in instructions about each performance counter metrics.

Transaction log metrics

The system-level database transaction_log metrics for SQL Server instances are collected by default. Metrics for user-level databases can be collected by specifying a list of user databases or by enabling the Fetch from all databases toggle to collect metrics from all databases on the server.

Read more in instructions and the operations supported by transaction log.

Fetch from all databases

To simplify the process of fetching metrics from all databases on the server, you can enable the Fetch from all databases toggle when configuring the integration. This field overrides manually entered database names in the Databases input and instead fetches the required transaction_log metrics from all databases, including system and user-defined databases.

Keep in mind that this feature is disabled by default and needs to be manually enabled to be activated.

Password URL encoding

When the password contains special characters, pass these special characters using URL encoding.

Logs

audit

The SQL Server audit dataset provides events from the configured Windows event log channel. All SQL Server audit-specific fields are available in the sqlserver.audit field group.

ECS Field Reference

Please refer to the following document for detailed information on ECS fields.

Exported fields

log

The Microsoft SQL Server log dataset parses error logs created by the Microsoft SQL server.

An example event for log looks as following:

{
    "@timestamp": "2022-07-14T07:12:49.210Z",
    "agent": {
        "ephemeral_id": "688f9c4d-2ac0-43b6-9421-bf465d5c92f0",
        "id": "42a4484f-4eb2-4802-bd76-1f1118713d64",
        "name": "docker-fleet-agent",
        "type": "filebeat",
        "version": "8.3.0"
    },
    "cloud": {
        "account": {},
        "instance": {
            "id": "b30e45e6-7900-4900-8d67-e37cb13374bc",
            "name": "obs-int-windows-dev"
        },
        "machine": {
            "type": "Standard_D16ds_v5"
        },
        "provider": "azure",
        "region": "CentralIndia",
        "service": {
            "name": "Virtual Machines"
        }
    },
    "data_stream": {
        "dataset": "microsoft_sqlserver.log",
        "namespace": "ep",
        "type": "logs"
    },
    "ecs": {
        "version": "8.11.0"
    },
    "elastic_agent": {
        "id": "42a4484f-4eb2-4802-bd76-1f1118713d64",
        "snapshot": false,
        "version": "8.3.0"
    },
    "event": {
        "agent_id_status": "verified",
        "category": [
            "database"
        ],
        "dataset": "microsoft_sqlserver.log",
        "ingested": "2022-07-14T07:13:12Z",
        "kind": "event",
        "original": "2022-07-14 07:12:49.21 Server      Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) \n\tMay 29 2022 15:55:47 \n\tCopyright (C) 2019 Microsoft Corporation\n\tDeveloper Edition (64-bit) on Linux (Ubuntu 20.04.4 LTS) <X64>",
        "type": [
            "info"
        ]
    },
    "input": {
        "type": "log"
    },
    "log": {
        "file": {
            "path": "/tmp/service_logs/errorlog"
        },
        "flags": [
            "multiline"
        ],
        "offset": 0
    },
    "message": "Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) \n\tMay 29 2022 15:55:47 \n\tCopyright (C) 2019 Microsoft Corporation\n\tDeveloper Edition (64-bit) on Linux (Ubuntu 20.04.4 LTS) <X64>",
    "microsoft_sqlserver": {
        "log": {
            "origin": "Server"
        }
    },
    "tags": [
        "mssql-logs"
    ]
}

ECS Field Reference

Please refer to the following document for detailed information on ECS fields.

Exported fields

Metrics

performance

The Microsoft SQL Server performance dataset provides metrics from the performance counter table. All performance metrics will be available in the sqlserver.metrics field group.

An example event for performance looks as following:

{
    "@timestamp": "2022-11-23T05:03:28.987Z",
    "agent": {
        "ephemeral_id": "70f5c0c1-37b1-486b-9806-8105b2cdcd20",
        "id": "6d444a4a-2158-445e-8953-dc6eef720a34",
        "name": "docker-fleet-agent",
        "type": "metricbeat",
        "version": "8.5.0"
    },
    "cloud": {
        "account": {},
        "instance": {
            "id": "b30e45e6-7900-4900-8d67-e37cb13374bc",
            "name": "obs-int-windows-dev"
        },
        "machine": {
            "type": "Standard_D16ds_v5"
        },
        "provider": "azure",
        "region": "CentralIndia",
        "service": {
            "name": "Virtual Machines"
        }
    },
    "data_stream": {
        "dataset": "microsoft_sqlserver.performance",
        "namespace": "ep",
        "type": "metrics"
    },
    "ecs": {
        "version": "8.11.0"
    },
    "elastic_agent": {
        "id": "6d444a4a-2158-445e-8953-dc6eef720a34",
        "snapshot": false,
        "version": "8.5.0"
    },
    "event": {
        "agent_id_status": "verified",
        "dataset": "microsoft_sqlserver.performance",
        "duration": 41134100,
        "ingested": "2022-11-23T05:03:30Z",
        "module": "sql"
    },
    "host": {
        "architecture": "x86_64",
        "containerized": false,
        "hostname": "docker-fleet-agent",
        "id": "66392b0697b84641af8006d87aeb89f1",
        "ip": [
            "172.18.0.5"
        ],
        "mac": [
            "02-42-AC-12-00-05"
        ],
        "name": "docker-fleet-agent",
        "os": {
            "codename": "focal",
            "family": "debian",
            "kernel": "5.10.104-linuxkit",
            "name": "Ubuntu",
            "platform": "ubuntu",
            "type": "linux",
            "version": "20.04.5 LTS (Focal Fossa)"
        }
    },
    "metricset": {
        "name": "query",
        "period": 60000
    },
    "mssql": {
        "metrics": {
            "active_temp_tables": 0,
            "batch_requests_per_sec": 54,
            "buffer_cache_hit_ratio": 24,
            "buffer_checkpoint_pages_per_sec": 105,
            "buffer_database_pages": 2215,
            "buffer_page_life_expectancy": 16,
            "buffer_target_pages": 2408448,
            "compilations_per_sec": 80,
            "connection_reset_per_sec": 13,
            "instance_name": "MSSQLSERVER",
            "lock_waits_per_sec": 4,
            "logins_per_sec": 16,
            "logouts_per_sec": 15,
            "memory_grants_pending": 0,
            "page_splits_per_sec": 9,
            "re_compilations_per_sec": 0,
            "server_name": "d10aad520431",
            "transactions": 0,
            "user_connections": 1
        }
    },
    "service": {
        "address": "elastic-package-service_microsoft_sqlserver_1",
        "type": "sql"
    }
}

ECS Field Reference

Please refer to the following document for detailed information on ECS fields.

Exported fields

transaction_log

The Microsoft SQL Server transaction_log dataset provides metrics from the log space usage and log stats tables. All transaction_log metrics will be available in the sqlserver.metrics field group.

An example event for transaction_log looks as following:

{
    "@timestamp": "2022-12-20T07:34:29.687Z",
    "agent": {
        "ephemeral_id": "8d528ff8-5e90-4572-89f6-61fb3a6c96f1",
        "id": "d44a1c4a-95bf-47e9-afb0-453a2ef43c00",
        "name": "192.168.1.2",
        "type": "metricbeat",
        "version": "8.5.3"
    },
    "data_stream": {
        "dataset": "microsoft_sqlserver.transaction_log",
        "namespace": "default",
        "type": "metrics"
    },
    "ecs": {
        "version": "8.11.0"
    },
    "elastic_agent": {
        "id": "d44a1c4a-95bf-47e9-afb0-453a2ef43c00",
        "snapshot": false,
        "version": "8.5.3"
    },
    "event": {
        "agent_id_status": "verified",
        "dataset": "microsoft_sqlserver.transaction_log",
        "duration": 2147044750,
        "ingested": "2022-12-20T07:34:32Z",
        "module": "sql"
    },
    "host": {
        "architecture": "x86_64",
        "containerized": false,
        "hostname": "192.168.1.2",
        "id": "627E8AE5-E918-5073-A58E-8A2D9ED96875",
        "ip": [
            "192.168.1.2"
        ],
        "mac": [
            "36-F7-DC-28-23-80"
        ],
        "name": "192.168.1.2",
        "os": {
            "build": "21D62",
            "family": "darwin",
            "kernel": "21.3.0",
            "name": "macOS",
            "platform": "darwin",
            "type": "macos",
            "version": "12.2.1"
        }
    },
    "metricset": {
        "name": "query",
        "period": 60000
    },
    "mssql": {
        "metrics": {
            "database_id": 1,
            "database_name": "master",
            "instance_name": "MSSQLSERVER",
            "log_space_in_bytes_since_last_backup": 602112,
            "server_name": "obs-int-mssql20",
            "total_log_size_bytes": 2088960,
            "used_log_space_bytes": 1024000,
            "used_log_space_pct": 49.01960754394531
        }
    },
    "service": {
        "address": "20.228.135.242",
        "type": "sql"
    }
}

ECS Field Reference

Please refer to the following document for detailed information on ECS fields.

Exported fields

Changelog