Splunk Search
Highlighted

Exclude weekends when calculating expected end time

New Member

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.
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 *

SLA DEADLINE
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.

0 Karma
Highlighted

Re: Exclude weekends when calculating expected end time

SplunkTrust
SplunkTrust

See if this answer helps: https://answers.splunk.com/answers/421889/how-can-we-exclude-weekends-from-the-count-of-the.html

---
If this reply helps you, an upvote would be appreciated.
0 Karma