topic Re: calculate duration by skiping overlapping time in Splunk Search

calculate duration by skiping overlapping time

anantdeshpande — Tue, 15 Jan 2019 08:46:41 GMT

Hi,
Please help me to calculate service availability of the system.

Method Time of down Time of up

A 01/01/2019 15:00:00 01/01/2019 15:05:00

B 01/01/2019 15:08:00 01/01/2019 15:11:00
C 01/01/2019 15:09:00 01/01/2019 15:12:00

D 01/01/2019 15:09:00 01/01/2019 15:10:00
E 01/01/2019 15:15:00 01/01/2019 15:20:00

Total Duration  20 Min  
Down duration   17 min  (currently)
Down duration      14 min  (required)

If any method is down, the complete service is down. Total duration will be the sum of downtime duration between each method. But there are overlapping time of methods going down.
The Method C goes down before Method B comes up and method C comes up after B comes up. So downtime duration= up time of C - down time of B. Also Method D goes down and comes up between method B & C. So should skip that duration calculation.

The normal stats command calculates downtime duration as 17 min where as I want only 14 min. Please help to build the query.

Re: calculate duration by skiping overlapping time

damann — Tue, 29 Sep 2020 22:46:06 GMT

Try this:

 your_base_search
| rename "Time of down" as time_down, "Time of up" as time_up 
| sort time_down
| eval pot_down=strptime(time_down, "%d/%m/%Y %H:%M:%S")
| eval pot_up=strptime(time_up, "%d/%m/%Y %H:%M:%S")
| eval duration=pot_up-pot_down
| streamstats window=2 first(pot_*) as prev_*
| eval duration=case(prev_up==pot_up,pot_up-pot_down,prev_up>pot_down,pot_up-prev_up,1==1,duration)
|streamstats sum(duration) as overlap_free_duration
| eval overlap_free_duration=toString(overlap_free_duration,"duration")
| table Method,time_down,time_up,overlap_free_duration

In the field overlap_free_duration i get a result of 14 minutes.
If you want a single line result you can append the following two lines:

| stats sum(duration) as overlap_free_duration
| eval overlap_free_duration=toString(overlap_free_duration,"duration")

Re: calculate duration by skiping overlapping time

anantdeshpande — Tue, 29 Sep 2020 22:46:17 GMT

Hi damann,
Thanks for the reply.
Your query works perfect on sample data.
But Somehow "| streamstats window=2 first(pot_) as prev_" is not giving the result as expected on actual data.
I tested it multiple scenarios and either miss boundry events or any middle event.

What I want is.....
If "Time of down" is less than any previous (not current) "Time of up" then consider the "time of down" of that event. Also if "Time of up" is less than any previous "Time of up" then consider the "Time of up" of that event.
Below is from actual data. I want time_of_down (13:32:27) and time_of_up(13:24:04) from method B to E.

Method time_of_down time_of_up
A 10/01/2019 11:25:00 10/01/2019 11:25:32
B 10/01/2019 13:32:27 10/01/2019 13:33:50
C 10/01/2019 13:32:30 10/01/2019 13:33:42
D 10/01/2019 13:32:31 10/01/2019 13:33:46
E 10/01/2019 13:32:36 10/01/2019 13:34:04

Re: calculate duration by skiping overlapping time

damann — Tue, 15 Jan 2019 15:19:26 GMT

A different solution if we consider your time resolution is in seconds:

 base_search
 | rename "time_of_down" as time_down, "time_of_up" as time_up 
 | sort time_down
 | eval start=strptime(time_down, "%d/%m/%Y %H:%M:%S")
 | eval end=strptime(time_up, "%d/%m/%Y %H:%M:%S")
 | eval duration=end-start
 | table start,end,duration
 |eval magic=mvrange(start,end)
 | stats dc(magic) as magic
 |eval result=toString(magic,"duration")