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!

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...