topic Re: How to calculate downtime or time difference within a custom date time range in Splunk Search

How to calculate downtime or time difference within a custom date time range

sh254087 — Thu, 02 Mar 2023 12:14:54 GMT

This is in continuation to my query(resolved) here - Solved: How to check time difference between a series of e... - Splunk Community

Here I was able to get the overall downtime for any selected time range by using SUM() and AVG() after teh suggested solution.

In continuation to the sample scenario explained in the aforementioned query, I have to now handle a scheduled downtime.

We have a process which turns our servers down at 4:30 pm and brings them up at 1:30am UTC time , automatically on a schedule, every day. The 9 hrs 10mins downtime which is shown in the sample is of the same.

In this case I was able to come up with unplanned downtime by subtracting 9:10 from total downtime as it was a specific selected time range : 2023-02-21T16:00:00Z to 2023-02-22T02:25:00Z and as there was only one record of 9hrs 10min downtime. However, we have noticed few other scenarios which are a bit complex, like -

1. When the time range selected is 24 hrs or yesterday (3/1/23 12:00:00.000 AM to 3/2/23 12:00:00.000 AM), assuming the system was up all the time outside the scheduled downtime, the total downtime will be shown as empty/null as the first event for the day will be at 1:35 and last one at 16:30 and no other downtime in between. (this is still okay, as I just have to handle the null downtime with a zero)

2. When the time range selected is anything more than a day, say 7days or 30 days or even a random custom date time range, I'm not sure how to calculate the actual downtime.
Can this be handled somehow?

Re: How to calculate downtime or time difference within a custom date time range

ITWhisperer — Thu, 02 Mar 2023 12:53:40 GMT

Can you not calculate the time HHMM from the timestamp and ignore events which are in the scheduled downtime period?

Re: How to calculate downtime or time difference within a custom date time range

sh254087 — Fri, 03 Mar 2023 07:59:33 GMT

Working with date and time is very new to me and I'm not able to figure this up.

Re: How to calculate downtime or time difference within a custom date time range

ITWhisperer — Fri, 03 Mar 2023 08:45:40 GMT

| eval time=strftime(_time,"%H%M")

Re: How to calculate downtime or time difference within a custom date time range

sh254087 — Fri, 03 Mar 2023 12:32:18 GMT

Tried something like this using this but no luck. Even after I exclude the events (downtime beginning and downtime end timestamps) the downtime calculation didn't change.

| eval time=strftime(_time,"%H:%M")
| search time != 16:30 AND time != 01:35 AND time != 01:40
| eval up=if(SampleCount >= 5, 1, null())
| streamstats window=2 global=f range(_time) as diff by up
| eval downtime=if(diff-300>0,diff-300,null())
| eval uptime=if(up=1, 300,0)
| table timestamp time up diff downtime uptime

After excludingBefore excluding

Re: How to calculate downtime or time difference within a custom date time range

ITWhisperer — Fri, 03 Mar 2023 12:55:55 GMT

Why would you change from my suggestion?

Try without the : so that the hours and minutes becomes a single number e.g. 09:30 becomes 0930 (930) then you can compare it with another number e.g. 1000 or 2330 and do a simple arithmetic comparison e.g. is it between 1630 and 2300, or whatever your time constraints are.

Re: How to calculate downtime or time difference within a custom date time range

sh254087 — Fri, 03 Mar 2023 13:49:02 GMT

I was, but, just trying different possibilities while I took the screenshot.

I had tried keeping it as is, but the comparison that I tried hadn't worked. Was getting like this -

Even if I change this limit like - 'time<1625' instead of 'time<1630', the downtime doesn't change.
It just don't display those rows, but are somehow being considered for the downtime calculation.

Re: How to calculate downtime or time difference within a custom date time range

ITWhisperer — Fri, 03 Mar 2023 14:20:14 GMT

Try moving it to after the streamstats!