How to track SLA breaches in closed tickets via database fields

[Morduk95]

• Used Zammad version: 6.4.1
• Used Zammad installation type: source

Expected behavior:

• I’d like to analyze which tickets were closed before SLA escalation.
• I expect to find a stable field in the database (e.g. escalation_at) that I can use to compare with closed_at.
• Ideally, escalation_at would retain its value even after the ticket is closed, so I can use it for reporting.

---

Actual behavior:

• The escalation_at field in the tickets table gets reset (set to NULL) when a ticket is closed.
• This makes it difficult to determine whether a ticket was resolved before escalation.
• Other fields like escalation_calculation exist, but it’s unclear how to interpret them for historical analysis.

---

Steps to reproduce the behavior:

1. Create and apply an SLA to a ticket with escalation rules (e.g. first response in 60 minutes).
2. Check the ticket’s escalation_at value — it shows the expected UTC timestamp.
3. Close the ticket.
4. Re-check the escalation_at field — it is now NULL.
5. This removes the ability to determine whether the ticket breached SLA or not, post-closure.

---

Additional context:

• Are there any other fields or recommended approaches to track SLA escalation history in the database?
• Is it possible to prevent escalation_at from being reset on closure, or persist it in a custom field/workflow?
• My goal is to produce external reports via SQL or BI tools that can verify if a ticket was resolved before escalation.

Thanks in advance for your support!

[Knobelix]

Hi,

I was facing exactly the same challenge just now.
My workaround was as follows:

1. Create a trigger that adds a tag to tickets which have reached the escalation point.
2. In the “tag_items” database, identify the ID for the escalation tag.
3. In the “tags” database, find all tickets that have this tag assigned.
   This database also contains a timestamp for when the tag was added.
   That timestamp corresponds to the moment the ticket was escalated.

With that, I was able to create a suitable report in Power BI.