CA Service Management

Our help desk is trying to get a report that would determine exactly how many tickets they have been able to resolve without transferring or escalating the ticket to another group.  This question was answered by a great post found at : https://communities.ca.com/docs/DOC-231172424 but I was wondering if there was a simpler way to determine if a ticket has been moved since the first group assignment. 

We are in the process of implementing the linked changes but since it requires:

   -a full Service Desk services stop, publish, and restart

   -creating of the aliases to add the fields to the tickets

   -creating instances within the reporting universe / configuring for Jaspersoft

I was thinking there might be an easier way to find this.  I don't necessarily need to know the previous group, just if the ticket has moved groups since it was assigned the first time.

Hi Richard, Unfortunately you would have to query (possibly by way of reporting of some sort) the activity history to see when the group was changed.  Its difficult to do because there are probably a TON of activities for each ticket and you would need to filter out all of them for field updates being group.  Would be a very heavy query to run against.  

Not sure if anyone else has come up with something like that.

Regards,

Jon I.

Hi Richard,

There is a field you can report on named 'Sum Transfer Activity'.  It shows the number of times a ticket has a 'transfer' activity logged.  But there's a problem with using this field as a consistent measure.  When a ticket is manually created by an analyst, assigned to themselves, and resolved or closed all on the first initial save, that field will have a '0' - no transfers.  But if they create the ticket and save it, then edit it and assign it to themselves, the action of assigning it to themselves after the initial save creates a 'transfer' activity.  So that field will have a '1' - one transfer.  Also, if the ticket was electronically created initially as assigned to their group (employee interface and/or maileater), and they edit it, assign it to themselves and resolve/close it, that field will also have a '1'.  I wish this field would track assigning it to yourself before the initial save as a transfer as well, so that we could expect this field to consistently show a '1' if the 1st Level Help Desk analyst assigned it to themselves and resolved/closed it without any additional transfers.  Then you could at least say that if this field is > 1, it was transferred either to another team or to another analyst on the same team. 

We instead created a report that tracks our "First Level Resolution Rate" by looking at all tickets created that month having an activity log entry made by an analyst ID belonging to the Help Desk team (that is the number of tickets we handled in some way) -- then it looks to see how many of those are closed where the assignee field is an analyst ID belonging to the Help Desk team (that is the number of tickets we handled that we resolved/closed).  But it doesn't definitively mean they weren't transferred to some other group, then transferred back to our team before resolving/closing.

Tammy

Hey RichFTB

I thought of another way to accomplish this, it still involves customization but may be easier to report on.. It expands a bit in the out of the box idea that tzadell mentioned. 

Instead of saving the previous group to a custom srel field you could accomplish the same use case with the following.. 

Each time the group field changes,  increment an integer field on CR by one. Then when the record is closed, check to see if the integer is 1, and if so set a First Contact  Resolution field (Yes/no field) to Yes. This would be more user friendly as you can add this field to the Efficiency Tracking tab and not clutter up the rest of the form. This also aligns with the concepts of the other fields on the efficiency tracking tab. Then in your reporting solution, check for records that are closed with the First Response value set to No. 

This solution would give you an easy way to check if the ticket had first contact resolution, and if not how many group transfers it had. 

Hi Richard,

Essentially if a ticket has not been transferred, then there will be no 'TR' activity type for it in the act_log tabl, otherwise every time a ticket is transferred, this is noted in the act_log table.  The only thing you would need to determine is if the transfer was from an analyst to another analyst or if the transfer was a change of group and tally the appropriate counts. In my opinion if all you want is a report that would determine exactly how many tickets they have been able to resolve without transferring or escalating the ticket to another group, you should be able to pull this off with a SQL query or CABI.

You could even install the KPI options and get similar info from the kpi tables.

Hope this help?

Kind Regards,

Brian

You can also activate the KPI_TICKET_DATA option and be able to know exactly in which group the ticket has been

If we're talking about from a reporting perspective, there is another option.  Turn on audit logging (different from activity logs).  I believe the Options Manager options are audit_ins and audit_upd.  These will create entries in the audit_log table.

Entries with an aud_opr value of 3 are inserts, like new ticket created.

Entries with an aud_opr value of 1 are updates, changes to a field on the ticket.

If you're looking at incident/problem/request tickets, the field to join on is call_req_id

If you're looking at changes, the field to join on is change_id

If you're looking at issues, the field to join on is issue_id

When the group changes, attr_name = 'group' and then you can see before and after values in attr_before_val and attr_after_val

So if you do something like this...

Select ref_num, count(attr_name) "Group Transfer Count"

from call_req

        left outer join audit_log On call_req.persid = audit_log.call_req_id and attr_name = 'group'

Where ref_num = 'ticketnumber'

Group By ref_num

You would get the ticket number and the number of times the group changes since initial save.  If it never changed, it'll say 0.  If it did you'll get a count > 0.  Obviously you'd modify you're where clause to something more appropriate.

Hi,

I was browsing and looks this is active thread for my query.

We are looking for a cabi /sql query to extract the amount of time the ticket is spent in each group from Open to closed.

we have activated the usp_kpi detail, but need help if any one actual build a report based on the report to extract the actual time spent on each group.

Regards,

Sharath.M