Reporting - sys_audit table

[Mike_D2L]

Hi all,

my organization is running the incident management module for technical support and I'm looking to create some reports via the reporting module which I need some help with. Right now we have an Assignment Group field which changes when an incident is 'escalated' to the next tier of support. I'm interested in running a report which shows 2 different metrics:

1. Total number of 'escalations' for a time period (a week). This is defined when the Assignment Group changes values.
2. Total number of 'escalations' per analyst. This is for KPI's and skill gap analysis.

I don't seem to be able to get this via the Incident Table. So I've been exploring the sys_audit table. I have identified the UID's for each Assignment Group and am currently running a report which tracks changes to the Assignment Group - so I report on UID Old Value and UID New Value (Old value being the UID for for the Assignment Group 'Tier 1' and the New Value being the UID for the Assignment Group which = 'Tier 2'). I've tried setting the time period for this with the 'Created' specifying the specific week. The issue is that the 'Created' field is for when the incident was created, not when the Assignment Group changed.

I was hoping to get some ideas here of how I could report on this. I know it's somewhere as this information is contained in the 'Calendar' view of the incident which contains all the changes to the incident and when and who made the change. Problem is, I don't know where to access these values when doing reporting. Any insight or suggestions are welcomed. Thanks all!


Mike Mitchell
Senior Manager, D2L

[Marcus Fly]

Mike,

Have you tried metrics? You should be able to get what you want out of the incident_metric table. Also you will be able to define new metrics to report on going forward for the future. Keep in mind if you define a metric today it will only have data from the time you define it, it does not go back and get historical data.

On the left hand column Navigate to "Metrics -> Definitions". OOB there is MTRC00006 with a name of "Assignment Group" which keeps track of the field value duration of Assignment group. Once you open that metric you'll see in the related list below the "Metrics" and their values. This will allow you in your reporting to group by incident and see how it changed from group to group.

Then on your report add the field from the incident table "Reassignment count" field. that field lives on the base task table and is available for all tables that extend the task table. It will give you an integer value of how many times the assignment group changed.

I hope this resoles your reporting issue

[rohana]

Hi, MTRC00006 is very useful for getting incident hops information. How do we get the catalog task hops information? something like MTRC00006? any help is appreciated!

thanks!
rohaNa.