如何通过Filebeat/NXLog读取SQL Server XEL文件并发送至Graylog？

Absolutely! You’ve got several reliable methods to ingest SQL Server Extended Events (.XEL) binary files into Graylog using tools like Filebeat, NXLog, or even custom scripts. Let’s walk through each approach with practical, actionable steps:

Filebeat doesn’t natively parse XEL binary files, so we’ll first convert the XEL data to structured JSON (easy to process in Graylog) using a script, then have Filebeat ingest and ship that data.

Step 1: Create a Parsing Script #

Use SQL Server’s built-in sys.fn_xe_file_target_read_file function to extract event data from XEL files, then convert it to JSON. Here’s a PowerShell example:

Invoke-SqlCmd -ServerInstance "YOUR_SQL_INSTANCE" -Query "
    SELECT CAST(event_data AS NVARCHAR(MAX)) 
    FROM sys.fn_xe_file_target_read_file('C:\\SQL_Extended_Events\\*.xel', NULL, NULL, NULL)
" | ConvertTo-Json

Make sure the account running Filebeat has permissions to query the SQL Server instance and access the XEL file directory.

Step 2: Configure Filebeat #

Set up Filebeat to run this script on a schedule, capture the output, and send it to Graylog’s GELF input:

filebeat.inputs:
- type: exec
  command: powershell -ExecutionPolicy Bypass -File "C:\\Scripts\\Parse-XEL.ps1"
  schedule: '@every 5m'  # Adjust interval based on your log volume
  enabled: true

output.graylog:
  hosts: ["graylog-server:12201"]  # Use your Graylog server's IP/port for GELF UDP/TCP
  codec.format:
    string: '%{[message]}'  # Ensures JSON data is sent as the GELF message field

NXLog has a built-in xm_sqlserver module that directly parses XEL binary files into structured fields—no extra scripts needed. This is the most efficient option for high-volume XEL logs.

Configure NXLog #

Here’s a minimal configuration to read XEL files, parse them, and send to Graylog:

# Load the SQL Server extension for XEL parsing
<Extension xm_sqlserver>
    Module      xm_sqlserver
</Extension>

# Input: Monitor XEL files
<Input xel_monitor>
    Module      im_file
    File        "C:\\SQL_Extended_Events\\*.xel"
    ReadFromLast True  # Start reading from the end of existing files
    Exec        parse_sqlserver_xel();  # Parse binary XEL to structured data
</Input>

# Output: Send parsed data to Graylog (GELF UDP)
<Output graylog_gelf>
    Module      om_udp
    Host        graylog-server
    Port        12201
    Exec        to_json();  # Convert structured data to JSON for GELF
</Output>

# Route data from input to output
<Route xel_to_graylog>
    Path        xel_monitor => graylog_gelf
</Route>

• Ensure you’re running NXLog Enterprise Edition (the xm_sqlserver module is part of the enterprise feature set)
• Verify the NXLog service account has read access to the XEL file directory

If you need full control over parsing logic, you can use a Python or C# script to:

1. Connect to SQL Server and run sys.fn_xe_file_target_read_file to extract XEL data
2. Transform the data into a format Graylog accepts (GELF is ideal)
3. Send the data directly to Graylog’s REST API or GELF endpoint

For example, a Python snippet using pyodbc and requests:

import pyodbc
import requests
import json

# Connect to SQL Server
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=YOUR_INSTANCE;DATABASE=master;Trusted_Connection=yes;')
cursor = conn.cursor()

# Extract XEL data
cursor.execute("SELECT CAST(event_data AS NVARCHAR(MAX)) FROM sys.fn_xe_file_target_read_file('C:\\SQL_Extended_Events\\*.xel', NULL, NULL, NULL)")
events = cursor.fetchall()

# Send each event to Graylog's GELF HTTP input
graylog_url = "http://graylog-server:12202/gelf"
for event in events:
    gelf_payload = {
        "version": "1.1",
        "host": "sql-server-01",
        "short_message": "SQL Server Extended Event",
        "full_message": event[0],
        "_event_data": json.loads(event[0])
    }
    requests.post(graylog_url, json=gelf_payload)

Key Tips for Success #

• Graylog Input Setup: Make sure you’ve created a GELF input (UDP, TCP, or HTTP) on your Graylog server that matches your output configuration.
• Permissions: All tools need read access to the XEL file directory and (for SQL-based parsing) permissions to query the SQL Server instance.
• Performance: For high-volume logs, NXLog’s native parsing is the most efficient—avoid frequent script runs with Filebeat if you’re dealing with large XEL files.
• Structured Data: Once in Graylog, use extractors or pipelines to pull out specific fields (like event_id, username, or sql_text) from the parsed XEL data for easier searching and analysis.

内容的提问来源于stack exchange，提问作者Arash Mousavi