topic Re: Excels' Networkdays equivalent in Splunk in Splunk Search

Excels' Networkdays equivalent in Splunk

jhlopez — Thu, 13 Nov 2014 03:22:55 GMT

Is there an equivalent or something like Networkdays from excel in Splunk??
I want to calculate the duration between two dates and exclude from the calculation all the weekends and holidays.

Ex. Startdate = Dec. 30, 2013, Enddate = Jan. 4, 2014
I want to get the duration between above dates. Note that Jan. 1, is holiday and assuming that Jan, 2, 3 would be weekends.

Please Help..

Thanks,

Re: Excels' Networkdays equivalent in Splunk

vasanthmss — Thu, 13 Nov 2014 03:56:56 GMT

Try this,

search:

| gentimes start=-1 | eval CreateDateEpoch="1412136000" | table CreateDateEpoch | eval start=relative_time(CreateDateEpoch,"@d") | eval end=relative_time(now(),"@d") | eval Date=mvrange(start,end+86400,86400) | convert ctime(Date) timeformat="%+" | eval NoOfBusinessDays=mvcount(mvfilter(NOT match(Date,"(Sun|Sat).*")))

Source: https://answers.splunk.com/answers/186662/i-am-try-to-to-find-the-number-of-business-days-be.html

Re: Excels' Networkdays equivalent in Splunk

jhlopez — Thu, 13 Nov 2014 04:05:02 GMT

Thanks vasanthmss, I'll give this a try.

Re: Excels' Networkdays equivalent in Splunk

jhlopez — Thu, 13 Nov 2014 10:06:51 GMT

Hi vasanthmss,
The Weekends work for me, but how about holidays?? Do you any idea?
Thanks,

Re: Excels' Networkdays equivalent in Splunk

gfuente — Thu, 13 Nov 2014 10:22:03 GMT

Hello

Maybe you coudl create a lookup with all the holidays, and the in the query filterout those days.

Regards

Re: Excels' Networkdays equivalent in Splunk

jhlopez — Mon, 28 Sep 2020 18:10:59 GMT

Hi gfuerte,

I am able to create a lookup with all the holidays, but I am not able to filter using the lookup output fields. The "match" expression uses a regex.

For example, I have a new fields from lookup called "Holiday_list". Your idea was something like below??(correct me if I'm wrong)
mvfilter(not match(Date, Holiday_list)), wherein the "Date" will be filtered based on the contents of the "Holiday_list" fields.

Thanks