July 5, 2017 at 10:47Analysis of Cisco CDRs and Asterisk Telephony with Splunk
Today there is a classic, from the point of view of analytics, task - analysis of CDR telephony. In this article, we will talk about how two different companies solved two completely different tasks. Company X analyzed the Cisco Telephony CDR, while Company Y analyzed the Asterisk Telephony CDR. Why do we write about this in one article? Because both companies use Splunk , which we wrote a lot about earlier, as an analysis tool .
Under the cut you will find a detailed description of the tasks and their solutions with pictures and requests.
Tasks
Company X has about 30 departments, in which about 400 internal numbers and about 100,000 calls per month.
Company Y has a call center based on Asterisk telephony with 1 million calls per day and wants to receive analytics about its work. Most of all, company Y wants to know the number of competitive calls (busy time slots) at a certain time slot (for example, every hour), with distribution over external flows. Plus basic kpi, such as: average call duration, average call duration, percentage of answered calls, and more.
In this article we will not talk about how to connect Splunk data and how to do field parsing (if this is interesting, write to us - and we will make a separate article about it, but in fact there is no rocket science there). We will show the basic queries, graphs and dashboards.
Company X
Analytics throughout the organization:
This dashboard contains general analytics for the entire company as a whole, with various statistical indicators. The dashboard is live, that is, it has various filters, and can also send the user to the next level of detail. For example, when you click on a specific department or phone number, the user will see analytics in the context of the selected segment.
Analytics within a separate department:
On this dashboard, the user sees the details for a specific department of the company, and can conclude both the statistics on the interaction of employees of this department with other internal departments and external calls.
User-specific analytics:
This is the last level of detail where we see information regarding a specific employee of the organization and can judge its activity.
Requests
In fact, all these graphs are based on fairly simple queries, the level of complexity is comparable to those that we discussed in our previous articles . Below is one of the most difficult:
Company Y
Everything is much simpler here, since the call center has only one type of call, and the company is more interested in only summary information. However, the possibility of additional processing and detailing is not excluded, for example, for a specific employee. Below is the main dashboard based on CDR Asteriska:
The most interesting is the bottom chart, where the task of calculating competitive sessions is solved.
Requests
Below is one of the most complex queries, just about competitive sessions:
We are happy to answer all your questions and comments on this topic. Also, if you are interested in something specifically in this area, or in the field of machine data analysis in general, we are ready to finalize the existing solutions for you, for your specific task. To do this, you can write about it in the comments or simply send us a request through the form on our website .
Under the cut you will find a detailed description of the tasks and their solutions with pictures and requests.
Tasks
Company X has about 30 departments, in which about 400 internal numbers and about 100,000 calls per month.
- Company X wants to receive analytics of calls of internal users both outside and among themselves. It is necessary to obtain information on the number of phone calls, the number of callers, the distribution of incoming and outgoing calls, the most callers inside / outside.
- Reports are needed showing how employees from various departments interact with counterparties. Often, such a task is difficult to objectively solve based on surveys, and through analysis of phone calls you can get the most objective picture.
- It is necessary to evaluate the intensity of calls in order to understand the activity of managers within the organization.
Company Y has a call center based on Asterisk telephony with 1 million calls per day and wants to receive analytics about its work. Most of all, company Y wants to know the number of competitive calls (busy time slots) at a certain time slot (for example, every hour), with distribution over external flows. Plus basic kpi, such as: average call duration, average call duration, percentage of answered calls, and more.
Task solutions
In this article we will not talk about how to connect Splunk data and how to do field parsing (if this is interesting, write to us - and we will make a separate article about it, but in fact there is no rocket science there). We will show the basic queries, graphs and dashboards.
Company X
Analytics throughout the organization:
This dashboard contains general analytics for the entire company as a whole, with various statistical indicators. The dashboard is live, that is, it has various filters, and can also send the user to the next level of detail. For example, when you click on a specific department or phone number, the user will see analytics in the context of the selected segment.
Analytics within a separate department:
On this dashboard, the user sees the details for a specific department of the company, and can conclude both the statistics on the interaction of employees of this department with other internal departments and external calls.
User-specific analytics:
This is the last level of detail where we see information regarding a specific employee of the organization and can judge its activity.
Requests
In fact, all these graphs are based on fairly simple queries, the level of complexity is comparable to those that we discussed in our previous articles . Below is one of the most difficult:
|inputlookup lookup.csv
| where unit = "MGMI"
| table ext
| join ext type=left
[search index=test sourcetype = csv Department = "MGMI" | stats count AS "colorig" by callingPartyNumber| rename callingPartyNumber as ext]
| join ext type=left
[search index=test sourcetype = csv DepartmentDest = "MGMI" | stats count AS "coldest" by originalCalledPartyNumber| rename originalCalledPartyNumber as ext ]
| eval C=if(isnull(colorig), 0,colorig)
| eval D = if(isnull(coldest), 0,coldest)
| table ext C D
|rename ext as "Сотрудники" C as "Количество исходящих вызовов" D as "Количество входящих вызовов"Company Y
Everything is much simpler here, since the call center has only one type of call, and the company is more interested in only summary information. However, the possibility of additional processing and detailing is not excluded, for example, for a specific employee. Below is the main dashboard based on CDR Asteriska:
The most interesting is the bottom chart, where the task of calculating competitive sessions is solved.
Requests
Below is one of the most complex queries, just about competitive sessions:
index="aster2" dstchannel="Beeline" | concurrency duration=duration | timechart span=1h max(concurrency) as Beeline
| join _time type=left
[search index="aster2" dstchannel="MTS" | concurrency duration=duration | timechart span=1h max(concurrency) as MTS
| join _time type=left
[search index="aster2" dstchannel="Megafon" | concurrency duration=duration | timechart span=1h max(concurrency) as Megafon
| join _time type=left
[search index="aster2" dstchannel="TTK" | concurrency duration=duration | timechart span=1h max(concurrency) as TTK ]]]Conclusion
We are happy to answer all your questions and comments on this topic. Also, if you are interested in something specifically in this area, or in the field of machine data analysis in general, we are ready to finalize the existing solutions for you, for your specific task. To do this, you can write about it in the comments or simply send us a request through the form on our website .