Splunk Search
Highlighted

A single event has two dates. How do I count the number of days excluding weekends and holidays between these two dates?

Explorer

A single event has two dates. How do I count the number of days excluding weekends and holidays between these two dates?

For instance, an event will have two date fields such as startdate and end_date. I need to count the number of days from the startdate to the enddate while skipping over weekends and holidays.

All the holidays that I am concerned with are in a lookup file.

I was thinking of using an array to store all the dates from the startdate to the enddate, loop through array, check if weekend, if not a weekend, increment a counter. This counter will be the number of days. But, unfortunately, I don't know how to use arrays in Splunk and am not sure how to calculate what the dates are to add to an array which fall between the start and end_date field.

Also, is there a better way of performing this task besides using an array of dates?

0 Karma
Highlighted

Re: A single event has two dates. How do I count the number of days excluding weekends and holidays between these two dates?

SplunkTrust
SplunkTrust

How many days are we talking about? A few days, a few weeks, or might these records span years?

The efficient solution for an HR system that deals with long-term leave will probably be different than the efficient solution for a system that tracks, for instance, business travel.

I'd suggest, if you are using an array of dates, just put the holidays and weekends into the array. Take the end date, minus the start date, plus one, and then subtract the sum of "off" days between those dates, inclusive.

0 Karma
Highlighted

Re: A single event has two dates. How do I count the number of days excluding weekends and holidays between these two dates?

SplunkTrust
SplunkTrust

Okay, to start you out, I've given you a full set of tools to test with. This code generates data from scratch.

This search returns one record for each combination of start and end dates from 12/10/2016 to 1/18/2017 -

| gentimes start=12/10/2016 end=01/18/2017
| rename starttime to StartDate
| table StartDate
| eval EndDate = mvappend(relative_time(StartDate,"+1d"),relative_time(StartDate,"+2d"),relative_time(StartDate,"+3d"),relative_time(StartDate,"+4d"))
| eval EndDate = mvappend(EndDate, relative_time(StartDate,"+5d"),relative_time(StartDate,"+6d"),relative_time(StartDate,"+7d"),relative_time(StartDate,"+8d"))
| eval EndDate = mvappend(EndDate, relative_time(StartDate,"+9d"),relative_time(StartDate,"+10d"),relative_time(StartDate,"+11d"),relative_time(StartDate,"+12d"))
| eval EndDate = mvappend(EndDate, relative_time(StartDate,"+13d"),relative_time(StartDate,"+14d"),relative_time(StartDate,"+15d"),relative_time(StartDate,"+16d"))
| eval EndDate = mvappend(EndDate, relative_time(StartDate,"+17d"),relative_time(StartDate,"+18d"),relative_time(StartDate,"+19d"),relative_time(StartDate,"+20d"))
| eval EndDate = mvappend(EndDate, relative_time(StartDate,"+21d"),relative_time(StartDate,"+22d"),relative_time(StartDate,"+23d"),relative_time(StartDate,"+24d"))
| mvExpand EndDate limit=0
| eval ThrowAway = if(EndDate <= now(),0,1)
| search ThrowAway = 0 
| eval HumanStartDate = strftime(StartDate,"%Y-%m-%d")
| eval HumanEndDate = strftime(EndDate,"%Y-%m-%d")
| sort StartDate EndDate
| fields StartDate EndDate HumanStartDate HumanEndDate

This search generates epoch times for all the weekend days and holidays between 12/10/2016 and 01/18/2017

| gentimes start=12/10/2016 end=01/18/2017
| eval MLKDay=strptime("2017-01-16","%Y-%m-%d")
| eval NYDay=strptime("2017-01-02","%Y-%m-%d")
| eval MyDay = starttime
| eval MySun = relative_time(starttime,"-0d@w")
| eval MySat = MySun+518400
| eval IsADayOff = case(MyDay==MySun,1,MyDay==MySat,1,MyDay = NYDay,1, MyDay = MLKDay,1,true(),0)
| search IsADayOff=1
| rename starttime to TheDate
| table TheDate IsADayOff
| eval TheHumanDate = strftime(TheDate,"%Y-%m-%d")

This search combines the two to calculate the number of work days between any two of those dates

