Splunk Search
Highlighted

How to search count by only business days?

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.

Highlighted

Re: How to search count by only business days?

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
Highlighted

Re: How to search count by only business days?

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 ctime=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
Highlighted

Re: How to search count by only business days?

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.

Highlighted

Re: How to search count by only business days?

Motivator

@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

Highlighted

Re: How to search count by only business days?

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
Highlighted

Re: How to search count by only business days?

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
Highlighted

Re: How to search count by only business days?

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
Highlighted

Re: How to search count by only business days?

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

Highlighted

Re: How to search count by only business days?

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)