Splunk Search

How to search count by only business days?

Explorer

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)
``````

Tags (4)
1 Solution
SplunkTrust

@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!!!!

Explorer

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)

Thanks again!
Lewis

Ultra Champion

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.

Ultra Champion

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.

SplunkTrust

@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!!!!

Explorer

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

Ultra Champion

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.

Ultra Champion

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))
``````
Explorer

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

Ultra Champion

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)
``````
SplunkTrust

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?

Explorer

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

Explorer

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

Ultra Champion

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

Splunk Employee

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