| gentimes start=12/12/2016 end=01/18/2017 
| rename starttime to StartDate
| table StartDate
| eval EndDate = mvappend(relative_time(StartDate,"+1d"),relative_time(StartDate,"+2d"),relative_time(StartDate,"+3d"),relative_time(StartDate,"+4d"))
| eval EndDate = mvappend(EndDate, relative_time(StartDate,"+5d"),relative_time(StartDate,"+6d"),relative_time(StartDate,"+7d"),relative_time(StartDate,"+8d"))
| eval EndDate = mvappend(EndDate, relative_time(StartDate,"+17d"),relative_time(StartDate,"+18d"),relative_time(StartDate,"+19d"),relative_time(StartDate,"+20d"))
| eval EndDate = mvappend(EndDate, relative_time(StartDate,"+21d"),relative_time(StartDate,"+22d"),relative_time(StartDate,"+23d"),relative_time(StartDate,"+24d"))
| mvExpand EndDate limit=0
| eval ThrowAway = if(EndDate <= now(),0,1)
| search ThrowAway = 0 
| eval HumanStartDate = strftime(StartDate,"%Y-%m-%d")
| eval HumanEndDate = strftime(EndDate,"%Y-%m-%d")
| sort StartDate EndDate
| fields StartDate EndDate HumanStartDate HumanEndDate Duration
| eval Duration = 1 + floor((EndDate - StartDate)/86400)
| eval IsADayOff = 1


| join IsADayOff max=0 type=left 
[
| gentimes start=12/10/2016 end=01/18/2017
| eval MLKDay=strptime("2017-01-16","%Y-%m-%d")
| eval NYDay=strptime("2017-01-02","%Y-%m-%d")
| eval MyDay = starttime
| eval MySun = relative_time(starttime,"-0d@w")
| eval MySat = MySun+518400
| eval IsADayOff = case(MyDay==MySun,1,MyDay==MySat,1,MyDay = NYDay,1, MyDay = MLKDay,1,true(),0)
| search IsADayOff=1
| rename starttime to TheDate
| table TheDate IsADayOff
| eval TheHumanDate = strftime(TheDate,"%Y-%m-%d")
]


| eval OffDays = if(isnull(TheDate),0,if((TheDate >= StartDate AND TheDate <=EndDate),1,0))
| stats First(Duration) as Duration, Sum(OffDays) as OffDays by StartDate EndDate
| eval WorkDays = Duration - OffDays
| eval HumanStartDate = strftime(StartDate,"%Y-%m-%d")
| eval HumanEndDate = strftime(EndDate,"%Y-%m-%d")
| table HumanStartDate HumanEndDate Duration OffDays WorkDays 
Highlighted

Re: A single event has two dates. How do I count the number of days excluding weekends and holidays between these two dates?

SplunkTrust
SplunkTrust

There are two ways to proceed.

Either you can calculate every possible combination up front, remembering the limit of 50K results from a lookup, or you can calculate only the combinations you need, at search time.

My gut tells me that most places would find it more efficient to do the calculation up front, and put it in a lookup table.

The way to make that work is probably to have the lookup be based solely on the startdate, and have columns for duration 1 days, 2 days etc up to the limit of the number of columns splunk will allow.

If doing it myself, I would tend to use base 10 and produce a lookup table with 100 values in each record and two keys
- StartDate, and
- RecordNumber ... calculated as floor(duration/100)

0 Karma
Highlighted

Re: A single event has two dates. How do I count the number of days excluding weekends and holidays between these two dates?

Explorer

The issue with gentimes is that it needs to be at the very start of the query and hence, I cannot pass it the start and end date fields from the data within my index?

0 Karma
Highlighted

Re: A single event has two dates. How do I count the number of days excluding weekends and holidays between these two dates?

SplunkTrust
SplunkTrust

It has to be at the start of its section, but that can be a subquery, and it can receive passed-in values (for instance by using the map command.

0 Karma
Highlighted

Re: A single event has two dates. How do I count the number of days excluding weekends and holidays between these two dates?

Explorer

really? could you provide an example of that? Lets say I already have two fields in my event called 'startdate' and 'enddate'.

0 Karma
Highlighted

Re: A single event has two dates. How do I count the number of days excluding weekends and holidays between these two dates?

SplunkTrust
SplunkTrust

Sorry, didn't see the question. Here's a run-anywhere chunk of code demonstrating how to add such records to an ongoing search...

| makeresults | where false()
| rename COMMENT as "The above just establishes that there was an ongoing search of whatever kind."      

| rename COMMENT as "The eval statement below could be replaced with a stats command to find the start_date and end_date you wanted, this is just an example."    
| append [| makeresults | eval s1="12/10/2016", e1="01/18/2017"| map search="| gentimes start=$s1$ end=$e1$"]

The output could be formatted any way you wanted before it is appended into the ongoing search. You could also use appendpipe if you wanted to use data from the ongoing search to calculate the beginning and ending dates.

0 Karma