I'm trying to calculate mttr and mtta per environment; this metric is important for each existing environment. Does anyone know how I can do this in the case_history table?
I notice that the `cases` table contains the `cases.environment` field. Is it possible to perform this calculation using that table?
MMTR:
stage stage1{
$case_id = case_history.case_response_platform_info.case_id
match:
$case_id
outcome:
$case_close_time = max(if(case_history.case_activity = "CLOSE_CASE", case_history.event_time.seconds, 0))
$status = array_distinct(case_history.case_activity)
$TTC = $case_close_time - min(case_history.event_time.seconds)
condition:
arrays.contains($status, "CREATE_CASE") and arrays.contains($status, "CLOSE_CASE")
}
outcome:
$case_count = count($stage1.case_id)
$MTTC = (math.round(avg($stage1.TTC)/60))
MTTA:
stage stage1{
$case_id = case_history.case_response_platform_info.case_id
match:
$case_id
outcome:
$case_assign_time = min(if(case_history.case_activity = "ASSIGNEE_CHANGE", case_history.event_time.seconds, 9999999999999999))
$status = array_distinct(case_history.case_activity)
$TTA = $case_assign_time - min(case_history.event_time.seconds)
condition:
arrays.contains($status, "CREATE_CASE") and arrays.contains($status, "ASSIGNEE_CHANGE")
}
outcome:
$case_count = count($stage1.case_id)
$MTTA = (math.round((avg($stage1.TTA)/60)))
