+4Now that multi-stage queries are available as pre-GA, is it possible to create a dashboard KPI for MTTX using case_history data? An easy example would be a measure of the avg time cases were in Triage before going to Investigation.
My organization has been trying various yara-l approaches but to no avail. The examples in the multi-stage documentation do not seem to be related to this type of use case.
Thanks
Best answer by cmmartin_google
These are not fully tested, but give them a go:
AVG MTTD
stage stage_1 {
$case_id = case.response_platform_info.response_platform_id
cast.as_int(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds) > 0
match: $case_id
outcome:
$created_time = max(case.create_time.seconds)
$min_event_ts = min(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds)
$tags = array_distinct(case.tags.name)
$environment = array_distinct(case.environment)
$detection_rule_name = array_distinct(case.alerts.metadata.detection.rule_name)
order: $case_id desc
limit: 1000
}
stage stage_2 {
$case_id = $stage_1.case_id
$environment = $stage_1.environment
match: $case_id, $environment
outcome:
$mttd_per_case = sum($stage_1.created_time - $stage_1.min_event_ts)
}
$stage_1.case_id = $stage_2.case_id
outcome:
$average_mttd_seconds = math.round(avg($stage_2.mttd_per_case),2)
$average_mttd_minutes = math.round(avg($stage_2.mttd_per_case) / 60,2)
$average_mttd_hours = math.round(avg($stage_2.mttd_per_case) / 3600,2) MTTD BY CASE
stage stage_1 {
$case_id = case.response_platform_info.response_platform_id
cast.as_int(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds) > 0
match: $case_id
outcome:
$created_time = max(case.create_time.seconds)
$min_event_ts = min(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds)
$tags = array_distinct(case.tags.name)
$environment = array_distinct(case.environment)
$detection_rule_name = array_distinct(case.alerts.metadata.detection.rule_name)
order: $case_id desc
limit: 1000
}
stage stage_2 {
$case_id = $stage_1.case_id
match: $case_id
outcome:
$mttd_per_case = sum($stage_1.created_time - $stage_1.min_event_ts)
}
$stage_1.case_id = $stage_2.case_id
$case_id = $stage_1.case_id
$mttd_seconds = $stage_2.mttd_per_case
match: $case_id, $mttd_seconds
outcome:
$mttd_minutes = math.round(sum($stage_2.mttd_per_case / 60 ),2)
$mttd_hours = math.round(sum($stage_2.mttd_per_case / 3600 ),2)
order: $case_id descAVG MTTA
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
$case_history_stage = "Triage"
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "STAGE_CHANGE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
(
$case_history_stage != "Triage"
and $case_history_stage != ""
)
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
outcome:
$mtta_seconds = avg($case_changed-$case_created)
$mtta_minutes = math.round(avg($case_changed-$case_created) / 60,2)
$mtta_hours = math.round(avg($case_changed-$case_created) / 1440,2)
MTTA BY CASE
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
$case_history_stage = "Triage"
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "STAGE_CHANGE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
(
$case_history_stage != "Triage"
and $case_history_stage != ""
)
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_id = $stage_1_case_created.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
$case_history_stage = $stage_2_case_change.case_history_stage
match: $case_id, $case_created, $case_changed, $case_history_stage
outcome:
$mtta_seconds = sum($case_changed-$case_created)
$created = array_distinct(timestamp.get_timestamp($case_created))
$changed = array_distinct(timestamp.get_timestamp($case_changed))
order: $case_id desc
unselect: $case_created, $case_changed
AVG MTTR
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CLOSE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
outcome:
$mttr_seconds = math.round(avg($case_changed-$case_created),2)
$mttr_minutes = math.round(avg($case_changed-$case_created) / 60,2)
$mttr_hours = math.round(avg($case_changed-$case_created) / 1440,2)
MTTR BY CASE
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CLOSE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_id = $stage_1_case_created.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
match: $case_id, $case_created, $case_changed
outcome:
$mttr_seconds = sum($case_changed-$case_created)
$mttr_minutes = math.round(sum($case_changed-$case_created) / 60,2)
$mttr_hours = math.round(sum($case_changed-$case_created) / 1440,2)
$created = array_distinct(timestamp.get_timestamp($case_created))
$closed = array_distinct(timestamp.get_timestamp($case_changed))
order: $case_id desc
unselect: $case_created, $case_changed
As mentioned, not thoroughly tested, so things like changing sum to min maybe needed in some aggregates. You’d need change the stages or activity based on how your SOC works, and a future idea was to try and put this into a Data Table so its a variable.
We’re missing joins against case as the useful filtering capabilities are in that table in many cases, but its a start.
Note an official solution, but I wrote this MVP application to perform more detailed and customizable MTTx KPIs - https://medium.com/@thatsiemguy/from-prd-to-app-building-secops-mttx-reports-with-gemini-and-the-secops-sdk-91cbb8fd3a1c
Additional item not got around to yet, adding in case handling times.
+11These are not fully tested, but give them a go:
AVG MTTD
stage stage_1 {
$case_id = case.response_platform_info.response_platform_id
cast.as_int(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds) > 0
match: $case_id
outcome:
$created_time = max(case.create_time.seconds)
$min_event_ts = min(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds)
$tags = array_distinct(case.tags.name)
$environment = array_distinct(case.environment)
$detection_rule_name = array_distinct(case.alerts.metadata.detection.rule_name)
order: $case_id desc
limit: 1000
}
stage stage_2 {
$case_id = $stage_1.case_id
$environment = $stage_1.environment
match: $case_id, $environment
outcome:
$mttd_per_case = sum($stage_1.created_time - $stage_1.min_event_ts)
}
$stage_1.case_id = $stage_2.case_id
outcome:
$average_mttd_seconds = math.round(avg($stage_2.mttd_per_case),2)
$average_mttd_minutes = math.round(avg($stage_2.mttd_per_case) / 60,2)
$average_mttd_hours = math.round(avg($stage_2.mttd_per_case) / 3600,2) MTTD BY CASE
stage stage_1 {
$case_id = case.response_platform_info.response_platform_id
cast.as_int(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds) > 0
match: $case_id
outcome:
$created_time = max(case.create_time.seconds)
$min_event_ts = min(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds)
$tags = array_distinct(case.tags.name)
$environment = array_distinct(case.environment)
$detection_rule_name = array_distinct(case.alerts.metadata.detection.rule_name)
order: $case_id desc
limit: 1000
}
stage stage_2 {
$case_id = $stage_1.case_id
match: $case_id
outcome:
$mttd_per_case = sum($stage_1.created_time - $stage_1.min_event_ts)
}
$stage_1.case_id = $stage_2.case_id
$case_id = $stage_1.case_id
$mttd_seconds = $stage_2.mttd_per_case
match: $case_id, $mttd_seconds
outcome:
$mttd_minutes = math.round(sum($stage_2.mttd_per_case / 60 ),2)
$mttd_hours = math.round(sum($stage_2.mttd_per_case / 3600 ),2)
order: $case_id descAVG MTTA
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
$case_history_stage = "Triage"
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "STAGE_CHANGE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
(
$case_history_stage != "Triage"
and $case_history_stage != ""
)
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
outcome:
$mtta_seconds = avg($case_changed-$case_created)
$mtta_minutes = math.round(avg($case_changed-$case_created) / 60,2)
$mtta_hours = math.round(avg($case_changed-$case_created) / 1440,2)
MTTA BY CASE
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
$case_history_stage = "Triage"
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "STAGE_CHANGE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
(
$case_history_stage != "Triage"
and $case_history_stage != ""
)
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_id = $stage_1_case_created.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
$case_history_stage = $stage_2_case_change.case_history_stage
match: $case_id, $case_created, $case_changed, $case_history_stage
outcome:
$mtta_seconds = sum($case_changed-$case_created)
$created = array_distinct(timestamp.get_timestamp($case_created))
$changed = array_distinct(timestamp.get_timestamp($case_changed))
order: $case_id desc
unselect: $case_created, $case_changed
AVG MTTR
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CLOSE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
outcome:
$mttr_seconds = math.round(avg($case_changed-$case_created),2)
$mttr_minutes = math.round(avg($case_changed-$case_created) / 60,2)
$mttr_hours = math.round(avg($case_changed-$case_created) / 1440,2)
MTTR BY CASE
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CLOSE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_id = $stage_1_case_created.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
match: $case_id, $case_created, $case_changed
outcome:
$mttr_seconds = sum($case_changed-$case_created)
$mttr_minutes = math.round(sum($case_changed-$case_created) / 60,2)
$mttr_hours = math.round(sum($case_changed-$case_created) / 1440,2)
$created = array_distinct(timestamp.get_timestamp($case_created))
$closed = array_distinct(timestamp.get_timestamp($case_changed))
order: $case_id desc
unselect: $case_created, $case_changed
As mentioned, not thoroughly tested, so things like changing sum to min maybe needed in some aggregates. You’d need change the stages or activity based on how your SOC works, and a future idea was to try and put this into a Data Table so its a variable.
We’re missing joins against case as the useful filtering capabilities are in that table in many cases, but its a start.
Note an official solution, but I wrote this MVP application to perform more detailed and customizable MTTx KPIs - https://medium.com/@thatsiemguy/from-prd-to-app-building-secops-mttx-reports-with-gemini-and-the-secops-sdk-91cbb8fd3a1c
Additional item not got around to yet, adding in case handling times.
These are not fully tested, but give them a go:
AVG MTTD
stage stage_1 {
$case_id = case.response_platform_info.response_platform_id
cast.as_int(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds) > 0
match: $case_id
outcome:
$created_time = max(case.create_time.seconds)
$min_event_ts = min(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds)
$tags = array_distinct(case.tags.name)
$environment = array_distinct(case.environment)
$detection_rule_name = array_distinct(case.alerts.metadata.detection.rule_name)
order: $case_id desc
limit: 1000
}
stage stage_2 {
$case_id = $stage_1.case_id
$environment = $stage_1.environment
match: $case_id, $environment
outcome:
$mttd_per_case = sum($stage_1.created_time - $stage_1.min_event_ts)
}
$stage_1.case_id = $stage_2.case_id
outcome:
$average_mttd_seconds = math.round(avg($stage_2.mttd_per_case),2)
$average_mttd_minutes = math.round(avg($stage_2.mttd_per_case) / 60,2)
$average_mttd_hours = math.round(avg($stage_2.mttd_per_case) / 3600,2) MTTD BY CASE
stage stage_1 {
$case_id = case.response_platform_info.response_platform_id
cast.as_int(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds) > 0
match: $case_id
outcome:
$created_time = max(case.create_time.seconds)
$min_event_ts = min(case.alerts.metadata.collection_elements.references.event.metadata.event_timestamp.seconds)
$tags = array_distinct(case.tags.name)
$environment = array_distinct(case.environment)
$detection_rule_name = array_distinct(case.alerts.metadata.detection.rule_name)
order: $case_id desc
limit: 1000
}
stage stage_2 {
$case_id = $stage_1.case_id
match: $case_id
outcome:
$mttd_per_case = sum($stage_1.created_time - $stage_1.min_event_ts)
}
$stage_1.case_id = $stage_2.case_id
$case_id = $stage_1.case_id
$mttd_seconds = $stage_2.mttd_per_case
match: $case_id, $mttd_seconds
outcome:
$mttd_minutes = math.round(sum($stage_2.mttd_per_case / 60 ),2)
$mttd_hours = math.round(sum($stage_2.mttd_per_case / 3600 ),2)
order: $case_id descAVG MTTA
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
$case_history_stage = "Triage"
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "STAGE_CHANGE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
(
$case_history_stage != "Triage"
and $case_history_stage != ""
)
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
outcome:
$mtta_seconds = avg($case_changed-$case_created)
$mtta_minutes = math.round(avg($case_changed-$case_created) / 60,2)
$mtta_hours = math.round(avg($case_changed-$case_created) / 1440,2)
MTTA BY CASE
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
$case_history_stage = "Triage"
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
//$case_history_case_activity = "STAGE_CHANGE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
(
$case_history_stage != "Triage"
and $case_history_stage != ""
)
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_id = $stage_1_case_created.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
$case_history_stage = $stage_2_case_change.case_history_stage
match: $case_id, $case_created, $case_changed, $case_history_stage
outcome:
$mtta_seconds = sum($case_changed-$case_created)
$created = array_distinct(timestamp.get_timestamp($case_created))
$changed = array_distinct(timestamp.get_timestamp($case_changed))
order: $case_id desc
unselect: $case_created, $case_changed
AVG MTTR
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CLOSE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
outcome:
$mttr_seconds = math.round(avg($case_changed-$case_created),2)
$mttr_minutes = math.round(avg($case_changed-$case_created) / 60,2)
$mttr_hours = math.round(avg($case_changed-$case_created) / 1440,2)
MTTR BY CASE
stage stage_1_case_created {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CREATE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
match: $case_history_case_id
outcome:
$case_start_time = min($case_history_case_event_time)
limit: 5000
}
stage stage_2_case_change {
$case_history_case_id = case_history.case_response_platform_info.case_id
$case_history_case_activity = case_history.case_activity
$case_history_case_activity = "CLOSE_CASE"
$case_history_case_event_time = case_history.event_time.seconds
$case_history_stage = case_history.stage
match: $case_history_case_id, $case_history_stage
outcome:
$case_first_change = min($case_history_case_event_time)
limit: 5000
}
$stage_1_case_created.case_history_case_id = $stage_2_case_change.case_history_case_id
$case_id = $stage_1_case_created.case_history_case_id
$case_created = $stage_1_case_created.case_start_time
$case_changed = $stage_2_case_change.case_first_change
match: $case_id, $case_created, $case_changed
outcome:
$mttr_seconds = sum($case_changed-$case_created)
$mttr_minutes = math.round(sum($case_changed-$case_created) / 60,2)
$mttr_hours = math.round(sum($case_changed-$case_created) / 1440,2)
$created = array_distinct(timestamp.get_timestamp($case_created))
$closed = array_distinct(timestamp.get_timestamp($case_changed))
order: $case_id desc
unselect: $case_created, $case_changed
As mentioned, not thoroughly tested, so things like changing sum to min maybe needed in some aggregates. You’d need change the stages or activity based on how your SOC works, and a future idea was to try and put this into a Data Table so its a variable.
We’re missing joins against case as the useful filtering capabilities are in that table in many cases, but its a start.
Note an official solution, but I wrote this MVP application to perform more detailed and customizable MTTx KPIs - https://medium.com/@thatsiemguy/from-prd-to-app-building-secops-mttx-reports-with-gemini-and-the-secops-sdk-91cbb8fd3a1c
Additional item not got around to yet, adding in case handling times.
This has been very helpful. I’ve been banging my head against the wall for a few days on this. Your examples work and have definitely moved the needle for us.
I am now having an issue with the time window. I get the error below whenever I set the time in the dashboard greater than 14 days. We want to have this report for 30 days.
An error occurred while executing Chart query.
generic::invalid_argument: invalid Time Window: invalid argument
+11I think in the preview of multi-stage queries they are limited to 14 days I’m afraid, but this is pre-GA, so I would expect that will increase:
`Joins with match window: 30 days, with match window time of up to 14 days`
I think in the preview of multi-stage queries they are limited to 14 days I’m afraid, but this is pre-GA, so I would expect that will increase:
`Joins with match window: 30 days, with match window time of up to 14 days`
Ok, thanks. Do we know when this will change?
Also, I am trying to break the query down to include the case priority. But if I put “case_history.priority = "PRIORITY_HIGH"” in each stage, the calculations turn negative. We have various SLAs tied to the severity of the case.
Actually, got it figured out. The CREATE_CASE doesn’t have the priority. So if I just add the priority to the second stage, it works
+4This was a huge help
One follow up question: We are calculating an MTTR metric but *if* the case was set to a “paused case stage” for a duration, we want to subtract that duration in the metric. It seems like this should be 2 additional yara-l stages: one for the pause event and one for the unpause event. But not all cases pause, so joining these stages in the root seems to ruin the logic.
Do you think it’s possible with the current release to construct this metric with the optional stages?
+11This would require an outer join (retrieves all records from both stages, including matching and non-matching records), which I think is already implemented, albeit not explicitly documented yet.
This would require an outer join (retrieves all records from both stages, including matching and non-matching records), which I think is already implemented, albeit not explicitly documented yet.
How would we perform this with an outer join? The problem we have been running into is that the “if” condition. Probably half of cases aren’t “paused” while the other half are not. Right now we have 3 stages in the code: case creation, assignee change (an analyst working a case), and close case. Some cases may be “paused” at some point during the case lifecycle. Is there a code example I can use the achieve this functionality? I would want to subtract the “paused” time from the entire duration of the case time.
+3Hi
Thanks for the resources, they are always very helpful!
I am trying to implement the above dashboards on our mssp environment but the information of alerts associated with cases does not get populated.
Just looking at the first stage of the query in “MTTD BY CASE” I get 0 whatever I try. Same goes for the field “detection_rule_name”. It seems to me that any information associated with the case regarding detection and alerts is missing.
Any idea on what could be the issue here?
Thanks
+9
+4
+4How can we accurately calculate MTTD & MTTR breakdown of Incidents for each analyst in Google SecOps so that the results align with expected SOC ranges (minutes / fractions of hours)?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
