CA Service Management

  • 1.  remaining time for sla violation

    Posted Feb 02, 2016 06:39 AM

    Hi all,

     

    I was wondering if there is an easy way to calculate remaining sla time before violation of SLA?

    On my definitions for service types, there are lots of condition based events running on specific times but I couldn't seperate the event which marks the ticket as sla_violated.

    If I can get the remaining time, I want to use it to create some charts shown on Dashboards.

     

    Regards,

    Utku



  • 2.  Re: remaining time for sla violation

    Posted Feb 02, 2016 07:26 AM

    Hi Utku,

    here is my spel way:

    - fetch all attached events with violate_on_true=1 event template (make sure that you have defined that type of event templates);

    - sort them by fire_time;

    - check condition using this macro: Check condition macro via spel;

    - profit

     

    Regards,

    Timur



  • 3.  Re: remaining time for sla violation

    Posted Feb 02, 2016 07:43 AM

    Hi Timur,

     

    is there any SQL approach to find out the same value?

    I'm trying to calculate remaining time in order to show tickets near to SLA breach on LCD with some graphical reporting interface



  • 4.  Re: remaining time for sla violation

    Posted Feb 02, 2016 08:25 AM

    I think there is no easy way to check condifition via SQL,

    but overal query will be like this:

    select * from mdb.dbo.att_evt where obj_id = 'cr:4163385' AND event_tmpl IN (
    select persid from mdb.dbo.evt where violate_on_true = 1
    ) AND status_flag = 2
    order by fire_time desc
    


  • 5.  Re: remaining time for sla violation

    Posted Feb 02, 2016 08:30 AM

    but some events have conditions those match or not match with tickets. For example, I have 2 events behind same priority value with violating SLA but one of them is for Requests and one of them is for Incidents.

    As i understand, there isn't any unique value for that time



  • 6.  Re: remaining time for sla violation

    Posted Feb 02, 2016 08:41 AM

    Bad thing is that I haven't seen how SLA are works in OOTB environment

    In your situation I see only 2 ways:

    - publish custom attribute which will contain actual SLA violation date;

    - map events via SQL functions, "if" contsratints, etc...

     

    But I hope community's great minds will give you better suggestions

     

    Regards.



  • 7.  Re: remaining time for sla violation

    Posted Feb 13, 2018 01:27 PM

    Hi cdtj,

     

    I was working on the same thing using sql. And this is what I came up with... Hopefully it might help if you have not already found a solution

     

    select
    ticket_id
    ,mapped_cr
    ,srv_desc.sym as [Service Type]
    ,srv_desc.Description
    ,slatpl.sym as [Service Type Event]
    ,case
    when status_flag = 0 then 'Cancelled'
    when status_flag = 1 then 'Complete'
    when status_flag = 2 then 'Pending'
    when status_flag = 3 then 'Repeating'
    when status_flag = 1 then 'Complete'
    when status_flag = 12 then 'Delayed'
    when status_flag = 13 then 'Deplayed Repeating'
    end as Status
    ,case
    when sla_viol_status = 0 then 'Not Violated'
    when sla_viol_status =1 then 'Violated'
    end as [Violation Status]
    ,convert(varchar(20), dateadd (ss, last_ttv_upd -21600, '1/1/1970') , 101) + ' ' + + LTRIM(RIGHT(CONVERT(CHAR(20), dateadd (ss, last_ttv_upd -21600, '1/1/1970'), 22), 11)) as [Evaluated On]
    ,convert(varchar(20), dateadd (ss, start_time -21600, '1/1/1970') , 101) + ' ' + + LTRIM(RIGHT(CONVERT(CHAR(20), dateadd (ss, start_time -21600, '1/1/1970'), 22), 11)) as [SLA Start Time]
    ,case
    when status_flag = 12 or status_flag = 13 then null
    else convert(varchar(20), dateadd (ss, fire_time -21600, '1/1/1970') , 101) + ' ' + + LTRIM(RIGHT(CONVERT(CHAR(20), dateadd (ss, fire_time -21600, '1/1/1970'), 22), 11))
    end as [Expiration Time]
    ,case
    when status_flag = 12 or status_flag = 13 then
    CAST((fire_time/ 86400) AS varchar(10)) + ' d ' + CAST((fire_time % 86400 / 3600) AS varchar(10)) + ' h ' + CAST(((fire_time % 86400) % 3600 / 60) AS varchar(10)) + ' m'
    else null
    end as [SLA Remaining]
    ,num_fire
    -- ,*
    from attached_sla sla -- ticket's attached service type
    join srv_desc ON srv_desc.persid = sla.map_sdsc -- service type' description table
    join -- links service type's descriptions (srv_desc) with SLA's attached events (att_evt) and filters for violations only
    ( select sym, service_type, event from slatpl where object_type = 'cr' and sym like '%Violation'
    ) slatpl on slatpl.service_type = sla.map_sdsc
    join att_evt ae on ae.event_tmpl = slatpl.event and ae.obj_id = sla.mapped_cr -- service type sla events
    where mapped_cr in
    (
    select persid from call_req where sla_violation > 0
    )