topic Re: How to search count by only business days? in Splunk Search

How to search count by only business days?

lewisgrantevans — Tue, 11 Jun 2019 13:21:45 GMT

Hello Splunk Ninjas,

I have created an 'aging' field that counts the number of days since a certain date & time. I would like to remove weekends from this count.

I am using:

| eval Created=strptime(Created,"%d/%m/%Y %H:%M")
| eval c_time=strftime(Created,"%d/%m/%Y %H:%M")
| eval aging=round((now()-strptime(c_time, "%d/%m/%Y %H:%M"))/86400,2)

Many thanks in advance.

Re: How to search count by only business days?

kmorris_splunk — Tue, 11 Jun 2019 15:29:34 GMT

You could do something like this in the beginning of your search:

[BASE SEARCH HERE]
| eval daytype=if(date_wday in ("monday","tuesday","wednesday","thursday","friday"), "busday","weekend") 
| where daytype="busday"

Re: How to search count by only business days?

lewisgrantevans — Wed, 30 Sep 2020 00:53:26 GMT

Thanks guys, but i should have made it clear that i'm only using a .csv file for this query so i don't have any time fields except for the one i've created below -

| eval Created=strptime(Created,"%d/%m/%Y %H:%M")
| eval c_time=strftime(Created,"%d/%m/%Y %H:%M")
| eval aging=round((now()-strptime(c_time, "%d/%m/%Y %H:%M"))/86400,2)

Cheers,
Lewis

Re: How to search count by only business days?

VatsalJagani — Wed, 12 Jun 2019 08:42:40 GMT

@lewisgrantevans,

I've tried to implement Ronald's second solution. Please try.

| eval Created2=floor(relative_time(strptime(Created,"%d/%m/%Y %H:%M"),"@d"))
| eval today = floor(relative_time(now(),"@d"))
| eval createdW = strftime(Created2,"%w")
| convert num(createdW)
| eval createdW = if(createdW == 0, 7, createdW)
| eval todayW = strftime(today,"%w")
| convert num(todayW)
| eval todayW = if(todayW == 0, 7, todayW)
| eval totalDays = (today-Created2)/86400
| eval Aging = totalDays - 2 * floor((totalDays/7))
| eval Aging = if(totalDays % 7 != 0, case(createdW = 7, Aging-1, todayW = 7, Aging-1, todayW < createdW, Aging-2, 1=1, Aging), Aging)

Hope this helps!!!!

Re: How to search count by only business days?

FrankVl — Wed, 12 Jun 2019 12:56:10 GMT

The solutions referring to date_wday are indeed not of any use to you. But the answer from @VatsalJagani should work, I think.

Re: How to search count by only business days?

lewisgrantevans — Wed, 12 Jun 2019 13:27:10 GMT

Hi @FrankVl & @VatsalJagani , I think we're close but it's not quite calculating correctly. I've attached a screenshot of my results in excel.

I used almost the exact query (I just changed the eval to make a Created2 field so i could retain the Created field).

The results for June 4th & 5th are more accurate than the excel answer, taking the time into consideration, however the results for June 6th, 7th & 10th are not accurate. You'll notice a jump in noOfBusinessDays between the 5th & 6th.

| eval Created2=strptime(Created,"%d/%m/%Y %H:%M")
| eval today = now()
| eval createdW = strftime(Created2,"%w")
| convert num(createdW)
| eval createdW = if(createdW == 0, 7, createdW)
| eval todayW = strftime(today,"%w")
| convert num(todayW)
| eval todayW = if(todayW == 0, 7, todayW)
| eval totalDays = round(((today-Created2)/(86400)),2)
| eval noOfBusinessDays = totalDays - 2 * (totalDays/7)
| eval noOfBusinessDays = if(totalDays % 7 != 0, case(createdW = 7, noOfBusinessDays-1, todayW = 7, noOfBusinessDays-1, todayW < createdW, noOfBusinessDays-2, 1=1, noOfBusinessDays), noOfBusinessDays)

Thanks again!
Lewis

Re: How to search count by only business days?

FrankVl — Wed, 12 Jun 2019 15:13:39 GMT

Try adding a floor to the second to last line: | eval noOfBusinessDays = totalDays - 2 * floor((totalDays/7))

You don't want to take the decimal part of totalDays/7 into account.

Re: How to search count by only business days?

lewisgrantevans — Wed, 12 Jun 2019 15:23:18 GMT

Thank you @FrankVl & @VatsalJagani !

This works perfectly when taking @FrankVl last comment into consideration

"Try adding a floor to the second to last line: | eval noOfBusinessDays = totalDays - 2 * floor((totalDays/7))

