How to calculate total Business hours in between weekend days?

[SapthagiriAavik]

Example: if Friday by 02-FEB-2018 23:00 a ticket got recorded and resolved on monday 05-FEB-2018 20: 00. So I want to exclude weekend hours in SLA. My query is here

index=hr_Pandora  source=Pandora_interaction Ticket_status="Closed" METHOD="Phone" |eval call_close=strptime(CLOSE_TIME, "%Y-%m-%d %H:%M:%S")  
|eval call_open=strptime(OPEN_TIME, "%Y-%m-%d %H:%M:%S")|eval diff=(cal_close-cal_open)/3600
|eval time_variance=if(diff<=24,1,0) |stats avg(time_variance) as Average |eval Average =round(Average*100,2) ."%"

[bonnlbbelandres]

Thank you for this example. I just would like to ask if there is a similar approach to this but is able to cater around 100 000 events without much hassles on performance? Peace

[elliotproebstel]

This answer is a lightly modified version of this clever approach by @javiergn.

The run-anywhere code below takes the sample date/time values from your post and assigns them to start_time and end_time and then calculates the number of "business hours" (in minutes):

| makeresults 
| eval start_time="02-FEB-2018 23:00" 
| eval close_time="05-FEB-2018 20:00" 
| eval start_time_epoch = strptime(start_time,"%d-%b-%Y %H:%M") 
| eval close_time_epoch = strptime(close_time,"%d-%b-%Y %H:%M") 
| eval minute = mvrange(0, (close_time_epoch - start_time_epoch), 60) 
| mvexpand minute 
| eval _time = start_time_epoch + minute 
| eval myHour = strftime(_time,"%H") 
| eval myMinute = strftime(_time,"%H") 
| eval myDay = strftime(_time,"%A") 
| where myDay != "Saturday" AND myDay != "Sunday" AND myHour >= 9 AND (myHour < 17 OR (myHour = 17 AND myMinute = 0)) 
| stats count as durationInMinutes by start_time, close_time 
| eval duration = tostring(durationInMinutes*60, "duration")

[grittonc]

This is awesome and I used it and modified it for my own purposes (counting business days in a month). But users might want to know that the mvrange function will stop before it reaches the value in the second argument if it is an exact multiple of the third argument (see | makeresults| eval foo=mvrange(0, 10, 2)).

This is significant only if you want to make sure that the last value is counted -- in this example, the minute that occurs at the close_time. This search will count up to and including 19:59, which is probably the intent of the search. But if you are counting business days in a month, and the last day of the month is a business day, you'll want to add +1 to the mvrange to force the mv field to include the last in . See | makeresults| eval foo=mvrange(0, 11, 2) for counting to 10 by increments of 2.

Oops, and noticed a typo in line 10 -- do you mean that to be %M for minutes?

Great example, and thanks much!