CA Service Management

Xtraction version 2016.2.6037.958

CA Service Desk Manager version 14.1.03

I have an employee who is developing SLA reports.  Using the 'CA Service Desk \ Incidents' data source and creating a list report beginning with the system default columns.  This is returning the results as expected.  He added the 'SLA Violation (Yes/No)' and the results count was the same and displayed the new field as well.  But when he added the 'SLA Remaining' field, the results are reduced to one record and it has a negative value (-17380 d -17 -37 m).

I looked up the calculated field for this in the data model and it is taking the difference in seconds between the current time and the fire time on the attached events table (att_evt) for the specific incident where the event status value is 2,3,12 or 13..  This one ticket is currently on hold and the service event (4 hr cr resolution violation) is marked delayed.  The Delay Remaining shown on the incident is: 00:26:52

There are four events associated with this ticket and only the '4 hr cr resolution violation' event matches the event status value (13).  But the fire time for this event is 1612 (1969-12-31 20:26:52.000 (offset for EDT).  I figured this is why we are getting a negative value for 'SLA Remaining'.  This is also the value for 'SLA Expiry Date'.

There are two other active incidents which have event status values of 12 and also have fire times in low epoch times but they do not show up in the report results.

I don't see anything in the service event's definition that would cause this but could always use another set of eyes.

The employee's first question is why adding these fields act as a filter down to one record when there appear to be others that should be included and the second is why the results are negative.

TIA,

J.W.

I stumbled upon this related discussion:

Is there any way to calculate remaining time for SLA violation in BOXI? 

I don't know how I missed it in my searches, I used some variation on those tags.  Oh, well.

In any event, the Xtraction calculated value syntax is:

CAST((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) / 86400) AS varchar(10)) + ' d ' + CAST((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) % 86400 / 3600) AS varchar(10)) + ' h ' + CAST(((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) % 86400) % 3600 / 60) AS varchar(10)) + ' m'

But this is returning the negative value when status is 12 or 13 (Delayed or Delayed Repeating) because the fire_time is seconds for Delay Remaining and not a date timestamp which is what the above code is assuming.

I was able to verify the following works when the status is 12 or 13:

CAST((SLA_EVENT_ATTRIBUTES.FIRE_TIME / 86400) AS varchar(10)) + ' d ' + CAST((SLA_EVENT_ATTRIBUTES.FIRE_TIME % 86400 / 3600) AS varchar(10)) + ' h ' + CAST(((SLA_EVENT_ATTRIBUTES.FIRE_TIME % 86400) % 3600 / 60) AS varchar(10)) + ' m'

this returns '00 d 00 h 26 m', which matches the displayed value on the ticket (to the minute).

So, I updated the expression for the calculated value of 'SLA Remaining' using this CASE statement::

CASE WHEN SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 2 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 3 THEN CAST((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) / 86400) AS varchar(10)) + ' d ' + CAST((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) % 86400 / 3600) AS varchar(10)) + ' h ' + CAST(((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) % 86400) % 3600 / 60) AS varchar(10)) + ' m' WHEN SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 12 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 13 THEN CAST((SLA_EVENT_ATTRIBUTES.FIRE_TIME / 86400) AS varchar(10)) + ' d ' + CAST((SLA_EVENT_ATTRIBUTES.FIRE_TIME % 86400 / 3600) AS varchar(10)) + ' h ' + CAST(((SLA_EVENT_ATTRIBUTES.FIRE_TIME % 86400) % 3600 / 60) AS varchar(10)) + ' m' ELSE 'N/A' END

Next up:  Modifying the check for 'SLA Expiry Date' (fire_time) and 'SLA Breach Warning' to account for this as.well.

J.W.

Modified Breach Warning code.

This follows the previous example except when the event status is 12 or 13, then just the word 'Delayed' is returned.

CASE WHEN SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 2 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 3 THEN
CASE WHEN (SLA_EVENT_ATTRIBUTES.FIRE_TIME - DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())) < 600 THEN '01.  < 10 Mins' WHEN (SLA_EVENT_ATTRIBUTES.FIRE_TIME - DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())) < 1800 THEN '02.  10 Mins - 30 Mins' WHEN (SLA_EVENT_ATTRIBUTES.FIRE_TIME - DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())) < 3600 THEN '03.  30 Mins - 1 Hour' WHEN (SLA_EVENT_ATTRIBUTES.FIRE_TIME - DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())) < 7200 THEN '04.  1 - 2 Hours' WHEN (SLA_EVENT_ATTRIBUTES.FIRE_TIME - DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())) < 10800 THEN '05.  2 - 3 Hours' WHEN (SLA_EVENT_ATTRIBUTES.FIRE_TIME - DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())) < 14400 THEN '06.  3 - 4 Hours' WHEN (SLA_EVENT_ATTRIBUTES.FIRE_TIME - DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())) < 18000 THEN '07.  4 - 5 Hours' WHEN (SLA_EVENT_ATTRIBUTES.FIRE_TIME - DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())) < 28800 THEN '08.  5 - 8 Hours' WHEN (SLA_EVENT_ATTRIBUTES.FIRE_TIME - DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())) < 43200 THEN '09.  8 - 12 Hours' ELSE '10.  > 12 Hours' END
WHEN SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 12 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 13 THEN
'Delayed' END

Modified SLA Expiry Date code.

Same logic except this is a date field, so setting this to NULL rather than the string 'Delayed'

CASE WHEN SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 2 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 3 THEN SLA_EVENT_ATTRIBUTES.FIRE_TIME
WHEN SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 12 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 13 THEN NULL
END

I'd be interested in other people's use cases and format decisions.

J.W.