How to find Number of days between two custom dates, excluding weekends and holidays in Splunk?

How To Find Number Of Days Between Two Custom Dates, Excluding Weekends And Holidays In Splunk?

Hello Splunkers !!!!
Today we are back with another astonishing  trick of Splunk. I hope you have understood from the title of this blog that what we will gonna do today,  if not then let’s take an example.
Suppose you have a custom date field called “recived_date” which is a date of receiving an application and now if you want to calculate the number of days covered till today from when the application had received.

1
It’s simple right but the real problem will occur if you try to exclude weekends (i.e. Saturday and Sunday) or any holidays (i.e Christmas or New year etc). That means you only need to consider working days.

Ahh! Now it’s tricky right, don’t worry we are here to help you out from this kin of situation in Splunk.

Step 1:
First we will start with excluding the weekends.
So try to understand the query given below and it will give you the desired result.

index="time_gap" sourcetype="csv"
| eval now=now(),now_date=strftime(now,"%Y/%m/%d")
| eval dates=mvrange(strptime(recived_date,"%Y/%m/%d"),now,86400)
| table now recived_date dates now_date
| convert ctime(dates) timeformat="%A"
| eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)"))
| eval noOfDays=mvcount(dates)

2

As you can see that in the “dates” field there is no Sunday or Saturday right. That’s it, our first objective has been completed. Now remove the “dates” and “now” field you will get your desired result.

You can also know about :  Usage Of Splunk Eval Function: MVRANGE

3
Explanation:
At first with the help of mvrange() function we have created a multi-value field consisting all the dates from “recived_date” and now() with the range of 86400 sec (total  number of seconds for 1day ). Then using the ctime() function with convert command we have converted them into human readable date format and then using “timeformat” we have changed those dates in days (like Monday,Tuesday etc) and finally we discarded Sunday and Saturday from the “dates” field.

Step 2:
Now we will try to exclude the holidays, we will try to discard here 1st January, 2020 from the “noOffDays” field.
At first try to find the epoch time of 1st January, 2020 , you can do it like this.

| makeresults
| eval date="2020/01/01"
| fields - _time
| eval date_epoch=strptime(date,"%Y/%m/%d")

4

You need the change the date which you want to exclude in the underlined area. Then copy the value from the “date_epoch” field and paste it in your main query as shown in below figure (Step 3).
Now if you have multiple holidays to remove from the list then you can do it in this way,

| makeresults
| eval date=split("2019/12/25,2020/01/01",",")
| mvexpand date
| fields - _time
| eval date_epoch=strptime(date,"%Y/%m/%d")

5
You can split all dates separated by comma (,)
You need the change the date which you want to exclude in the underlined area. Then copy the value from the “date_epoch” field and paste it in your main query as shown in below figure (step 4).

Step 3:

index="time_gap" sourcetype="csv"
| eval now=now(),now_date=strftime(now,"%Y/%m/%d")
| eval dates=mvrange(strptime(recived_date,"%Y/%m/%d"),now,86400)
| table now recived_date dates now_date
| rex field=dates mode=sed s/1577817000.000000//g
| convert ctime(dates) timeformat="%A"
| eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)"))
| eval noOfDays=mvcount(dates)
| fields - now dates

6

Now you can see the changes, if you compare it with our previous result you can see the count value has been decreased by 1 for the first two rows (because the first two row’s dates are beyond 2020/01/01).

You can also know about :  How To Add Fixed Time Range In Drop Down Option

To understand the queries you can see our other blogs upon rex,mvfilter,convert,strptime,makeresults etc.

Step 4:
If you want to exclude multiple holidays then you can do it in this way.

index="time_gap" sourcetype="csv"
| eval now=now(),now_date=strftime(now,"%Y/%m/%d")
| eval dates=mvrange(strptime(recived_date,"%Y/%m/%d"),now,86400)
| table now recived_date dates now_date
| eval dates=mvfilter(NOT match(dates ,"(1577212200.000000|1577817000.000000)"))
| convert ctime(dates) timeformat="%A"
| eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)"))
| eval noOfDays=mvcount(dates)
| fields - now dates

7

Hope you have understood the below topic.
How To Find Number Of Days Between Two Custom Dates, Excluding Weekends And Holidays In Splunk?

Happy Splunking !!

Advertisements

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.