topic Re: Duration between two events by day based on three or more fields in Splunk Search

Duration between two events by day based on three or more fields?

nu_learner — Mon, 13 Mar 2023 13:26:52 GMT

Hello - I need to calculate the average duration between two status types for a user type in a location in a region. I have attached the sample data. For example, I need to calculate the average duration a given user type remains in "inactive" status. These user types are segregated into different locations in different regions.

Sample data:

The result is want is like this (time is in seconds):

aaa bbb ccc
Day 1 30 40 10
Day 2 10 20 30
Day 3 20 10 10

and so on
I would appreciate any help.

Re: Duration between two events by day based on three or more fields

ITWhisperer — Fri, 10 Mar 2023 08:20:43 GMT

Your sample data show multiple inactive status for some users. Is this representative or will you only get one event when the state changes?

Re: Duration between two events by day based on three or more fields

yuanliu — Sat, 11 Mar 2023 04:59:29 GMT

This is nearly one for one the same as https://community.splunk.com/t5/Splunk-Search/Calculate-total-time-for-a-specific-event-over-a-range-of-time/m-p/634096#M220230; the only difference is that you have consecutive time boundaries you want to calculate for while the other use case calculates for a single start-to-end interval.

| eval day = _time | bin span=1d@d day ``` start of day of event ``` | addinfo ``` search end time is info_max_time ``` | eval duration = if(closed_txn == 1, duration, if(status == "active", _time - max(day, info_min_time), min(info_max_time, relative_time(day, "+1d")) - _time)) | stats sum(duration) as inactive by day user_type region_id location_id