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!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...