Hi @Sai-08 ,
You can calculate the average time difference between the "In Progress" status and the "Closed" or "Resolved" status using the stats command.
Here is an example query using makeresults for sample data. Replace the makeresults part with your base search.
| makeresults count=4
| streamstats count as alert_id
| eval _time = case(
alert_id=1, now() - 3600,
alert_id=2, now() - 7200,
alert_id=3, now() - 10800,
alert_id=4, now() - 14400
)
| eval status_label="New"
| append [| makeresults count=4 | streamstats count as alert_id | eval _time = case(alert_id=1, now() - 3000, alert_id=2, now() - 6000, alert_id=3, now() - 9000, alert_id=4, now() - 12000) | eval status_label="In Progress"]
| append [| makeresults count=4 | streamstats count as alert_id | eval _time = case(alert_id=1, now() - 600, alert_id=2, now() - 1200, alert_id=3, now() - 1800, alert_id=4, now() - 2400) | eval status_label=if(alert_id%2=0, "Closed", "Resolved")]
| sort 0 _time
``` Replace above makeresults block with your base search: index= sourcetype= status_label IN ("In Progress", "Closed", "Resolved")```
``` Ensure you have a unique identifier for each alert (e.g., alert_id)```
``` Filter for relevant status transitions```
| where status_label IN ("In Progress", "Closed", "Resolved")
``` Capture the timestamp for "In Progress" and "Closed/Resolved" statuses```
| eval in_progress_time = if(status_label="In Progress", _time, null())
| eval closed_resolved_time = if(status_label="Closed" OR status_label="Resolved", _time, null())
``` Group by alert_id and find the earliest "In Progress" time and latest "Closed/Resolved" time```
| stats earliest(in_progress_time) as start_time latest(closed_resolved_time) as end_time by alert_id
``` Filter out alerts that didn't complete the transition or where times are illogical```
| where isnotnull(start_time) AND isnotnull(end_time) AND end_time > start_time
``` Calculate the duration for each alert```
| eval duration_seconds = end_time - start_time
``` Calculate the average duration (MTTM) across all alerts```
| stats avg(duration_seconds) as mttm_seconds
``` Optional: Format the result for readability```
| eval mttm_readable = tostring(mttm_seconds, "duration")
| fields mttm_seconds mttm_readable
How it works:
- The search first filters events for the relevant statuses ("In Progress", "Closed", "Resolved"). You need a unique field (alert_id in the example) to identify each alert instance.
- It uses eval to create fields holding the timestamp (_time) only when the event matches the specific status ("In Progress" or "Closed"/"Resolved").
- stats groups the events by alert_id and finds the earliest time the alert was "In Progress" (start_time) and the latest time it was "Closed" or "Resolved" (end_time).
- It filters out any alerts that haven't reached a final state or have inconsistent timestamps.
- The duration_secondsis calculated for each alert.
- Finally, stats calculates the mean time across all valid alert durations.
- The result is optionally formatted into a human-readable duration string (e.g., HH:MM:SS).
🌟 Did this answer help you? If so, please consider:
- Adding kudos to show it was useful
- Marking it as the solution if it resolved your issue
- Commenting if you need any clarification
Your feedback encourages the volunteers in this community to continue contributing
