Curated dashboard queries: SOAR case history

Supported in:

This document is for Security Operations Center (SOC) managers and analysts who want to monitor threat landscapes and system health using curated dashboards— predefined dashboards designed for visibility across various security use cases. This document provides a collection of curated dashboards and their underlying queries for the SOAR case history source type.

You can use these queries in the query editor or as a baseline for custom widgets. For information on how to create and manage dashboards, see Manage dashboards.

Dashboard name Description Chart name Query
CIS Controls Compliance Overview Provides a central view of CIS compliance metrics, such as asset accuracy and backup reliability. use these insights to strengthen security governance and track remediation progress. Mean Time to Resolve (MTTR) 
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.abs(math.round(avg($stage1.TTC)/60))
Case & Incident Analytics (SOAR) Provides a centralized view of case management and incident response, enabling teams to resolve threats faster and continuously improve operational efficiency. Reopened Cases 
case_history.case_activity = "REOPEN_CASE"

outcome:
    $Count = count(case_history.name)
SOC Workflow Monitoring (SOAR) Provides centralized tracking of alert detection, incident handling, and case management metrics. Use these insights to optimize resources, improve response efficiency, and maintain adherence to SLAs. MTTA (Time to Acknowledge) - Minutes 
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)))
SOC Workflow Monitoring (SOAR) Provides centralized tracking of alert detection, incident handling, and case management metrics. Use these insights to optimize resources, improve response efficiency, and maintain adherence to SLAs. MTTR (Time to Resolution in mins) - Last 7 Days 
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))
SOC Workflow Monitoring (SOAR) Provides centralized tracking of alert detection, incident handling, and case management metrics. Use these insights to optimize resources, improve response efficiency, and maintain adherence to SLAs. MTTI (Time to Investigate in mins) - Last 7 Days 
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))
    $case_assign_time = min(if(case_history.case_activity = "ASSIGNEE_CHANGE", case_history.event_time.seconds, 9999999999999999))
    $status = array_distinct(case_history.case_activity)
    $TTI = $case_close_time - $case_assign_time
condition:
    arrays.contains($status, "CREATE_CASE") and arrays.contains($status, "CLOSE_CASE")
    AND arrays.contains($status, "ASSIGNEE_CHANGE") 
    }
outcome:
    $case_count = count($stage1.case_id)

    $MTTI = (math.round(avg($stage1.TTI)/60))
SOC Workflow Monitoring (SOAR) Provides centralized tracking of alert detection, incident handling, and case management metrics. Use these insights to optimize resources, improve response efficiency, and maintain adherence to SLAs. Total Cases Exceeding SLA 
case_history.case_activity = "EXCEEDED_SLA"
case_history.sla_type = "CASE_SLA"

outcome:
   $Count = count(case_history.case_activity)
Security Management Overview Provides centralized visibility into response performance and operational trends. It helps security teams track progress and make informed decisions to strengthen the security posture. Handling Time by Case Stage - Last 7 Days 
case_history.assignee.soc_roles != ""

$Case_ID = case_history.case_response_platform_info.case_id
$Case_Stage = case_history.stage

match:
    $Case_Stage

outcome:
    $Handling_Time_Minutes = (max(case_history.event_time.seconds) - min(case_history.event_time.seconds)) / 60
Security Management Overview Provides centralized visibility into response performance and operational trends. It helps security teams track progress and make informed decisions to strengthen the security posture. MTTA (Time to Acknowledge in mins) Last 7 Days 
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)))
Security Management Overview Provides centralized visibility into response performance and operational trends. It helps security teams track progress and make informed decisions to strengthen the security posture. Case Creation vs. Closure Rate - Last 7 Days 
case_history.case_activity = "CREATE_CASE" 
or case_history.case_activity = "CLOSE_CASE"

$Case_Activity = case_history.case_activity
$Date = timestamp.get_date(case_history.event_time.seconds) 

match: 
    $Date, $Case_Activity

outcome: 
    $Count = count_distinct(case_history.case_response_platform_info.case_id)

order:
    $Date desc
Security Management Overview Provides centralized visibility into response performance and operational trends. It helps security teams track progress and make informed decisions to strengthen the security posture. MTTR (Time to Resolution in mins) - Last 7 Days 
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))
Security Management Overview Provides centralized visibility into response performance and operational trends. It helps security teams track progress and make informed decisions to strengthen the security posture. MTTI (Time to Investigate in mins ) - Last 7 Days 
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))
    $case_assign_time = min(if(case_history.case_activity = "ASSIGNEE_CHANGE", case_history.event_time.seconds, 9999999999999999))
    $status = array_distinct(case_history.case_activity)
    $TTI = $case_close_time - $case_assign_time
condition:
    arrays.contains($status, "CREATE_CASE") and arrays.contains($status, "CLOSE_CASE")
    AND arrays.contains($status, "ASSIGNEE_CHANGE") 
    }
outcome:
    $case_count = count($stage1.case_id)

    $MTTI = (math.round(avg($stage1.TTI)/60))

Need more help? Get answers from Community members and Google SecOps professionals.