+2Now 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.
I think thats maybe possible
+10These 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
+10I 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
+2This 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?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.