Skip to main content

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.

Note

labs.cases.state_updates is a Labs (experimental) dataset. Its fields can change as the dataset evolves.

Tip

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 pathField data typeField data exampleDescription
schemaVersionNumber1Version of the event schema.
metadata.triggerEnum"caseResolved"What caused this snapshot: caseCreated, caseActivated, caseReactivated, caseAcknowledged, caseResolved, caseClosed, casePriorityChanged, caseAssigneeChanged, caseKpiBreached, caseUpdated, caseIdle, or caseArchived.
metadata.changeObject{"$type":"statusChanged","previousStatus":"ACTIVE","currentStatus":"RESOLVED"}The change this event introduced, when there is one. $type is statusChanged, priorityChanged, assigneeChanged, or kpiBreached.
caseIdString (UUID)"76c411be-ff4d-4fb1-a987-5fce042deaaf"Unique ID of the Case. Group and deduplicate on this field.
caseNumberNumber1234Number shown in the Case ID in the UI.
caseUrlString"https://..."Link to the Case in Coralogix.
titleString"High CPU on payment-api"Case title.
descriptionString"CPU above 80% for 10 minutes"Case description.
statusEnum"RESOLVED"PENDING_ACTIVATION, ACTIVE, ACKNOWLEDGED, RESOLVED, or CLOSED.
priorityEnum"P2"Effective priority: P1 to P5. A string, so compare with == 'P1'.
priorityDetails.systemEnum"P3"Priority set by Coralogix.
priorityDetails.overrideEnum / Null"P2"Priority set by a user, or null when not overridden.
categoryEnum"AVAILABILITY"AVAILABILITY or SECURITY.
assignee.coralogixUser.idString"a1b2c3"ID of the user the Case is assigned to. assignee is null when the Case is unassigned.
createdAtNumber (epoch ns)1754817300000000000When the Case was created.
firstTriggeredAtNumber (epoch ns)1754817240000000000When the underlying alert first fired. Can be earlier than createdAt.
activatedAtNumber (epoch ns) / Null1754817330000000000When the Case moved from PENDING_ACTIVATION to ACTIVE.
acknowledgedAtNumber (epoch ns) / Null1754817390000000000When the Case was acknowledged.
acknowledgedByObject / Null{"$type":"coralogixUser", ...}Who acknowledged the Case. $type identifies the source, for example coralogixUser, slackUser, pagerDutyUser, serviceNowUser, or apiKey.
assignedAtNumber (epoch ns) / Null1754817400000000000When the Case was assigned.
updatedAtNumber (epoch ns) / Null1754904300000000000When the Case last changed.
resolvedAtNumber (epoch ns) / Null1754903700000000000When the Case was resolved.
closedAtNumber (epoch ns) / Null1754904300000000000When the Case was closed.
closedByObject / Null{"$type":"coralogixUser", ...}Who closed the Case. Same shape as acknowledgedBy.
lastReactivatedAtNumber (epoch ns) / Null1754910000000000000When a resolved Case was last reopened.
resolutionCodeObject / Null{"type":"custom","id":"...","label":"False positive"}Resolution code chosen when the Case was resolved. type is system or custom.
resolutionDetailsObject / Null{"resolutionType":"USER","resolutionReason":"Rolled back the release", ...}How the Case was resolved: resolutionType, resolutionReason, resolvedAt, and resolvedBy.
kpiBreaches.breachedKpisArray[{"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.alertsArray[{"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.prometheusAlertsArray[...]Prometheus Alertmanager alerts that feed the Case.
indicators.genericIndicatorsArray[...]Other signals that feed the Case.
permutationsArray 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.
labelsObject{"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.
labelsFlatArray[{"key":"routing.team","value":"platform"}]The same labels as a flat list of key and value pairs, for queries that need to explode them.
notificationEvidencesArray[{"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.
impactedEntitiesArray[{"kind":"apmService","name":"payment-api","language":"go"}]APM services and databases impacted by the Case. kind is apmService or apmDatabase.
aiSummaryString / Null"CPU saturation cleared after autoscaling."AI-generated summary of the Case.
relationshipObject{"type":"CHILD","parentCaseId":"..."}Present when the Case is linked to another Case. type is PARENT or CHILD.
muteStatusObject{"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.

Last updated on