elastic / elastic/integrations

[microsoft_sqlserver]: Add tz_offset support for log timestamp parsing

Open
#21,167 0 comments 0 reactions 0 assignees View on GitHub
needs:triage
Dominant language
Handlebars
Stars
333
Forks
647
Avg merge
3d 4h
Merged PRs (30d)
209

Description

### 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:

```yaml
- 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:

```json
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:
```yaml
- 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:
```handlebars
{{#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:
```yaml
- 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`.

Contributor guide

Open the contributing guide

Research direction

Read packages/microsoft_sqlserver/data_stream/log/manifest.yml, agent/stream/log.yml.hbs, and elasticsearch/ingest_pipeline/default.yml, then compare the existing haproxy tz_offset pattern. Validate the generated Fleet policy and ingest pipeline for a configured timezone and for the default UTC behavior; done means MSSQL timestamps receive the configured offset without skipping parsing.

Written by the indexing model from the issue text.

Assessment

Tech stack
elasticsearch, handlebars, sql
Domain
observability-sre
Issue type
Feature
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.