SLA data in Standard Listing Reports
This blog is a follow up to a topic presented at xChange2015. It presents a 3rd solution to the dilemma of how to use native SBM reports to show listings of items alongside current SLA data. While the two solutions offered in previous blog entry (How can I do SLA Reporting via Standard SBM listing Reports?) offered viable solutions, there were some limitations to both.
v Only view SLAs on Incident by Incident basis
v Bring SLAs into Incident Dashboards
v Customizable out of box SLA Reporting
The approach I'm proposing below will provide some additional benefits and flexibility to generate many different listing reports.
1. First, it allows you to display more than just primary table data but also show SLA data with it which can be sorted and highlighted.
2. Second, another benefit is an item might have more than one active SLA (ie. Time to Respond and Time to Restore for an Incident) and this will display data for both SLAs. This approach accounts for multiple SLAs.
3. Third, the underlying SLAs themselves have all this great date and time information for ‘time to next level’ and ‘time until violation’ so why not show calculations based on such useful detail?
v SLA Data in Aux table
v Kept Current via DB Triggers
v SBM Join Reports
Below is a high level description of the pieces needed to implement SLA listing reports.
2. Implement Insert and Update DB triggers against the SLA_ITEM table to insert into the new SLAs as well as keep it current. The example I included limits the tables from which I would like to receive real time SLA data which limits the size of the auxiliary table. Great caution must be taken to limit when the update trigger is called. I chose changes to specific fields since these rows get updated with every cycle of the SLA processing which is I believe something like 30secs in our environment. The triggers included in the solution were built in a SQL Server DB but similar triggers could be written in Oracle.
3. Create a report against the SLAs table and Join with your desired primary table. This example just shows relationships with Incidents but could easily be extended to work with SRs or any other table. You will just need to create a Single Relational field for each primary table for which you’d like to capture SLA data. Due to some limitations with join reports and sorting you may need to also include sub-relational fields like I did for Incident Priority. This was done since I wanted to sort not just by SLA times or Risk Levels but also by Priority of the Incident. The report examples shown below use a custom html template as well as some js/css for extra manipulation.
This only shows a couple basic options which were initially delivered. However now that we have easier access to the SLA data there are many more possibilities to deliver based upon that data. I'm interested for others to share ideas or thoughts not yet considered. For those who got to see the live demonstration at xChange2015 thank you for your participation and patience in getting this blog out there.
v Focus on most Urgent & Relevant Incidents
v Flexibility to sort by Incident Attributes AND SLA Risk Level
v Display Actual Times
Ø Until Next Risk Level
Ø Until Risk in Violation
v ROI – Improved SLA performance è TBD…..
***[Special Thanks to Sam Sabra for the custom template and supporting js/css]***
Keep in mind that while we have tested this and began using in our Production environment it comes without warranty or support. So buyer beware. All rights reserved, and most lefts too.
Supporting Files located in the following zip.