You don't want to take the decimal part of totalDays/7 into account."

Re: How to search count by only business days?

FrankVl — Wed, 12 Jun 2019 15:32:25 GMT

Also: it seems to be missing logic for dealing with createdW=6 or todayW=6, right?

Ah, the stackoverflow solution that was referred to uses a function that calculates the 'days between'. And round(((today-Created2)/(86400)),2) is not the same as days between. Same for excel, that does not do a days between but adds 1.

I think you also need to replace the round with a floor: | eval totalDays = floor(((today-Created2)/86400)).

Alternative would be to subtract 2 for sundays and subtract 1 for saturdays in the case statement.

Re: How to search count by only business days?

FrankVl — Wed, 12 Jun 2019 15:46:26 GMT

Careful it seems to be missing logic for dealing with createdW=6 or todayW=6, right?

The stackoverflow solution that was referred to uses a function that calculates the 'days between'. And round(((today-Created2)/(86400)),2) is not the same as days between. Same for excel, that does not do a days between but adds 1.

I think you also need to replace the round with a floor: | eval totalDays = floor(((today-Created2)/86400)) . (but then you do have an off-by-one compared to Excel, depends a bit on what you want. Is today-today equal to 0 or to 1?)

Alternative would be to subtract 2 for sundays and subtract 1 for saturdays in the case statement.

Re: How to search count by only business days?

FrankVl — Wed, 12 Jun 2019 16:02:31 GMT

Looking a bit closer, I think the issue might actually be with the totalDays % 7 != 0. totalDays % 7 is practically never 0 except when the timestamps are identical. You need a floor around that.

So the last 2 lines should be:

| eval noOfBusinessDays = totalDays - 2 * floor((totalDays/7))
| eval noOfBusinessDays = if(floor(totalDays % 7) != 0, case(createdW = 7, noOfBusinessDays-1, todayW = 7, noOfBusinessDays-1, todayW < createdW, noOfBusinessDays-2, 1=1, noOfBusinessDays), noOfBusinessDays)

Re: How to search count by only business days?

lewisgrantevans — Wed, 12 Jun 2019 16:09:02 GMT

Yes i was a little hasty, i noticed a miscalculation on June 5th but your most recent comment seems to have rectified it. I'm now using -

| eval Created2=strptime(Created,"%d/%m/%Y %H:%M")
| eval today = now()
| eval createdW = strftime(Created2,"%w")
| convert num(createdW)
| eval createdW = if(createdW == 0, 7, createdW)
| eval todayW = strftime(today,"%w")
| convert num(todayW)
| eval todayW = if(todayW == 0, 7, todayW)
| eval totalDays = floor(((today-Created2)/86400))
| eval Aging = totalDays - 2 * floor((totalDays/7))
| eval Aging = if(totalDays % 7 != 0, case(createdW = 7, Aging-1, todayW = 7, Aging-1, todayW < createdW, Aging-2, 1=1, Aging), Aging)

Cheers buddy

Re: How to search count by only business days?

FrankVl — Wed, 12 Jun 2019 17:16:14 GMT

The way the totalDays is calculated is just really tricky. It might be much better to simply reduce each date to the start of that day first. That way, if the two dates are the same day of week, the totalDays is 0 or a multiple of 7. Using floor does not solve that if now() is earlier in the day than Created2.

So I would suggest:

| eval Created2=floor(relative_time(strptime(Created,"%d/%m/%Y %H:%M"),"@d"))
| eval today = floor(relative_time(now(),"@d"))
| eval createdW = strftime(Created2,"%w")
| convert num(createdW)
| eval createdW = if(createdW == 0, 7, createdW)
| eval todayW = strftime(today,"%w")
| convert num(todayW)
| eval todayW = if(todayW == 0, 7, todayW)
| eval totalDays = (today-Created2)/86400
| eval Aging = totalDays - 2 * floor((totalDays/7))
| eval Aging = if(totalDays % 7 != 0, case(createdW = 7, Aging-1, todayW = 7, Aging-1, todayW < createdW, Aging-2, 1=1, Aging), Aging)

Re: How to search count by only business days?

VatsalJagani — Thu, 13 Jun 2019 06:03:07 GMT

Thanks @FrankVl for correction in logic. Thanks @lewisgrantevans now I've updated my answer so other user also can easily find the solution.

I think this is very common day to day life logic problem, isn't it?

Re: How to search count by only business days?

lewisgrantevans — Thu, 13 Jun 2019 08:09:05 GMT

Amazing, thank you guys. It's now more accurate than the excel calculation. Much appreciated!