How can I do SLA Reporting via Standard SBM listing Reports?

2 Likes
over 6 years ago
SLA information is not currently available in the standard listing reports and so there are a number of techniques you can use to help extend the SLA default reporting capabilities.
(1) Advanced SQL Conditions:

Using advanced query conditions you can run listing and distribution reports that interrogate information about SLA as part of their filter criteria. Use a listing report to get the list of items or distribution report to get a count grouped according to requirements e.g. fulfiller. The actual SLA data cannot be shown via this method but lists of items and counts satisfying SLA conditions can be easily produced. Please note that you will need to identify from primary tables ID for the below SQL i.e. The TS_ID in TS_TABLES for the primary table (in below examples it is 1011).

 Example 1: SQL Server – Service Request Violations in last 30 days.

 @where TSM_SERVICE_REQUESTS.TS_ID in (SELECT ITEM_ID FROM SLA_ITEM where RISK_CATEGORY='V' and START_DATE between getdate()-30 and getdate() and ITEM_TABLE_ID=1011)

 Example 2: Oracle – Service Request Violations in last month

@where TSM_INCIDENTS.TS_ID in (SELECT ITEM_ID FROM SLA_ITEM where RISK_CATEGORY=’V’ AND START_DATE >= add_months(trunc(sysdate,'mm'),-1)   and START_DATE < trunc(sysdate, 'mm' and ITEM_TABLE_ID=1011) Normal 0 false false false EN-GB X-NONE X-NONE
Example 3: SQL Server– Completed items not violated
@where TSM_SERVICE_REQUESTS.TS_ID in (SELECT ITEM_ID FROM SLA_ITEM where (RISK_CATEGORY='H' OR RISK_CATEGORY='L' OR RISK_CATEGORY='M') and ACTUAL_COMPLETION_DATE is not NULL and START_DATE between getdate()-30 and getdate() and ITEM_TABLE_ID=1011)
NOTE: This SQL is provided "as is" and works with SSM 5.1 and below. Please ensure that on upgrades this still works as expected as Serena reserves the right to change the database schema in future releases.

(2) Adding an SLA Status Field to the Primary Table:

Instead of using Advanced Query Conditions used in (1) above you can add a field to your primary table and use the SLA actions to simply update this using the standard update transition as the item progresses through the SLA Risk levels. This field can then be used in SBM reports as per a standard SBM field.

(a)    Add a single selection or binary/trinary field to field to your primary table e.g. SLA_STATUS (note that if each item can have multiple SLA’s then you would need one field for each SLA).

(b)     On the SLA definition add a new action(s) to run the standard “update” transition and set the value of this field accordingly.

Example: The example below sets a single selection field to a value of “violated” if the  SLA is “violated”. This would then enable all items violating their SLA to be easily identified and manipulated using standard SBM reporting techniques. This can be made more sophisticated if required by adding additional actions at the different SLA risk stages. Please note that the notification server is used to run these transitions and so must be running and able to take the additional load required.
Action for the SLA is defined as below:
Notification which runs the standard “update” transition and then sets the field to violated.
Comment List
Anonymous
Related Discussions
Recommended