Audit ticket timestamps before measuring SLA performance

A negative response duration is a data problem before it is a performance result. So is a midnight timestamp that stands in for an unknown time, or two systems recording the same event in different zones without offsets. Calculating first and investigating later can turn weak evidence into a confident percentage.

This audit diagnoses input quality. The clock guide owns valid service-time arithmetic, while the SLA glossary owns the definition. Keep those responsibilities distinct: changing a calendar will not repair a missing first-response event.

Preserve source values and meanings

For each event retain ticket, cycle, source event ID, raw timestamp, parsed instant, supplied offset or zone, precision, source system and extract time. Record what the field represents. Resolved_at, closed_at and last_updated_at may identify different actions.

Separate an absent event from an absent timestamp on an event known to exist. An unresolved ticket normally has no resolution event; that is not automatically a quality failure. A ticket marked completed with no usable stop time is a different condition.

Microsoft's Power Query documentation provides the DateTimeZone type and functions for handling offset-bearing values. A parsed date-time without an offset does not by itself establish the correct event instant. Preserve the original zone evidence and obtain system semantics from the administrator. DateTimeZone functions.

Run six checks before calculating

Check required fields for the selected obligation, parse failures, precision, offset/zone coverage, impossible event order and duplicate or conflicting events. Evaluate the checks by account and source system, because one poorly configured source can disappear inside an otherwise healthy total.

A stop before start requires review. A pause without a resume may be valid at cutoff or may represent a missing event. A first response before opening can indicate imported history or clock skew; do not fix it by forcing duration to zero. Repeated event IDs usually signal repeated extracts, while repeated timestamps with different IDs may be legitimate activity.

A date-only event cannot establish a minute-level obligation. If the source rounds to whole minutes, document how events near the target boundary are treated. Never invent seconds to make a result appear exact.

A worked audit population

This synthetic sample contains 50 completed response obligations. They all require opening and first-response instants. Each row receives one mutually exclusive primary quality disposition after review.

Among the 40 usable obligations, 34 pass and six breach. Observed attainment is 34/40 = 85%. Outcome coverage is 40/50 = 80%. Present both figures. An 85% result with 20% unknown evidence is not equivalent to 85% on a fully observed population.

If all ten unknown outcomes ultimately failed, the full population would have 34/50 = 68% passes; if they all passed, it would have 44/50 = 88%. This 68%-88% range illustrates evidence uncertainty under the stated completed-obligation population. It does not classify unknown events as contractual breaches.

Repair from evidence, not a favorable assumption

Give each exception an owner, source reference, proposed correction, justification, reviewer and correction time. Recover missing events from an authorized event log if possible. If an offset was incorrectly stripped, repair it using documented source behavior, retaining both original and corrected values.

Do not shift all times by an hour because one example looks wrong. Daylight-saving changes, multiple offices or system migrations may require dated zone rules. Test repairs on known events around the transition and record which rows the rule affects.

Atlassian separates SLA conditions from calendars, including calendar time zones. Check both event interpretation and configured working time instead of treating every discrepancy as a timestamp error. SLA calendar documentation.

Re-run controls after repair

Reconcile the source count to usable plus unresolved dispositions. Check that duplicate removal is supported by stable identifiers and that a repair never creates two outcomes for one obligation. Compare repaired results with the originally issued output and log every changed result.

Keep unresolved rows visible. The denominator guide distinguishes open work from unknown evidence, and the ticket-history workflow shows where these checks belong in file preparation.

Make the audit useful

The deliverable should be a concise exception list, quality coverage by source/account, a repair log and a recalculated sample with traceable events. Prioritize failures that affect high-volume or contract-sensitive accounts rather than simply counting empty cells.

An authorized sample can establish whether the evidence supports the intended report. Discuss the data and reporting scope before assuming an automatic repair, connector or ongoing monitoring service. A clear unknown is more useful than an invented timestamp presented as fact.