elastic / elastic/integrations
[microsoft_sqlserver]: Add tz_offset support for log timestamp parsing
- 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
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