Splunk Search

How to search count by only business days?

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

Many thanks in advance.

1 Solution

VatsalJagani
SplunkTrust
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!!!!

View solution in original post

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

alt text

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

0 Karma

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

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

VatsalJagani
SplunkTrust
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!!!!

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

0 Karma

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

0 Karma

FrankVl
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))
| eval noOfBusinessDays = if(floor(totalDays % 7) != 0, case(createdW = 7, noOfBusinessDays-1, todayW = 7, noOfBusinessDays-1, todayW < createdW, noOfBusinessDays-2, 1=1, noOfBusinessDays), noOfBusinessDays)
0 Karma

lewisgrantevans
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

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

VatsalJagani
SplunkTrust
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?

lewisgrantevans
Explorer

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

0 Karma

lewisgrantevans
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

0 Karma

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

kmorris_splunk
Splunk Employee
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"
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 ...