Separate business hours intervals from off-business-hours

[avi123]

I am new to splunk. How do we write a splunk query for a support ticket that is "In Progress" status to calculate the business hours elapsed by the ticket. We need to exclude the non-business hours of the weekday when the incident is "In Progress" status and also exclude the holidays and weekends.

[avi123]

how to calculate incident end day SLA hours in splunk? I mean if Inc_Resolved_Time="27.02.2024 08:00" and TeamWorkTimings="Mon-Fri 7AM to 6PM", then the end day SLA hours should be 1business hour.

Could you please help me with splunk query to achieve this result. I am trying to use this query but not getting proper results::

| eval Ending_Day_SLA_Hours=if(((incidentEndTime1-incidentStartTime1)<86400 AND Inc_Open_Days=Inc_Resolved_Actual_Days),0,if((Inc_Resolve_Date=Weekend_1 OR Inc_Resolve_Date=Weekend_2 OR Inc_Resolve_Date=Weekend_3 OR Inc_Resolve_Date=Weekend_4),0,if((incidentEndTime1>Inc_SLA_Start_Day_Epoch AND incidentEndTime1>Inc_SLA_End_Day_Epoch),(660-((incidentEndTime1-Inc_SLA_End_Day_Epoch)/1440))/60,if(incidentEndTime1<Inc_SLA_Start_Day_Epoch,11,0))))

[avi123]

please help me understand how to calculate the incident end/resolved date working hours (we might need to consider if the incident is closed on weekends) and the number of middle days excluding the holidays and weekend. Kindly help me with the Splunk query. Do we need to use mv commands like mvindex, mvexpand and mvjoin for calculating the number of middle days excluding the holidays and weekends?

[ITWhisperer]

If I were to approach it, I would take the total time difference and subtract evening and morning out of hours for each difference in days, then subtract working hours for each each weekend day and holiday date between the start date and the end date. I would use a lookup file for the holiday dates, with every holiday date you want to consider, each date have another field with a flag in. I would work out what the dates were by creating a multi-value field with all the intervening dates in. The lookup could then retrieve all the holiday flags and therefore be able to work out how many hours to deduct from the duration.

[ITWhisperer]

This is an individual usecase, i.e. it depends on your hours of business and holiday dates.

If I were to approach it, I would take the total time difference and subtract evening and morning out of hours for each difference in days, then subtract working hours for each each weekend day and holiday date between the start date and the end date. I would use a lookup file for the holiday dates, with every holiday date you want to consider, each date have another field with a flag in. I would work out what the dates were by creating a multi-value field with all the intervening dates in. The lookup could then retrieve all the holiday flags and therefore be able to work out how many hours to deduct from the duration.

[avi123]

Thanks for the approach, could you also please help me understand how to calculate the incident end/resolved date working hours (we might need to consider if the incident is closed on weekends) and the number of middle days excluding the holidays and weekend. Kindly help me with the Splunk query.

[avi123]

please help me with this use case scenario