Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- A single event has two dates. How do I count the n...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

ahallak2016

Explorer

01-16-2017
08:24 PM

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 start*date to the end*date 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 start*date to the end*date, 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?

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DalJeanis

SplunkTrust

01-17-2017
02:10 PM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DalJeanis

SplunkTrust

01-17-2017
04:27 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DalJeanis

SplunkTrust

01-17-2017
04:38 PM

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)

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

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

ahallak2016

Explorer

01-18-2017
09:20 PM

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

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DalJeanis

SplunkTrust

01-21-2017
07:59 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

ahallak2016

Explorer

01-22-2017
02:25 PM

*date' and 'end*date'.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DalJeanis

SplunkTrust

06-13-2017
08:50 AM

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.