🚀 Unlock Advanced SOC Metrics: Joining case and case_history in Native Dashboards

Hello SecOps Community!

If you are building custom Native Dashboards to track SOC performance metrics like Mean Time to Resolve (MTTR), analyst workloads, or SLA compliance, you know that the data you need often lives in two different places: the current case state (case) and the audit log of actions taken on that case (case_history).

I want to provide some quick guide on how to seamlessly join these two data sources within your dashboard queries to unlock deeper insights into your case lifecycles.

The Secret Sauce: Aliasing and Shared Variables

In Native Dashboard queries, you do not need to use YARA-L rule or events blocks to join data. Instead, you establish the relationship by assigning an alias to each data source and mapping their unique identifiers to a single, shared placeholder variable.

To successfully join case and case_history, focus on this core syntax:

Code snippet

// Assign aliases ($h for history, $c for case) and map to a shared variable
$h.case_history.case_response_platform_info.case_id = $case_id
$c.case.response_platform_info.response_platform_id = $case_id

By assigning both the history log's foreign key and the case's primary key to $case_id, the query engine automatically correlates the records!

Example 1: Tracking Historical Actions per Case

Here is a complete, working example of a standard query. This correlates the data to count the total number of historical actions that have occurred on your high-priority cases:

Code snippet

// 1. Establish the Join using Aliases
$h.case_history.case_response_platform_info.case_id = $case_id
$c.case.response_platform_info.response_platform_id = $case_id

// 2. Apply Filters to the Case Data
$c.case.priority = "PRIORITY_HIGH"

// 3. Group the correlated data by the Case ID
match:
  $case_id

// 4. Calculate metrics to visualize on your dashboard
outcome:
  $case_name = array_distinct($c.case.display_name)
  $total_historical_actions = count($h.case_history.case_activity)

// 5. Order the results
order:
  $total_historical_actions desc

🔥 Advanced Use Case: Computing MTTR (Mean Time to Resolve)

For more complex metrics like MTTR or Mean Time to Close (MTTC), you can leverage a multistage query. This allows you to calculate the duration for each individual case in the first stage, and then average those durations globally in the final outcome block.

Here is an advanced query leveraging stage to compute the MTTC (in minutes) across cases specifically within the "Default Environment":

Code snippet

stage stage1 {
  // 1. Establish the Join
  $h.case_history.case_response_platform_info.case_id = $case_id
  $c.case.response_platform_info.response_platform_id = $case_id

  // 2. Filter by specific environment
  $c.case.environment = "Default Environment"

  // 3. Group by Case ID to process per case
  match:
    $case_id

  // 4. Calculate the Time to Close (TTC) for each case individually
  outcome:
    $case_close_time = max(if($h.case_history.case_activity = "CLOSE_CASE", $h.case_history.event_time.seconds, 0))
    $status = array_distinct($h.case_history.case_activity)

    // Subtract the very first event time (creation) from the close time
    $TTC = $case_close_time - min($h.case_history.event_time.seconds)

  // 5. Filter to ensure the case has a complete lifecycle
  condition:
    // Only aggregated checks belong here
    arrays.contains($status, "CREATE_CASE") and 
    arrays.contains($status, "CLOSE_CASE")
}

// 6. Global Aggregation: Calculate the Mean (Average) across all processed cases
outcome:
  $case_count = count($stage1.case_id)
  $MTTC = (math.round(avg($stage1.TTC) / 60))