topic Re: How do I calculate the date difference for two timestamps other than _time and exclude weekends? in Splunk Search

How do I calculate the date difference for two timestamps other than _time and exclude weekends?

splunk_hvijay — Wed, 21 Sep 2016 13:25:38 GMT

Hello,
I have two timestamps , both are NOT _time.

Received Date - 09/10/16
Processed Date - 09/14/16

I need to calculate the age of these two, but need to exclude weekends. I need something like below

base search | eval age = (Processed Date - Received date). | table age

In the above example the result should be 2, so that weekend is excluded..
It should not be 4.

I think date_wday excludes days from _time only. But for me the timestamp for calculating the age is not _time.

Please advise on how to exclude the weekends. Also, please advise on how to exclude public holidays as well. Thanks so much in advance.

Re: How do I calculate the date difference for two timestamps other than _time and exclude weekends?

somesoni2 — Wed, 21 Sep 2016 15:42:54 GMT

Try something like this (run anywhere sample, replace first line with your current search.

| gentimes start=-1 | eval Processdate="9/14/2016" | table Processdate| eval Receivedate="9/10/2016" 
| eval dates=mvrange(strptime(Receivedate,"%m/%d/%Y"),strptime(Processdate,"%m/%d/%Y"),86400) | convert ctime(dates)  timeformat="%A" | eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)")) | eval noOfDays=mvcount(dates)

Re: How do I calculate the date difference for two timestamps other than _time and exclude weekends?

splunk_hvijay — Thu, 22 Sep 2016 02:43:39 GMT

Thanks so much. excellent.. this worked for me.. BTW, i didnt use gentimes start=-1, is it mandatory?

And how to exclude public holidays...thanks again for your help

Re: How do I calculate the date difference for two timestamps other than _time and exclude weekends?

somesoni2 — Thu, 22 Sep 2016 04:10:09 GMT

This line is to generate a sample data to show the remaining processing steps. The whole thing should be replaced by your search which get the required data.

| gentimes start=-1 | eval Processdate="9/14/2016" | table Processdate| eval Receivedate="9/10/2016"

There is no in-build way to identify public holidays in Splunk, you would need to create some custom lookup with dates which are publich holidays and exclude them (not straightforward way. Don't have a ready to use solution, will need to figure that one out).

Re: How do I calculate the date difference for two timestamps other than _time and exclude weekends?

hartfoml — Thu, 22 Sep 2016 13:18:27 GMT

@somesoni2 you're solution is fascinating. I don't use the mvrange or mvcount much. Can you explain what is happening in the eval | eval dates=mvrange(strptime(Receivedate,"%m/%d/%Y"),strptime(Processdate,"%m/%d/%Y"),86400) please

Re: How do I calculate the date difference for two timestamps other than _time and exclude weekends?

somesoni2 — Thu, 22 Sep 2016 14:50:10 GMT

It's basically creating a multivalued field dates with arithmetic series starting with epoch value of Receivedate, ending at epoch value of Processdata, with step of 86400 (1 day in secs). I think it would be clearer if you can run this query

 | gentimes start=-1 | eval Processdate="9/14/2016" | table Processdate| eval Receivedate="9/10/2016" | eval dates_epoch=mvrange(strptime(Receivedate,"%m/%d/%Y"),strptime(Processdate,"%m/%d/%Y"),86400) 
| convert ctime(dates_epoch) as dates_human

Re: How do I calculate the date difference for two timestamps other than _time and exclude weekends?

RRajneesh — Thu, 03 May 2018 09:10:39 GMT

@somesoni2 : Do you have the solution to identify holidays yet ?
Thanks.

Re: How do I calculate the date difference for two timestamps other than _time and exclude weekends?

somesoni2 — Thu, 03 May 2018 16:12:08 GMT

I think so. As I mentioned before, for excluding public holidays, you'd need to setup a lookup table with list of holidays. You can find one here: https://gist.github.com/shivaas/4758439

Once you've your lookup table setup, say holidays.csv with column date (holiday date in format %Y-%m-%d,name (don't really care for this column), your query would be like this (using a sample data):

| gentimes start=-1 | eval Processdate="06/01/2018" | table Processdate| eval Receivedate="05/01/2018"  
   | eval dates=mvrange(strptime(Receivedate,"%m/%d/%Y"),strptime(Processdate,"%m/%d/%Y"),86400) | convert ctime(dates) timeformat="%A %Y-%m-%d" | eval workdays=mvfilter(NOT match(dates,[| inputlookup holidays.csv | stats values(date) as date| eval date=mvappend("(Saturday",date,"Sunday)")| eval search=mvjoin(date,"|") | table search | format "" "" "" "" "" ""])) | eval noOfDays=mvcount(dates)

Re: How do I calculate the date difference for two timestamps other than _time and exclude weekends?

RRajneesh — Thu, 03 May 2018 16:37:19 GMT

Hi,

Thanks for your reply.

Getting the below error :

Error in 'eval' command: The expression is malformed. An unexpected character is reached at ') ))'.

Also, the last part, shouldn't it be mvcount(workdays) instead of mvcount(dates) ?

Thanks in advance for your reply.