Exclude weekends and business hours to calculate duration of a support ticket

[louisawang]

I am creating a support ticket for my project. When a ticket is raised, it has 4 levels of severity(how long to solve the issue).
Sev 1 will have a 4 hour period, sev 2 = 8 hours, sev 3 = 72 hours and sev 4 = 120 hours.
I am referencing from this post: https://answers.splunk.com/answers/69820/search-to-only-include-business-hours-and-exclude-weekends....
but where do i input the "date_wday!="saturday" AND date_wday!="sunday" | eval myHour=strftime(_time, "%H") | where ( myHour <= 18 AND myHour > 5 )" into my codes? This is my code for the project:

index="test" sourcetype="incident_all_v3" 
| eval check = strptime(strftime(_time , "%d/%m/%Y") , "%d/%m/%Y") 
| eventstats max(check) as checktime 
| where checktime = check 
| dedup 1 ticket_id sortby -_time 
| join ticket_id type=left 
    [ search index="test" sourcetype="incident_assigned_ams" 
    | eval check = strptime(strftime(_time , "%d/%m/%Y") , "%d/%m/%Y") 
    | eventstats max(check) as checktime 
    | where checktime = check 
    | eval move_datetime = strptime(move_datetime, "%Y-%m-%d %H:%M:%S") 
    | dedup 1 ticket_id sortby -move_datetime 
    | eval move_datetime = strftime(move_datetime, "%Y-%m-%d %H:%M:%S") 
    | fields ticket_id move_datetime] 
| eval realtime = if(isnotnull(move_datetime), move_datetime, create_time) 
| eval create_time_epoch = strptime(realtime, "%Y-%m-%d %H:%M:%S") 
| lookup inc_app_name.csv queue_name output vendor, app_name 
| search vendor = "Company" AND ticket_type = "Incident" AND app_name = "*" 
| eval diff_seconds = now() - create_time_epoch 
| eval diff_days = diff_seconds / 86400 
| eval status = if (ticket_state="Closed" OR ticket_state="Completed" OR ticket_state="For Verification" OR ticket_state="Verified", "resolved" , "unresolved") 
| where status = "unresolved" AND ticket_type = "Incident" 
| eval SEVERITY = case ( SLA == "SLA Level 1", "1", SLA == "SLA Level 2", "2", SLA == "SLA Level 3", "3", SLA == "SLA Level 4", "4") 
| eval SEVERITY = "Sev ".SEVERITY 
| lookup sev_target.csv SEVERITY output TARGET 
| eval SLA_DEADLINE = case(SEVERITY = "Sev 4", create_time_epoch + (TARGET*3600), SEVERITY = "Sev 3", create_time_epoch + (TARGET*3600), SEVERITY = "Sev 2", create_time_epoch + (TARGET*3600), SEVERITY = "Sev 1", create_time_epoch + (TARGET*3600)) 
| where SLA_DEADLINE <= relative_time(strptime(strftime(now(),"%d/%m/%Y"),"%d/%m/%Y") , "+4d") AND SLA_DEADLINE >= strptime(strftime(now(),"%d/%m/%Y"), "%d/%m/%Y") 
| append 
    [| stats count 
    | fields - count 
    | eval SLA_DEADLINE=strptime(strftime(now(),"%d/%m/%Y"),"%d/%m/%Y") ] 
| append 
    [| stats count 
    | fields - count 
    | eval SLA_DEADLINE=relative_time(strptime(strftime(now(),"%d/%m/%Y"),"%d/%m/%Y") , "+1d") ] 
| append 
    [| stats count 
    | fields - count 
    | eval SLA_DEADLINE=relative_time(strptime(strftime(now(),"%d/%m/%Y"),"%d/%m/%Y") , "+2d") ] 
| append 
    [| stats count 
    | fields - count 
    | eval SLA_DEADLINE=relative_time(strptime(strftime(now(),"%d/%m/%Y"),"%d/%m/%Y") , "+3d") ] 
| eval SLA_DEADLINE = strftime(SLA_DEADLINE,"%d/%m/%Y") 
| stats count, avg(diff_days) as avg by SLA_DEADLINE 
| eval count=count-1 
| eval average = round(avg,2)." Days" 
| eventstats sum(count) as total 
| eval perc = round(count*100/total,2)."%" 
| eval perc = count." (".perc.")" 
| table SLA_DEADLINE, average, perc 
| sort -perc 
| sort SLA_DEADLINE 
| rename SLA_DEADLINE as "Incident SLA Deadline", average as "Avg Duration", perc as "Count (Percentage)" 

Right now, it is 24 hours, 7 days a week. How do i make it to 5 days a week and business hours(8am to 8pm)?
If a ticket is raised on friday 10am with SLA 3, it should be solved by Wednesday 10am.

[efavreau]

@louisawang You were on the right track. For performance reasons, the less information Splunk has to parse, the better. Which means that whenever possible, you should would put as much into your search as you can and before the first pipe. When doing so, use the terms native for Splunk. Change line 1 to something like:

index="test" sourcetype="incident_all_v3" date_wday!="Saturday" AND date_wday!="Sunday" AND date_hour>7 AND date_hour<17 

This will limit your data to weekdays, between 8:00 (eliminating all times with an hour of 7:##) and 16:59:59 (excluding 17:00), before even getting to the first pipe, which keeps things very fast. You can then use the data returned however you'd like.

[louisawang]

I've tried using it but its not working 😞 The thing is there is this create_time which records when the ticket is created, and SLA_DEADLINE is determined by the create_time_epoch + Severity of SLA*3600, which then returns the date. So for example if create_time is 2019-01-25 00:00:00(Friday), the SLA_DEADLINE shows 30/01/2019, which took Saturday and Sunday included as well 😕
https://imgur.com/iXdo2Jy

[efavreau]

You asked

"...where do i input the
"date_wday!="saturday" AND
date_wday!="sunday" | eval
myHour=strftime(_time, "%H") | where (
myHour <= 18 AND myHour > 5 )" into my
codes? "

I answered the question asked. If there's more context and definition needed, please refine your question and provide that level of detail.

[louisawang]

edit 2: I found this answer interesting: https://answers.splunk.com/answers/620591/how-to-calculate-total-business-hours-in-between-w.html
This excluded Saturday and Sunday and the business times as stated in:
| where myDay != "Saturday" AND myDay != "Sunday" AND myHour >= 9 AND (myHour < 17 OR (myHour = 17 AND myMinute = 0))
but this is to calculate the duration. My question is to determine the end date from the start date and the duration, so how do i change the code from this answer to suit my question?

[louisawang]

edit: It should be solved by Friday 10am considering business hours to be 12 hours and exclude Weekends