Avaya Call Management System is very good as a standalone reporting solution, but it has its drawbacks, too. Besides having very outdated architecture and performance limitations, its lack of openness for external applications was always one of the major concerns for anybody who wanted to make the most of their call center. For example, people are often interested in cradle-to-grave call analysis or collecting “warm” sales leads or detailed agent statistics or detailed customer profiles or inter-switch call flow analysis; the list is very long–but the point is, there are actually only two ways of collecting that data. The one is by utilizing CTI applications and monitoring everything in the system, which is grossly ineffective, resource consuming and prone to incurable headaches; the other is by utilizing an obscure CMS subsystem called External Call History Interface. Unfortunately, Avaya have always positioned this mechanism as fit-to-purpose and there were very limited purposes, so many people don’t even know that their CMS can export loads of data they’re looking for! And even more of that, since November 7th 2005–almost five years ago, my, the time goes fast!–ECHI comes as standard, free of charge, with every CMS server sold by Avaya, so if you have CMS R13 or later you’re almost guaranteed to have this function enabled. The Big Question is: how to utilize it? This article is an answer to the first part of the equation: how to extract External Call History data and make it available for further processing by 3rd party applications–or just viewing in Microsoft Excel, plain and simple.
A little foreword: although ECHI can be utilized for various purposes and can benefit any call center of any size and complexity–there is always a need for warm sales leads collection, for example–I will assume typical setup for a midsize call center. In this case, CMS server exports ECHI data to an external database server running Microsoft SQL server, data is imported in the SQL Server with standard tools and is reported upon with standard reporting services. There are no specific requirements for Windows Server nor for SQL Server, although I would recommend using SQL Server 2005 or later, they have reporting services built in the software and it is quite adequate in my opinion. Of course, you can use old SQL Server 2000 and report with old Crystal Reports, it will be perfectly fine. Or use Oracle database with its reporting services, or even free database engines like PostgreSQL, it doesn’t really matter. What matters is that in this article, I will cover only data extraction, conversion and export. The second part of the answer to the Big Question, i.e. what to do with the data, how to process it and what kind of results to get from it, will be discussed in subsequent articles.
Well, enough talking! Let’s dive in!
First of all, you should check if your CMS system has ECHI licensed. To do this, first you need log in as root.
Now run “cmssvc” command and enter “1” at the prompt:
You should see RTU screen like this:
Take a look at “external call history” line, it should read “authorized”. In case it reads “not authorized”, it means just that and you don’t have this feature authorized on your CMS. Ask your vendor if ECH interface can still be purchased for your CMS system. Probably not by this time, but if you know the right phone number, just call it and they’ll make an exception for you. Things Avaya will do for money! J
If you see “installed/on/extended” like on screenshot, or something similar, then you already have ECH interface installed and turned on–which means that you need to research it more before doing anything. ECHI can be used to feed Avaya Operational Analyst, NICE Analyzer or some other application, so you better check with your vendor to see what’s that using it.
As a rule of thumb, any CMS system purchased or upgraded in last five years should have external call history authorized by default.
OK, so you have RTU available and want to proceed. Next step is setting up SSH environment, because my version of transmission script uses SCP utility to copy text files over the network to the database server. Why SCP instead of default UUCP? Well, UUCP is very outdated technology, there are none decent Windows implementations and even UNIX clones do not include UUCP by default these days. Besides that, UUCP does not support link encryption nor strong authentication, and when someone wants UUCP to be secure they tunnel it through SSH connection. Now that would be rather unnecessary complication, so we will just use SSH in the first place; it’s right there on your CMS server already, comes as standard package with Solaris OS so you won’t need to install it. Why not FTP? It’s not obvious but default FTP client in Solaris does not support atomic file operations–it means that upon invoking FTP to transfer a file, we cannot know for sure if the file was transferred in full, without errors. Besides that, FTP is as insecure as it can get, transmitting authenticating credentials in plain text along with the actual data. Remember, we’re talking about transmitting ECHI data dumps and they can contain pretty sensitive information–your customers’ ANI numbers, for example. I bet you wouldn’t want to have this information disclosed so we’ll use secure methods of file transmission.
I will not describe how to set up a SSH server in Windows, firstly because there are several Windows SSH implementations, both free and commercial, and I cannot know beforehand which one you will decide to use. I would recommend using OpenSSH because it’s the most popular free SSH implementation, but setting it up may be a bit complicated if you have never dealt with it before. Secondly, as far as I know the most popular SSH distributions are documented back and forth (OpenSSH definitely is!), so nothing I could write would add significantly to already existing documents. However, the client side setup is within the scope of this article so I will elaborate on that.
In order to copy our files to database server, we will need a username and a set of keys, these are used to authenticate the user instead of a password. With SSH, you can still use password authentication if you want to, but due to security restrictions, no SSH utility will accept a password entered with automated means (unlike FTP or Telnet), only a live typing will do. All this is great for security but we want our application to run as unattended as possible so we will need some means to authenticate our user on the remote side without using a password. Fortunately, there is a way to do that, and it doesn’t break security, too. It is called symmetric key authentication. In order to use it, we will need to create a cryptographic key pair with ssh-keygen utility. Note that default username is “echi” and default path to private key file is /cms/dc/chr/echi.key. You can change it for whatever username or path you want, or just leave it as is so it will just work.
Run ssh-keygen and when it asks you for a passphrase, just press Enter twice:
Now that we have our set of keys, we need to let the other side know that we’re going to use them. Private key is just that, it’s private and nobody should have access to it; but we will need to let the other side know our public key. Print its contents on the terminal:
See that long string of characters after the “cat” command? That’s our public key all right. You can just copy the text and paste it in Notepad on the database server using Remote Terminal session. If you are using OpenSSH, you should store public keys in a file called authorized_keys that should be placed in .ssh directory in echi’s home directory, %HOME%/.ssh/authorized_keys. Note that there is no extension. If you are not using OpenSSH, consult your SSH server documentation as to public key file placement.
When you have configured public key, it’s time to test SSH connectivity by trying to login on the remote host:
Note that SSH tells you that it does not know the host you’re trying to connect to and asks for your confirmation–you’ll need to type in “yes” and confirm with Enter. This procedure is one of the key security components of the SSH suite, because a fingerprint is unique for every host and if remembered one does not match live one you get from the host upon trying to connect, SSH will cry out loud and won’t allow you to connect:
Note that I changed the host key for illustrational purposes; it’s highly unlikely that you would ever encounter a hacking attempt, especially within your organization’s local network. If you would ever see a message like this, it would probably mean that your IT department has changed the hardware or the software on the database server, but nevertheless it’ll never hurt to check. And it’s nice to know that your connection is secure.
Now it’s time for the final check, i.e. if SCP connection is working. Let’s create some test file and copy it over to the database server:
Note that SCP command is rather long so I’ll repeat it here: “scp -i /cms/dc/chr/echi.key test .txt firstname.lastname@example.org:/tmp”. As you can see, the file was successfully copied to our host sql.call-center.com in the /tmp directory, and that means our connection is working properly.
Next thing you need to do is to install ECHI subsystem. Read this document and follow the instructions in it to enable ECH software on your CMS system, “Setting up the ECHI package” section has step by step explanations. Follow them and choose default program paths for uucp_copy and uucp_check; for the computer where to send files, enter actual DNS name of the database server, or IP address if you choose not to use DNS. Default ECHI state is better “off” but that would require someone to start it every time CMS is restarted. The buffer size is arbitrary, consider your call traffic and decide how often you would like to receive information–one record in buffer contains one call segment, and when the buffer gets full, it is dropped to disk and transmitted. However, setting buffer size too low could mean that files will be dumped faster than they could be processed and transmitted and that is undesirable situation. I always set buffer size for its maximum value, it yields binary data files about 1 megabyte in size which is pretty hefty but is useful for call centers with significant call traffic–and when traffic is low, buffer gets dumped and transmitted at least once per interval anyway which is fine for my purposes. Your Mileage May Vary, though.
Note that ECHI installation process would require restarting CMS service and is better done off hours. When you have ECH installed, it should start generating data files in /cms/cmstables directory, with names like “chrXXYY”. These files are encoded in proprietary binary format that cannot be read by humans or other software without specialized convertor that would decode binary format into text files for further processing. Avaya charges a hefty chunk of $$ for such convertor, but there are free alternatives available, one of which is my own ECHI decoder program. In fact, it’s not only one program but rather a suit of programs I developed for one of my customers, and it’s been in use for two years without any major problems.
This ECHI decoder suite has one major advantage compared to other free ECHI processors: it does not require any framework or software that is not already on CMS server. The program itself is written in Perl that comes standard with Solaris operating system that runs on your CMS server, there is no need for additional modules or programs; the only software you will need to install on CMS server is ECHI decoder itself and transmission and maintenance scripts I provide with it. They’re rather small and require zero to very little maintenance, giving you an opportunity to extract call detail data from CMS effectively for free. This is basic version of ECHI decoder suite, however. I don’t mind sharing some of my know-how with fellow Avaya engineers but to share all of it for free would be a little too altruistic. J The extended ECHI decoder suite supports double text-and-binary sequenced transmission, FTP protocol as well (as robust as it can get), automatic error recovery and retransmission, backup data storage with automatic cleanup, watchdog service with email and SNMP notification–in short, it’s a solution suited for large installations where robustness and minimal maintenance footprint are at premium. If you’re interested, just drop me an email and we’ll discuss it further.
Installation process for ECHI decoder is pretty straightforward, and for simplicity I will assume that your CMS server has Internet access. Type in this command: “pkgadd -d http://avaya.dwalin.ru/wp-content/uploads/2010/08/CMSechi-basic-1.5.pkg”
Press Enter and you will see the following prompt:
Just press Enter to install all packages–there is only one in that package anyway. You will see the following prompt:
Type in “y” and press Enter. These two files come with CMS by default and my package needs to overwrite them for all-default installation. You should see no more prompts and installer will just finish with OK.
If your CMS server does not have access to Internet or there is any kind of problem downloading the package, you can download it on your computer and then upload on CMS server. I won’t describe how to do that, because this post is getting quite long already. J
Now, change directory to /cms/dc/chr and check that all files are there:
Hooray, we have installed the ECHI decoder. The one last step is to check how it’s working. If ECHI subsystem in CMS is installed but not “turned on”, it should generate chrXXYY files I was talking about up there in the beginning. Check if some such files are already there:
Whoa, here it is. The files are being created but not transmitted since ECHI is turned off. We don’t want no screw-ups so we’d better check how it’s going manually:
You see that uucp_copy gave no output? It’s not supposed to, because it is called by CMS service that doesn’t care for any human-readable output anyway. Let’s check our log files, firstly see if they’re here:
Looks normal, since basic ECHI decoder scripts are generating only two log files: echi-decode.log for conversion and scp_log for transmission. Let’s check the conversion log:
Well, no errors mean everything’s all right. Just like needed. Now let’s check transmission log:
The line we looked for tells that transmission was successful. But one more check won’t hurt anyone, right? Let’s login on the remote machine and see if the file is there:
Right-o! The only thing that’s left is to turn ECHI subsystem on with cmsadm option 6, and enjoy your all-fresh ECHI data. JCMS, data export, echi