---
title: Query case data with ES|QL
description: Analyze case data directly with ES|QL queries against the case analytics indices.
url: https://www.elastic.co/elastic/docs-builder/docs/3739/explore-analyze/cases/query-case-data-esql
products:
  - Elastic Cloud Enterprise
  - Elastic Cloud Hosted
  - Elastic Cloud Serverless
  - Elastic Cloud on Kubernetes
  - Elastic Stack
  - Kibana
applies_to:
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Preview from 9.2 to 9.4
---

# Query case data with ES|QL
ES|QL queries the analytics indices directly, which is ideal for calculating metrics like case volume, closure rates, and mean time to respond (MTTR). This page provides sample queries you can adapt in [ES|QL in Discover](https://www.elastic.co/elastic/docs-builder/docs/3739/explore-analyze/discover/try-esql).
<applies-switch>
  <applies-item title="{ "stack": "ga 9.5", "serverless": "ga" }" applies-to="Elastic Cloud Serverless: Generally available, Elastic Stack: Planned">
    Query the shared `.cases`, `.cases-activity`, and `.cases-attachments` indices, and reference fields with the singular `case.*` prefix, such as `case.status`. For field units and other details, refer to the [field reference](https://www.elastic.co/elastic/docs-builder/docs/3739/explore-analyze/cases/case-analytics-fields). Here are some sample queries to get you started:
    - Count open cases by severity:
      ```esql
      FROM .cases
      | WHERE case.status == "open"
      | STATS case_count = COUNT(*) BY case.severity
      | SORT case_count DESC
      ```
    - Find mean time to resolve by severity:
      ```esql
      FROM .cases
      | WHERE case.status == "closed" AND case.time_to_resolve IS NOT NULL
      | STATS mttr_seconds = AVG(case.time_to_resolve) BY case.severity
      | EVAL mttr_hours = ROUND(mttr_seconds / 3600, 1)
      | SORT mttr_seconds DESC
      ```
    - Count open cases by solution (`owner`):
      ```esql
      FROM .cases
      | WHERE case.status == "open"
      | STATS case_count = COUNT(*) BY owner
      | SORT case_count DESC
      ```
    - Join activity to current case attributes:
      ```esql
      FROM .cases-activity
      | STATS actions = COUNT(*) BY case.id
      | LOOKUP JOIN .cases ON case.id
      | KEEP case.id, case.title, case.status, actions
      ```
  </applies-item>

  <applies-item title="stack: preview 9.2-9.4" applies-to="Elastic Stack: Preview from 9.2 to 9.4">
    Each index covers one solution in one space, so use the [index name](https://www.elastic.co/elastic/docs-builder/docs/3739/explore-analyze/cases/case-analytics-indices) for the data you want, such as `.internal.cases.observability-default`. Reference fields without the `case.*` prefix, such as `status` instead of `case.status`. These examples use the `default` space. Here are some sample queries to get you started:
    - Count cases by status. Change the index name to target a different solution or space:
      ```esql
      FROM .internal.cases.observability-default
      | STATS count = COUNT(*) BY status
      ```
    - Find open Security cases, with the most recent first:
      ```esql
      FROM .internal.cases.securitysolution-default
      | WHERE status == "open"
      | SORT created_at DESC
      ```
    - Find the average time to close Security cases:
      ```esql
      FROM .internal.cases.securitysolution-default
      | STATS average_time_to_close = AVG(time_to_resolve)
      ```
  </applies-item>
</applies-switch>