topic Re: How do you exclude weekends from the calculation of expected end time? in Splunk Search

How do you exclude weekends from the calculation of expected end time?

louisawang — Tue, 05 Feb 2019 14:18:34 GMT

I am doing a support ticket with 4 levels of severity.

Level 1 expects the ticket to be resolved in 4 hours
Level 2 expects the ticket to be resolved in 8 hours.
Level 3 expects the ticket to be resolved in 72 hours aka 3 days.
Level 4 expects the ticket to be resolved in 120 hours aka 5 days.

I have the average response time, and the created time. But the response time includes weekends when calculating when the ticket is expected to be resolved.

So when a ticket is raised on a Thursday with level 4 severity, it should be expected to be solved by Next Wednesday. However, my code now included Saturday and Sunday into the calculation, resulting it to be resolved by next Monday instead.

How do I exclude Saturday and Sunday out when calculating the expected time?

 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 SLA_DEADLINE = strftime(SLA_DEADLINE,"%Y-%m-%d %H:%M:%S") 
    | table *

So for this picture, on 2019-01-18(Friday), the Severity is level 4 and the Deadline is 2019-01-23, which is not what I wanted because it included Saturday and Sunday inside. It should be 2019-01-25 instead.

This question took me days, and I still have not solved it yet. It would be great if someone could help me. The answers in the forum only filters out weekends, so if the ticket ends in a weekend, it will not show. This is not what i wanted.

Re: How do you exclude weekends from the calculation of expected end time?

vishaltaneja070 — Tue, 29 Sep 2020 23:10:14 GMT

Hello @louisawang,

I would suggest to create one more field based on create_time_epoch which will tell you the day of ticket and based on that do the changes like below:

  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=="Monday",0, (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 = strftime(SLA_DEADLINE,"%Y-%m-%d %H:%M:%S") 
     | table *

Re: How do you exclude weekends from the calculation of expected end time?

louisawang — Wed, 06 Feb 2019 06:57:27 GMT

Hello, Thank you, it worked, partially. If the ticket is raised on a Monday, the SLA deadline is on a Saturday. How do i make it to be on Monday instead? So for a create_time of 28/1/2019, it ends at 2/2/2019, which is a Saturday. How do i skip the 2 weekends? For the rest of the days, it works 🙂
And another question to add on to this, is it possible to make it to business hours as well? The business hours is 8am to 8pm ( 12 hours). so for SLA level 4, 120 hours actually means 10 days instead of 5 days.

Re: How do you exclude weekends from the calculation of expected end time?

vishaltaneja070 — Tue, 29 Sep 2020 23:10:17 GMT

@louisawang

| eval sum= case(day_of_week=="Monday",0, (day_of_week=="Tuesday" OR day_of_week== "Sunday"), 86400, 1=1, 172800)

This is setting which is added. So For monday i have set not to change. Can you give me list,
like Monday ----> SLA end ----> Sat

I will change it accordingly.

For SLA based on business hours, you can change it as well. The numbers 86400, 172800 is seconds, which you can change based on your requirement.

Re: How do you exclude weekends from the calculation of expected end time?

louisawang — Wed, 06 Feb 2019 07:14:37 GMT

Thank you for your help, so for a create_time of 28/1/2019, it ends at 2/2/2019, which is a Saturday.

Re: How do you exclude weekends from the calculation of expected end time?

louisawang — Wed, 06 Feb 2019 07:18:26 GMT

oh and for the Business hours, i will elaborate a bit more. Sometimes, a ticket is raised on a weekend, but the start date is a monday, 8am. For example, a ticket is raised on saturday 10am, the SLA timing should start from Monday 8am.
Also, sometimes the ticket is raised outside of business hours(business hours is 8am to 8pm). Some tickets are raised at 9pm. So how do i start the SLA from next day 8am? Thank you!

Re: How do you exclude weekends from the calculation of expected end time?

vishaltaneja070 — Tue, 29 Sep 2020 23:10:23 GMT

Try to use this then:
| eval sum= case( (day_of_week=="Tuesday" OR day_of_week== "Sunday"), 86400, 1=1, 172800)

Re: How do you exclude weekends from the calculation of expected end time?

louisawang — Tue, 29 Sep 2020 23:10:26 GMT

thank you! it works 🙂
So for the rest of the SLA, i just have to add
| 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) ?