labs.cases.state_updates
Purpose
Build your own response metrics on top of Cases: mean time to acknowledge and resolve per team, case volume per service, KPI breaches, or the alerts that open the most Cases. The labs.cases.state_updates dataset records a snapshot of a Case every time its state changes, so you can query the full lifecycle of every Case with DataPrime and chart it in Custom Dashboards.
Each event holds the whole Case as it was at that moment: status, priority, assignee, lifecycle timestamps, the alerts that feed it, its labels, and where its notifications were delivered. The metadata.trigger field says what caused the snapshot.
labs.cases.state_updates is a Labs (experimental) dataset. Its fields can change as the dataset evolves.
For ready-made response metrics without writing queries, use Cases analytics.
How events are recorded
Coralogix records an event when a Case is created, activated, reactivated, acknowledged, resolved, closed, or archived, when its priority or assignee changes, when it breaches a KPI, and when it is otherwise updated.
A Case that stays open with no changes for more than an hour gets a periodic caseIdle snapshot, so an open Case still appears in any query window, even when nothing happened to it in that window.
As a result, the same caseId appears many times in a time range. To count Cases rather than events, reduce the results to one row per caseId before you aggregate, or filter on a trigger that fires once per Case. See Query examples.
Query the dataset
source system/labs.cases.state_updates
Fields in the table below are under $d. Timestamps in the payload, such as createdAt and resolvedAt, are numbers in epoch nanoseconds. Cast them with :timestamp before you compare or subtract them, for example (resolvedAt:timestamp - createdAt:timestamp).toTimeUnit('m'). The time of the event itself is $m.timestamp.
Schema description
Show schema description
| Full JSON path | Field data type | Field data example | Description |
|---|---|---|---|
schemaVersion | Number | 1 | Version of the event schema. |
metadata.trigger | Enum | "caseResolved" | What caused this snapshot: caseCreated, caseActivated, caseReactivated, caseAcknowledged, caseResolved, caseClosed, casePriorityChanged, caseAssigneeChanged, caseKpiBreached, caseUpdated, caseIdle, or caseArchived. |
metadata.change | Object | {"$type":"statusChanged","previousStatus":"ACTIVE","currentStatus":"RESOLVED"} | The change this event introduced, when there is one. $type is statusChanged, priorityChanged, assigneeChanged, or kpiBreached. |
caseId | String (UUID) | "76c411be-ff4d-4fb1-a987-5fce042deaaf" | Unique ID of the Case. Group and deduplicate on this field. |
caseNumber | Number | 1234 | Number shown in the Case ID in the UI. |
caseUrl | String | "https://..." | Link to the Case in Coralogix. |
title | String | "High CPU on payment-api" | Case title. |
description | String | "CPU above 80% for 10 minutes" | Case description. |
status | Enum | "RESOLVED" | PENDING_ACTIVATION, ACTIVE, ACKNOWLEDGED, RESOLVED, or CLOSED. |
priority | Enum | "P2" | Effective priority: P1 to P5. A string, so compare with == 'P1'. |
priorityDetails.system | Enum | "P3" | Priority set by Coralogix. |
priorityDetails.override | Enum / Null | "P2" | Priority set by a user, or null when not overridden. |
category | Enum | "AVAILABILITY" | AVAILABILITY or SECURITY. |
assignee.coralogixUser.id | String | "a1b2c3" | ID of the user the Case is assigned to. assignee is null when the Case is unassigned. |
createdAt | Number (epoch ns) | 1754817300000000000 | When the Case was created. |
firstTriggeredAt | Number (epoch ns) | 1754817240000000000 | When the underlying alert first fired. Can be earlier than createdAt. |
activatedAt | Number (epoch ns) / Null | 1754817330000000000 | When the Case moved from PENDING_ACTIVATION to ACTIVE. |
acknowledgedAt | Number (epoch ns) / Null | 1754817390000000000 | When the Case was acknowledged. |
acknowledgedBy | Object / Null | {"$type":"coralogixUser", ...} | Who acknowledged the Case. $type identifies the source, for example coralogixUser, slackUser, pagerDutyUser, serviceNowUser, or apiKey. |
assignedAt | Number (epoch ns) / Null | 1754817400000000000 | When the Case was assigned. |
updatedAt | Number (epoch ns) / Null | 1754904300000000000 | When the Case last changed. |
resolvedAt | Number (epoch ns) / Null | 1754903700000000000 | When the Case was resolved. |
closedAt | Number (epoch ns) / Null | 1754904300000000000 | When the Case was closed. |
closedBy | Object / Null | {"$type":"coralogixUser", ...} | Who closed the Case. Same shape as acknowledgedBy. |
lastReactivatedAt | Number (epoch ns) / Null | 1754910000000000000 | When a resolved Case was last reopened. |
resolutionCode | Object / Null | {"type":"custom","id":"...","label":"False positive"} | Resolution code chosen when the Case was resolved. type is system or custom. |
resolutionDetails | Object / Null | {"resolutionType":"USER","resolutionReason":"Rolled back the release", ...} | How the Case was resolved: resolutionType, resolutionReason, resolvedAt, and resolvedBy. |
kpiBreaches.breachedKpis | Array | [{"kpiType":"TIME_TO_ACKNOWLEDGE","breachStatus":"MITIGATED", ...}] | KPIs the Case breached. Each item has kpiType (TIME_TO_ACKNOWLEDGE, TIME_TO_RESOLVE, or TIME_TO_UPDATE), casePriority, thresholdNanos, breachedAt, mitigatedAt, and breachStatus (BREACHED or MITIGATED). |
indicators.alerts | Array | [{"alertDefinitionId":"...","title":"Test CPU Alert","state":"TRIGGERED", ...}] | Coralogix alerts that feed the Case. Key fields: alertDefinitionId, title, alertType, priority, groupingType, state (TRIGGERED, RESOLVED, or NO_DATA), triggeredAt, resolvedAt, permutations, and labels. |
indicators.prometheusAlerts | Array | [...] | Prometheus Alertmanager alerts that feed the Case. |
indicators.genericIndicators | Array | [...] | Other signals that feed the Case. |
permutations | Array of arrays | [[{"key":"coralogix.metadata.applicationName","value":"checkout","permutationIndex":0}]] | Label combinations that produced the Case, merged across all its alerts. Each inner array is one combination of key and value pairs. |
labels | Object | {"routing.team":["platform"],"routing.service":["checkout"]} | Case labels. Each value is an array. Routing labels use the routing. prefix, for example routing.team and routing.environment. |
labelsFlat | Array | [{"key":"routing.team","value":"platform"}] | The same labels as a flat list of key and value pairs, for queries that need to explode them. |
notificationEvidences | Array | [{"type":"slack","evidence":{"url":"https://..."}}] | Where the Case's notifications were delivered. type is the destination, for example slack, service_now, pagerduty, microsoft_teams, email, or generic_https. Some types include a link under evidence.url. |
impactedEntities | Array | [{"kind":"apmService","name":"payment-api","language":"go"}] | APM services and databases impacted by the Case. kind is apmService or apmDatabase. |
aiSummary | String / Null | "CPU saturation cleared after autoscaling." | AI-generated summary of the Case. |
relationship | Object | {"type":"CHILD","parentCaseId":"..."} | Present when the Case is linked to another Case. type is PARENT or CHILD. |
muteStatus | Object | {"UNMUTED":{}} | Whether the Case is muted. A muted Case records its muteSource (AUTOMATIC_LINKING or USER). |
Query examples
Set a time range of at least one hour for every query. Because open Cases are snapshotted again after an hour without changes, a shorter range can miss Cases that are open but quiet.
Count Cases resolved in the time range
Filtering on the caseResolved trigger skips idle snapshots, and distinct caseId counts a Case once even if it was reopened and resolved again:
source system/labs.cases.state_updates
| filter metadata.trigger == 'caseResolved'
| distinct caseId
| count
List the current state of every Case
Keep the most recent snapshot of each Case:
source system/labs.cases.state_updates
| dedupeby caseId orderby $m.timestamp desc
| choose caseNumber, title, status, priority, createdAt
Count the Cases that are currently active
source system/labs.cases.state_updates
| groupby caseId aggregate max_by($m.timestamp, status) as currentStatus
| filter currentStatus == 'ACTIVE'
| count
Track mean time to resolve per day
source system/labs.cases.state_updates
| filter metadata.trigger == 'caseResolved'
| create ttr from (resolvedAt:timestamp - createdAt:timestamp).toTimeUnit('m')
| create day from roundTime(resolvedAt:timestamp, 1d)
| groupby day aggregate avg(ttr) as mttr_minutes, percentile(0.95, ttr) as p95_minutes, count() as resolved
| orderby day asc
For mean time to acknowledge, filter on caseAcknowledged and use acknowledgedAt instead of resolvedAt. A Case can be acknowledged more than once, so add distinct caseId when you count Cases.
Compare response times per team
Reduce to one row per Case first, then split by the routing.team label:
source system/labs.cases.state_updates
| dedupeby caseId orderby $m.timestamp desc
| explode labels['routing.team'] into team original preserve
| create team from if(team != null, team, 'unassigned')
| create tta from (acknowledgedAt:timestamp - createdAt:timestamp).toTimeUnit('m')
| create ttr from if(status == 'RESOLVED' || status == 'CLOSED', (resolvedAt:timestamp - createdAt:timestamp).toTimeUnit('m'), null)
| groupby team aggregate avg(tta) as mtta_minutes, avg(ttr) as mttr_minutes, count() as cases
| orderby mttr_minutes desc
Find the alerts that open the most Cases
source system/labs.cases.state_updates
| dedupeby caseId orderby $m.timestamp desc
| explode indicators.alerts into alert
| groupby alert.alertDefinitionId as id, alert.title as title aggregate count() as cases
| orderby cases desc
Next steps
Monitor notification delivery success and failure rates with notification.deliveries.