Splunk Search
Highlighted

I am try to to find the number of business days between two extracted fields

Path Finder

I am trying to follow what the others have done, but I am stumped as to why things are not functioning for me. I have a couple of date fields and sometimes I want to find the number of business days between those fields and sometimes from one of those fields to now. No matter what I try, I cannot seem to append BusinessDays to my rows.

index=dccmtdit sourcetype="dccmt_log4j_json" | transaction DpsNum |  convert ctime(CreateDateEpoch) as cd timeformat="%m/%d/%Y:%H:%M:%S" |  eval ld=strftime(now(), "%m/%d/%Y:%H:%M:%S") | append [|gentimes start=$cd$ end=$ld$ | search starthuman!="Sun*" starthuman!="Sat*" | stats count as BusinessDays]

This produces the following error:
[subsearch]: command="gentimes", invalid literal for int() with base 10: '$cd$'. Traceback: Traceback (most recent call last): File "C:\Program Files\Splunk\etc\apps\search\bin\gentimes.py", line 66, in generateTimestamps starttime = getTime(startagostr) File "C:\Program Files\Splunk\etc\apps\search\bin\gentimes.py", line 35, in getTime daysago = int(val) ValueError: invalid literal for int() with base 10: '$cd$'

I am stumped as to why I am getting the error. But even if I hard code the dates to get by that error, I do not have BusinessDays as a field after my search. Can anyone tell me what I am doing wrong? Eventually, I want to turn this into a macro so that I can always get the BusinessDays between two dates.

0 Karma
Highlighted

Re: I am try to to find the number of business days between two extracted fields

SplunkTrust
SplunkTrust

Try something like this (runanywhere sample)

| 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).*")))

So for your case

index=dccmtdit sourcetype="dccmt_log4j_json" | transaction DpsNum | 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).*"))) | fields - start end Date

View solution in original post

Highlighted

Re: I am try to to find the number of business days between two extracted fields

Path Finder

Works like a charm! I have added it to a macro and now I am finding business days like crazy. I may start to go really crazy and look for holidays, but I am happy now.

0 Karma
Highlighted

Re: I am try to to find the number of business days between two extracted fields

Explorer

Hi,

How about for holidays? Have any idea?

Thanks

0 Karma
Highlighted

Re: I am try to to find the number of business days between two extracted fields

Path Finder

You can build a regex string which checks for the holidays from a lookup table. That takes care of the floating holidays like Thanksgiving. Here is an example macro I created where the regex string is hardcoded to look for Christmas Day, New Years Day and Independence Day in America.

eval localvar = $StartDate$
| eval mysd = if( isnotnull(tonumber(localvar)), tonumber(localvar), strptime(localvar, "%m/%d/%Y %H:%M") )
| eval start = relative_time(mysd, "@d")
| eval localvar = $EndDate$
| eval myed = if( isnotnull(tonumber(localvar)), tonumber(localvar), strptime(localvar, "%m/%d/%Y %H:%M") )
| eval end = if (myed == -1, relative_time(now(), "@d"), relative_time($EndDate$, "@d") + 86400)
| eval DateRange = mvrange(start, end, 86400)
| convert ctime(DateRange) timeformat="%+"
| eval BusinessDays = mvcount( mvfilter( NOT match(DateRange,"(Sun|Sat).*") AND NOT match(DateRange, ".*(Dec 25|Jul 04|Jan 01).*") ) )
| eval BusinessDays = if( isnotnull(BusinessDays), BusinessDays, 1 )
| fields - localvar mysd myed start end DateRange
0 Karma
Highlighted

Re: I am try to to find the number of business days between two extracted fields

Explorer

Hi nfiedlein,

I believe your example above is just for regular holidays (Known holidays, meaning static dates eg. Christmas newyear...). How about if the holiday list is dynamic?? For example, Holiday list are just stored in a single field. Is it possible to match and filter these fields with the Date range?

Thanks

0 Karma
Highlighted

Re: I am try to to find the number of business days between two extracted fields

Path Finder

I have not completed the code, yet, but I am going to be creating a lookup which generates the regular expression for matching holidays. The users will then be able to load a holidays CSV file every year against which the expression match will work. I have not yet completed the design and if someone else has it, please point me there.

0 Karma
Highlighted

Re: I am try to to find the number of business days between two extracted fields

Explorer

Ok, just please post it here if you have done it. Me as well, I'll just post it here if I found a solution for it. Thanks,

0 Karma
Highlighted

Re: I am try to to find the number of business days between two extracted fields

Path Finder

It is not scheduled for my next two sprints, so it may be a while.

0 Karma
Highlighted

Re: I am try to to find the number of business days between two extracted fields

Path Finder

Hi,

Do we have something regarding the dynamic Holidays List yet?

0 Karma