Query case data with ES|QL

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. Here are some sample queries to get you started:

• Count open cases by severity:

  FROM .cases
  | WHERE case.status == "open"
  | STATS case_count = COUNT(*) BY case.severity
  | SORT case_count DESC
  

• Find mean time to resolve by severity:

  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):

  FROM .cases
  | WHERE case.status == "open"
  | STATS case_count = COUNT(*) BY owner
  | SORT case_count DESC
  

• Join activity to current case attributes:

  FROM .cases-activity
  | STATS actions = COUNT(*) BY case.id
  | LOOKUP JOIN .cases ON case.id
  | KEEP case.id, case.title, case.status, actions
  

Each index covers one solution in one space, so use the index name 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:

  FROM .internal.cases.observability-default
  | STATS count = COUNT(*) BY status
  

• Find open Security cases, with the most recent first:

  FROM .internal.cases.securitysolution-default
  | WHERE status == "open"
  | SORT created_at DESC
  

• Find the average time to close Security cases:

  FROM .internal.cases.securitysolution-default
  | STATS average_time_to_close = AVG(time_to_resolve)