Who hung up first the call?
This question sometimes arises in companies that handle calls, and, for example, there are some complaints from customers that employee of your company, not listening to them, hang up. If you have a modern call center, it probably has the required report. But if such a report is available, then it will contain the necessary information only if the call was not transferred outside the call center. Usually, the call center does not have information on calls that have been transferred from it, for example, to company managers. If you need to have information on who first hung up on all calls and you are using Cisco Unified Communications Manager (CUCM) as your telephone system, we can suggest using Tariscope, which will allow you to get the information you need.
Tariscope system receives CDR and CMR from CUCM and allows you to analyze them in the views for calls.
To have information on who completed the call first, you need to specify in the Tariscope settings for CUCM that all CDR fields are saved, as shown in Figure 1.
Figure 1
It should be borne in mind that this will require additional disk space and the Tariscope database can quickly reach the limit if you use Microsoft SQL Server Express edition.
The usual view for calls in Tariscope does not have a field for displaying information on who first hung up. There is only information on the call termination cause code. But the relevant information can be found in the detailed information, which contains all the CDR fields. If you are interested in data on some individual calls, select them in the view and click on the Record Details icon on the toolbar. If you are interested in information on all calls, then click on the Details of all records icon. The call detail view can be displayed instead of the main view, or in a separate view, depending on what the user chooses. An example of a record detail view is shown in Figure 2.
Figure 2
As with the usual call view, this view allows you to select only those fields of interest for analysis. To identify who hung up first, analyze the origCause_value and destCause_value fields, which are highlighted in the figure. A value of 0 in the fields means which the party originated the call (origCause_value) or the party that received the call (destCause_value) hung up first. In this case, in the opposite field contains the value of 16 for normal call termination. For more information on call termination cause codes, see the article.
But this view may not always be convenient, because it does not contain information about the subscriber, which is in the usual view for calls. In Tariscope it is possible to combine the necessary information on the view of call details with the usual view for calls. A typical call view contains a large set of fields that are not used for each of the phone systems that are supported. Let us, for example, display the name of Number A or Number B, depending on which subscriber with Number A or Number B initiated the end of the conversation, in the field intended for the project code.
To do this, you need to execute the following SQL query:
ALTER VIEW dbo.viCalls as SELECT C.ID, PBXID, NodeID, RecType, CallDirection, CallType, Originator, OriginatorAuxId, Terminator, TerminatorAuxId, CONVERT(datetime2(0), CallDateTime) AS CallDateTime, CallSeconds, AccessCode, OriginalDialnumber, Dialnumber, OriginalCLID, CLID, DNIS, RingTimeSeconds, HoldTimeSeconds, ReleaseCause, NoAnswerReasonID, AuthCode, (CASE WHEN CCM.destCause_value = 0 THEN (CASE WHEN CCM.origCause_value = 0 THEN '' ELSE 'Number A' END) ELSE (CASE WHEN CCM.origCause_value = 0 THEN 'Number B' ELSE '' END) END) as ProjectCode, FromAbonentID, FromAbonentPlaneID, ToAbonentID, ToAbonentPlaneID, FromTelephoneID, ToTelephoneID, CategoryID, ParentCallID, Tarif, Cost, Cost2, FromDepartmentID, FromDepartment, ToDepartmentID, ToDepartment, FromTelephone, ToTelephone, CONVERT(datetime2(0), EndDateTime) AS EndDateTime, CallDate, CONVERT(datetime2(0), CallTime) AS CallTime, CallDuration, CallDurationDays, RingTime, HoldTime, NumberA, NumberB, FromAbonent, ToAbonent, ISNULL(ISNULL(FromAbonent, FromTelephone), NumberA) AS CallingParty,
ISNULL(ISNULL(ToAbonent, ToTelephone), NumberB) AS CalledParty
FROM dbo.viCallsInternal AS C
left join dbo.CallsCCM as CCM on C.ID = CCM.CallID
GO
The part of the SQL query that was added is highlighted in red.
This query can be executed by selecting in the menu: Additional options → SQL- queries and placing the above query, as shown in Figure 3.
Figure 3
Click the Execute button.
That is all there is to it. Then create a new view or add the Project code field to an existing call view. After that, the view will be like as shown in Figure 4.
Figure 4
As you can see in this figure, information is displayed on the party of the call that first hung up. Since we used the Project Code field to display this information, the previous column heading remains. It can be changed to anything, for example, "Who hung up". To do this, in Windows Explorer, find the file that corresponds to this view. The view files are located in the folder: ...\SoftPI\Tariscope\Views\[user name]\,
where [user name] is the Tariscope use name who created this view.
In the files of the view for which you want to change the name of the Project Code column, find the tags of <DisplayName>Project code</ DisplayName> that are located after the tags of <FullName>ProjectCode</ FullName> and replace the value in the <DisplayName> tag, for example "Who hung up". After that, refresh the view and it will be as shown in Figure 5.
Figure 5
If necessary, you can create reports that will contain this information, but this is a topic for another article.
Tariscope and Cisco Unified Communications Manager
Benefits of Tariscope to collect and analyze CDR and CMR from CUCM
Control of call quality for CUCM using Tariscope
Tariscope configuration to use the restriction feature with CUCM
