[microsoft_sqlserver]: Add tz_offset support for log timestamp parsing

[jalogisch]

Integration Name

Microsoft SQL Server [packages/microsoft_sqlserver]

Dataset Name

microsoft_sqlserver.log

Integration Version

2.17.1

Agent Version

9.4.1

OS Version and Architecture

Windows Server 2019 (x86_64)

User Goal

Configure the Microsoft SQL Server integration to correctly parse log timestamps for servers running in non-UTC timezones. SQL Server writes error log timestamps in local server time with no UTC offset marker. I need the integration to apply the correct timezone when converting those timestamps to @timestamp.

Existing Features

There is no tz_offset or timezone configuration option in the MSSQL integration's Fleet policy UI. The integration's ingest pipeline parses timestamps unconditionally as UTC:

- date:
    if: ctx.event.timezone == null
    field: date
    formats:
    - yyyy-MM-dd HH:mm:ss.SS

There is no second branch for ctx.event.timezone != null, so injecting event.timezone via a pre-pipeline would cause the date processor to be skipped entirely — the timestamp would never be parsed. Other integrations (e.g., haproxy) already support tz_offset via a two-branch date processor pattern. MSSQL does not.

What did you see?

SQL Server writes error log lines like:

2026-09-09 10:30:00.97 Logon       Error: 978, Severity: 14, State: 1.

No timezone marker. The integration parses 10:30:00.97 as UTC. For a server in CEST (UTC+2), the resulting @timestamp is 2026-09-09T10:30:00.970Z, which displays in Kibana as 12:30:00 CEST — two hours late.

The offset is consistent and matches exactly the host's UTC offset. All events are affected, not just during edge cases.

Workaround I'm currently using — @custom ingest pipeline that re-extracts the timestamp from event.original (which is preserved by the managed pipeline) and re-parses with an explicit timezone:

PUT _ingest/pipeline/logs-microsoft_sqlserver.log@custom
{
  "description": "Timezone workaround. Remove when integration natively supports tz_offset.",
  "processors": [
    {
      "grok": {
        "field": "event.original",
        "patterns": ["(?<_mssql_ts>\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}\\.\\d+)"],
        "ignore_failure": true,
        "ignore_missing": true
      }
    },
    {
      "date": {
        "if": "ctx._mssql_ts != null",
        "field": "_mssql_ts",
        "formats": ["yyyy-MM-dd HH:mm:ss.SS", "yyyy-MM-dd HH:mm:ss.SSS"],
        "timezone": "Europe/Oslo",
        "on_failure": [
          {
            "append": {
              "field": "error.message",
              "value": "[@custom] Failed to apply timezone: {{ _ingest.on_failure_message }}"
            }
          }
        ]
      }
    },
    { "remove": { "field": "_mssql_ts", "ignore_missing": true } }
  ]
}

This works but requires hardcoding the timezone, does not integrate with Fleet, and needs to be deleted manually if/when native support lands.

Anything else?

The haproxy integration already implements tz_offset support. The pattern requires changes to three files in packages/microsoft_sqlserver/data_stream/log/:

Note: The specific code sketch below was derived from a Claude AI analysis session comparing the haproxy and MSSQL integration source. It has not been hand-verified against the current source tree. Treat it as a starting point, not a ready-to-merge patch — engineers familiar with the integration internals should validate before implementing.

1. manifest.yml — add var:

- name: tz_offset
  type: text
  title: Timezone
  required: false
  show_user: false
  description: >-
    IANA time zone or UTC offset (e.g. `Europe/Oslo` or `+0200`) for interpreting
    log timestamps. Leave empty if SQL Server runs in UTC.

2. agent/stream/log.yml.hbs — add timezone/locale block:

{{#if tz_offset}}
fields_under_root: true
fields:
  _conf:
    tz_offset: "{{tz_offset}}"
{{/if}}
processors:
- add_locale: ~

3. elasticsearch/ingest_pipeline/default.yml — add second date processor branch:

- date:
    if: ctx.event.timezone != null
    field: date
    formats:
    - yyyy-MM-dd HH:mm:ss.SS
    timezone: '{{ event.timezone }}'
    on_failure:
    - append:
        field: error.message
        value: '{{ _ingest.on_failure_message }}'

This matches the pattern used by haproxy and other integrations that support tz_offset.