topic Re: skip days and hours non-working to calculate SLA in Splunk Search

skip days and hours non-working to calculate SLA

lufermalgo — Mon, 28 Sep 2020 17:03:21 GMT

Hi all,

I am building an application to analyze my platform help desk, basically what I want is to build reports on compliance with the SLA, I have several clients and several SLAs Example: 7x24, 5x8, etc ... the dates of the tickets was unix format so that the calculation of direncia creating closure is provided, but as I can skip these days and times do not work according to the SLA calculations.

Ej:

index=prueba sourcetype=otrs state_ticket=closed | eventstats max(change_time) as lasttime by tn | dedup tn | convert ctime(lasttime) AS closing_date ctime(create_time_unix) AS create_ticket | eval timediff=(change_time-create_time_unix) | eval hour=floor(timediff / 3600) | eval min=floor((timediff / 60) % 60) | eval duracion=hour.":".min | table tn,client,ticket_queue,priority_ticket,state_ticket,create_ticket,closing_date,agent,duration | sort -duration

this query tells me the time takes me a ticket since its creation in transition to the closing.

If someone can give me a hand, thanks for that.

Re: skip days and hours non-working to calculate SLA

musskopf — Mon, 28 Sep 2020 17:03:24 GMT

You can use couple o thing, maybe use date_wday together with date_hour to filter your results and will be enough for you...

index=test AND (date_wday="monday" OR date_wday="tuesday" OR...) AND (date_hour>=8 AND date_hours<=18) | stats count AS tickets_business_hours

But a more sophisticated approach might be using a lookup table to check those values and return what sort of SLA it fits in. For example, Create a csv like:

date_wday, date_hour, sla
monday, 1, "24x7"
monday, 2, "24x7"
(...)
monday, 8, "8x5"

and use it as lookup:

index=test | lookup sla_periods date_wday, date_hour OUTPUT sla | count by sla

You can even do another lookup with the holiday after that, which will override the sla from the first lookup.

Hope it helps!

Re: skip days and hours non-working to calculate SLA

martin_mueller — Mon, 14 Jul 2014 07:33:32 GMT

The lookup approach has a big issue - performance. To determine whether an event is within the SLA period or not you need to load it, add the lookup, and then filter.

I'd built a couple of macros, e.g. 5x8:

((date_wday="monday" OR ... date_wday="friday") AND date_hour>=9 AND date_houry<17)

Use that in searches like this:

`5x8` AND index=test | ...

This way events outside the SLA rage don't even need to be loaded off disk.

Re: skip days and hours non-working to calculate SLA

lufermalgo — Mon, 28 Sep 2020 17:03:44 GMT

thanks musskopf and martin_mueller for the replies,

I explain my situation a little more, the two ideas are very good but I think I'm wrong in asking the question.

The SLA is defined in hours (Client 1 - 1 hour SLA, SLA Client 2 4 hours, etc. ..), and some of our customers have contracts 7x24 or 5x8, which I require to seek the best way to calculate the closing time of tickets depending on the SLA and the type of contract.

Ej:
business hours of the agents is from 8AM to 6PM, 7x24 for the contract serves other staff.

name_client SLA_hours contract
client1 2 7x24
client2 4 5x8

tn client create_time change_time status_ticket

1111111 client1 1405119600 1407798000 closed

2222222 client2 1405119600 1405350000 closed

not meet the SLA client1 and client2 meets SLA

for client2 should exclude Saturday and Sunday's hours on Friday night and early Monday. ie if the tiequete was created at 6PM on Friday and closed on Monday at 8AM time was 2 hours and the SLA was fulfilled.

Re: skip days and hours non-working to calculate SLA

musskopf — Mon, 28 Sep 2020 17:04:08 GMT

So, in my view you'll need to use the lookup approach, having a table like that:

client, date_wday, date_hour, SLA
clientA, monday, 1, 2
clientA, , 2, 2
(...)
clientB, , 8, 4

After the lookup you'll have the SLA column for each event, where you can use a eval to define if the ticket met the SLA or not... could of IFs or CASEs.