Splunk Search

count time of my select time within week-ends

wcastillocruz
Path Finder

Hi community,

is it possible to calculate the time between info_max_time and info_min_time according to the period chosen in the button "select time" but removing the weekends contained in the period.

for example I run a search like this:
sourcetype = XXXXXXX | `transactions` | eval date_wday = strftime (_time, "% w% H") | search date_wday> = 106 AND date_wday <= 523 |

I take the events between Monday 6 a.m. and Friday 10 p.m.
if my select time equals "the last month" so April then
I have to do :
| eval period = info_max_time - info_min_time | and delete from my variable "period" the weekends <
for the last month period must be = 22 days because 30 days of the month - 8 days of weekends.

I want the calculation to be systematic according to the period chosen in the button "select time"

can you help me please

Labels (4)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

You need to determine which day of the week the start and end times are and factor that in to work out how many weekends exist between the two dates. So, work out how many days before the first weekend and how many days since the last weekend, subtract these from the number of days in the period (assume start at midnight on first day and end at 23:59:59 on the last day), subtract a further 2 for the first weekend, divide by 7 to give number of whole weeks between dates. Multiply this by 5 (working days) and add the working days before the first weekend and after the last weekend back in. There is possibly a shorter way to do this, but this appears to be sound.

| makeresults 
| eval info_min_time=strptime("2021-04-01","%Y-%m-%d")
| eval info_max_time=strptime("2021-04-30","%Y-%m-%d")+(24*60*60)-1
| eval startwday=strftime(info_min_time,"%w")
| eval endwday=strftime(info_max_time,"%w")
| eval days=(info_max_time-info_min_time+1)/(24*60*60)
| eval startworkdays=(6-startwday)%6
| eval endworkdays=endwday%6
| eval fullweeks=(days-startworkdays-endworkdays-2)/7
| eval workdays=(5*fullweeks)+startworkdays+endworkdays
| fieldformat info_min_time=strftime(info_min_time,"%Y-%m-%d")
| fieldformat info_max_time=strftime(info_max_time,"%Y-%m-%d")

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

You need to determine which day of the week the start and end times are and factor that in to work out how many weekends exist between the two dates. So, work out how many days before the first weekend and how many days since the last weekend, subtract these from the number of days in the period (assume start at midnight on first day and end at 23:59:59 on the last day), subtract a further 2 for the first weekend, divide by 7 to give number of whole weeks between dates. Multiply this by 5 (working days) and add the working days before the first weekend and after the last weekend back in. There is possibly a shorter way to do this, but this appears to be sound.

| makeresults 
| eval info_min_time=strptime("2021-04-01","%Y-%m-%d")
| eval info_max_time=strptime("2021-04-30","%Y-%m-%d")+(24*60*60)-1
| eval startwday=strftime(info_min_time,"%w")
| eval endwday=strftime(info_max_time,"%w")
| eval days=(info_max_time-info_min_time+1)/(24*60*60)
| eval startworkdays=(6-startwday)%6
| eval endworkdays=endwday%6
| eval fullweeks=(days-startworkdays-endworkdays-2)/7
| eval workdays=(5*fullweeks)+startworkdays+endworkdays
| fieldformat info_min_time=strftime(info_min_time,"%Y-%m-%d")
| fieldformat info_max_time=strftime(info_max_time,"%Y-%m-%d")

wcastillocruz
Path Finder

@ITWhisperer
Thank you very much, that's right

0 Karma
Get Updates on the Splunk Community!

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...