topic Re: No of Businessdays between two dates in Splunk Search

No of Businessdays between two dates

snehalatha — Tue, 29 Sep 2020 17:04:00 GMT

Hi , The below give me the no of days between two dates but i want to calculate only no of business days between two dates.
eval start=relative_time(now(),"@d") |
eval start_string=strftime(start,"%Y-%m-%d %A")|
eval end=strptime('time_submitted',"%Y-%m-%d %H:%M")|
eval end_string=strftime(end,"%Y-%m-%d %A") |
eval SLA_duration=round((start-end)/86400,0)|

Please help me

Re: No of Businessdays between two dates

cmerriman — Tue, 05 Dec 2017 13:56:51 GMT

you'll want something like this at the end of your syntax (if I understand, it could probably replace SLA_duration and BusinessDays could be that field name, instead):

|eval Date1=mvrange(start,end,86400)
|eval BusinessDays=mvcount(mvfilter(NOT match(Date1,"(Sun|Sat).*")))

Re: No of Businessdays between two dates

elliotproebstel — Tue, 05 Dec 2017 19:20:23 GMT

I'd never used mvrange before, so I was playing around with this answer. Fun new trick!

A couple things I noticed - @snehalatha, if your value for start is later/greater than your value for end (seems likely, based on the way your calculation of SLA_duration was subtracting end from start value) - then the first line will need to be:
|eval Date1=mvrange(end,start,86400)
And at least with my testing, I needed to add a strftime to the second line, so mine wound up like this:
|eval BusinessDays=mvcount(mvfilter(NOT match(strftime(Date1, "%A"),"(Sun|Sat).*")))

Re: No of Businessdays between two dates

snehalatha — Wed, 06 Dec 2017 06:18:14 GMT

Wow perfect answer. The small mistake what I did till now is
|eval Date1=mvrange(start,end,86400) which was not giving the results.

Thank you so much cmerriman. . Appreciate your quick response.

Re: No of Businessdays between two dates

joesrepsolc — Wed, 19 Jun 2019 03:53:30 GMT

,cmerriman, trying to use this solution for my dates that are already in epoch time (start and end dates). Unexpected results. Data is coming in from .csv files and date columns are a string (like “06/14/2019”) with no times. Which i am just converting to epoch time to do some math between dates. Struggling to avoid the weekend dates though. Data brought in through forwarder have all the additional date_ fields in Splunk, but not from the .csv file.

WHAT I HAVE SO FAR (calculates the days just fine, but not sure how to eliminate the weekends, and only use business days (Mon-Fri) in the results):

index=testdata
| eval TriggerDateX = strptime(TriggerDate,"%m/%d/%y")
| eval MailDateX = strptime(MailDate,"%m/%d/%y")
| eval Diff = round((MailDateX-TriggerDateX)/86400,0)

Appreciate the help. Joe