Support ticket raised outside of business hours

[louisawang]

I have a support ticket system where people can submit their support tickets. The system is running 24 hours but the workers only work from 8am to 8pm,Monday to Friday. I have a create_time field which is when the ticket is created. So if a ticket is created on Monday 9pm, the create_time should be Monday 8am. If the ticket is created on Saturday, it should start on Monday instead.
Secondly, I have a SLA where Level 1 is 4 hours, level 2 is 8 hours. SLA refers to how long the support ticket must take to be solved. So if a support ticket of Level 1(4 hours) is raised on Monday, 7pm, the workers can only take 1 hour because they leave work at 8pm, and Tuesday 8am continue working on it, which means that the deadline should be Tuesday 11am. How do i do that?
This is my current script which is already able to skip weekends

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

      | eval day_of_week= strftime(create_time_epoch, "%A")
      | eval sum= case( (day_of_week=="Tuesday" OR day_of_week== "Sunday"), 86400, 1=1, 172800) 
      | eval SLA_DEADLINE = if(SEVERITY = "Sev 4", SLA_DEADLINE + sum , SLA_DEADLINE)
      | eval SLA_DEADLINE = if(SEVERITY = "Sev 3", SLA_DEADLINE + sum , SLA_DEADLINE)
      | eval SLA_DEADLINE = if(SEVERITY = "Sev 2", SLA_DEADLINE + sum , SLA_DEADLINE) 
      | eval SLA_DEADLINE = if(SEVERITY = "Sev 1", SLA_DEADLINE + sum , SLA_DEADLINE)       
      | eval SLA_DEADLINE = strftime(SLA_DEADLINE,"%Y-%m-%d %H:%M:%S") 
      | table *

[maciep]

@louisawang i wanted to follow up here. I saw that you may have commented at some point, but possibly deleted it. Did my answer help? I am using _time, but you can change that to whatever field you're using as long as you make it epoch first.

[maciep]

I didn't try to digest your search, but i think relative_time() is your friend here. Quick question just to verify though, in your post do you mean that if the ticket is created Monday at 9pm it should start on TUESDAY at 8am?

Not well tested but i think it should mostly work. If the ticket comes in off hours (hour >=20, hour< 8 ) or over the weekend (day 5/6/0), then adjust the start to the next work day at 8am. Get the initial end time by adding the appropriate hours based on the sla, then figure out how far past the 8pm that is. Again, check the day and adjust appropriately for off-hours/weekend and finally add the remainder back.

So if I didn't make any mistakes, this should result in start and end fields containing the official start and end times based on when the ticket came in and the SLA...using ctime()/table at the end to just see if the dates look right.

index="test" sourcetype="incident_all_v3"
| eval start_hour = tonumber(strftime(relative_time(_time, "@h"),"%H")), start_day=tonumber(strftime(relative_time(_time,"@d"),"%w")) 
| eval start = case(start_day=0 OR start_day=6 OR (start_day=5 AND start_hour>=20),relative_time(_time,"+1w@w1+8h"),start_hour < 8, relative_time(_time,"@d+8h"), start_hour>=20,relative_time(_time,"+1d@d+8h"), true(), _time) 
| eval end = if(SLA = "SLA Level 1",relative_time(start,"+4h"), relative_time(start,"+8h")), end_hour = tonumber(strftime(end,"%H")), end_day = tonumber(strftime(end,"%w")), end_rem = end - relative_time(start,"@d+20h"), end_rem = if(end_rem <0, 0, end_rem) 
| eval end = case(end_day = 6, relative_time(end, "+1w@w1+8h"), end_hour >=20, relative_time(end,"+1d@d+8h"), end_hour < 8, relative_time(end,"@d+8h"), true(),end), end = end + end_rem 
| convert ctime(start), ctime(end) 
| table start, end, SLA