How to calculate the time between the notable event state changes?

[ajaynyay]

I am trying to figure out a way to calculate the time for:

1. Time taken for a reviewer to assign the notable ticket from the creation time.
2. Time taken for the notable in progress till close.

notable|search NOT suppression

|eval _time=strftime(_time,"%Y/%m/%d %T")
|eval review_time=strftime(review_time,"%Y/%m/%d %T")
|eval assign_time = case(isnotnull(owner), _time) | eval close_time = case(status=5, review_time)
|stats min(_time) as notable_time min(assign_time) as assign_time min(close_time) as close_time by AlertTitle,owner

This is giving me notable created time and closed time, but not the state change time.

[ajaynyay]

Thank you for that.

The issue I see is that every time the notable incident state changes "review_time" is the only field that changes time. So, if I want to calculate the time from creation-->assignment and assignment-->closure its not giving me correct results.

For example, if I assign the notable today, the review_time field changes time, but if I close the same notable after sometime, it again updates the review_time field only. So, there is no good way of calculating time taken for old notables which are already closed or still open when the state changes later, unless I am missing something.

[to4kawa]

use range()

reference: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Stats

[to4kawa]

|eval _time=strftime(_time,"%Y/%m/%d %T")
This is wrong.
If you change epoch time to strings ,you can't aggregate times. Keep them stay.

 | eval assign_time = case(isnotnull(owner), _time) | eval close_time = case(status=5, review_time)
 | stats min(_time) as notable_time min(assign_time) as assign_time min(close_time) as close_time by AlertTitle,owner
 | eval state_change_time=your calculation
 | convert timeformat="%Y/%m/%d %T" ctime(notable_time) ctime(assign_time) ctime(close_time)