Splunk Search

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

nfieglein
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
1 Solution

somesoni2
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

somesoni2
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

jhlopez
Explorer

Hi,

How about for holidays? Have any idea?

Thanks

0 Karma

nfieglein
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

jhlopez
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

nfieglein
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

jhlopez
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

nfieglein
Path Finder

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

0 Karma

SwatiApte
Path Finder

Hi,

Do we have something regarding the dynamic Holidays List yet?

0 Karma

nfieglein
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...