Splunk Search

Excels' Networkdays equivalent in Splunk

jhlopez
Explorer

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,

Tags (2)
0 Karma
1 Solution

vasanthmss
Motivator

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

V

View solution in original post

0 Karma

vasanthmss
Motivator

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

V
0 Karma

jhlopez
Explorer

Thanks vasanthmss, I'll give this a try.

0 Karma

jhlopez
Explorer

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

0 Karma

gfuente
Motivator

Hello

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

Regards

0 Karma

jhlopez
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...