CDR Capturing Tool for Avaya

Posted: August 7, 2012 in Avaya CDR

Introduction

avayacdr

The AvayaCDR tool captures Avaya CDR data over TCP/IP connection and stores the data in a file. Then the data file is daily archived and moved to a directory at a time that defined by you. The program can convert the CDR data into CSV format or import them to database. Since the tool uses ODBC, database such as MySQL, MariaDB and MSSQL are supported. Moreover, the program can send the CDR data to Splunk which enables you to search CDR record by specific field name, generate reports and dashboards very easily. The following features are supported:

  • Writes CDR data to a local file, rotates to a new file by daily or monthly
  • Writes CDR data to MySQL, MariaDB and MSSQL database 
  • Writes CDR data to a CSV file, rotates to a new file every day
  • Writes CDR data to Splunk
  • Writes charge information for each CDR record
  • Supports Names Lookup and logging
  • Supports TCP and Reliable Session Protocol (RSP) connections 
  • Downloads and Imports CDR data via Communication Manager or Session Manager SFTP interface 
  • Imports archived CDR data files and sends the data to database or Splunk
  • Sends alert email to administrator when CDR link up down, DB up down or Splunk up down 
  • Sends alert email to administrator when blacklisted number or country code found in the CDR data
  • Sends alert email to administrator when CDR is received after office hour
  • Acts as System Printer for Avaya Aura
  • Supports Avaya Aura and IP Office
  • Supports Windows, CentOS 6.x, Ubuntu 14.04

OVA File for Testing

centos

We have created a OVA file which you can deploy it to VirtualBox or VMware Workstation or Player for testing.  After deploying the OVA file, please modify the following for your own environment

  • IP address of the CentOS VM
  • Telnet to AvayaCDR console port 14004, update the program parameters for your environment
  • Once the changes are completed, reboot the VM

The username and password of the OVA VM is

  • OS : root/P@ssw0rd
  • MySQL: root/P@ssw0rd
  • AvayaCDR console: tcpgate/tcpgate01
  • Splunk URL: http://newIP:8000/

Installation

  • I have recorded a video for Windows 2012 and Windows 7, please watch the video for Windows 2012 (64 bit) and Windows 7 (32bit) before the installation
  • Download the Avaya CDR Capturing Tool zip file here or backup site.
  • Follow the steps below to install the software.
    • Extract all the files into directory c:\program files\avayacdr
    • Open Windows Command Prompt (run as System Administrator if you are using Windows 7 or above), enter the following commands to register the program as Windows Service
      • cd c:\program files\avayacdr
      • avayacdr -i
    • Open ODBC Data Source Administrator, create a System DSN called AVAYACDRCFG for Microsoft Access Driver (*.mdb) and select avayacdr.mdb which is located in the directory c:\program files\avayacdr
      • Hint: make sure create a System DSN instead of User DSN, otherwise the program will startup without proper parameters
      • If you are using 64 bit Windows OS, please use 32 bit ODBC Data Source Administrator
      • avayacdrcfg
    • Open the Access file, edit the value of parameters cdr_archive_dir, that is the location for archived files. E.g. c:\\avayacdr\\
      • Hint: for the archive directory, make sure the format of double backslash (\\) is used because back slash is escape character in c++ and I’m lazy to modify it in my code.
      • You can edit the value of this parameter via the telnet console, the command is
        • update cdr_archive_dir c:\\avayacdr\\
    • Edit the value of parameter cdr_archive_time to to specify the time for file archive. E.g. 01:00
      • Hint: you can edit the value of this parameter via the telnet console, the command is
        • update cdr_archive_time 01:00
    • Start the Windows Service avayacdr
    • Telnet to localhost and port number 14004, enter username tcpgate and password tcpgate01 to access the program console
    • Enter the following command in the program console to add a listening port for Avaya CDR. E.g. add port 5001
      • add tcp 5001 * * custom cdr
      • Hint: You need to configure Avaya to send CDR to this host and port number 5001
      • Hint: If your Avaya sends data by Reliable Session Protocol, enter the following command
      • add tcp 5001 * * custom rsp
    • Enter the following command, you will receive debug information
      • trace on asc
    • Enter the following command, you will get the help message
      • help
    • Enter the following command, you can perform the file archive immediately
      • archive now

Install Multiple Instances of AvayaCDR on the Same Machine

  • Follow the steps below if you want to install multiple copies of AvayaCDR on the same machine
    • Copy the AvayaCDR files to a different directory
    • Rename the AvayaCDR.exe file to different name, say AvayaCDR2.exe
    • Create a new DSN for local Access MDB file
    • Create a new DSN for remote DB
    • Open Windows Command Prompt, enter the following command to register the program as service with startup argument
      • AvayaCDR2 -i -a AVAYACDRCFG2
      • Hint: register AvayaCDR2 as program service and use AVAYACDRCFG2 DSN
    • Stop all instances of AvayaCDR and start the new AvayaCDR instance
    • Telnet to localhost and port number 14004, enter username tcpgate and password tcpgate01 to access the program console
    • Change the telnet port by the following command
      • update telnetport 14005
    • Restart the new AvayaCDR instance and update other parameters accordingly to your environment
    • The following parameters must be changed for the new instance of AvayaCDR to work properly
      • cdr_archive_dir
      • cdr_remotedb_dsn
        • Hint: if you want to store CDR data in another DB, you need to modify the cdr_remotedb_dsn

64 bit Windows System 

  • For 64 bit Windows system, please follow the steps below
    • If AvayaCDR version >= 1.4.0, please download and install the Microsoft Visual C++ 2008 Service Pack 1 Redistributable Package MFC Security Update http://www.microsoft.com/en-hk/download/details.aspx?id=26368, choose x86 version
    • If AvayaCDR version < 1.4.0, please download and install the Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package MFC Security Update, http://www.microsoft.com/en-us/download/details.aspx?id=26347, choose x86 version
    • Configure ODBC System DSN using
      C:\Windows\SysWOW64\odbcad32.exe
    • Stop the Windows firewall because it enables by default and I don’t have time to figure out what ports are required to open, you need to find it out yourself. AvayaCDR uses 14004 for telnet port. If you defined 5001 for CDR data, you need to open this port in firewall as well.

Import CDR via SFTP interface 

  • This feature is supported by AvayaCDR version >= 1.3.0
  • Open the Access file, edit the following parameters
    • cdr_archive_dirsftp
      • Hint: The archive directory for sftp CDR files. The default value is c:\\avayacdrsftp\\
    • cdr_sftpdir
      • Hint: The remote directory that contains the CDR files. The default value is /var/home/ftp/CDR/
      • Hint: For a chroot sftp directory, the value is ./
    • cdr_sftpfilenameprefix (depreciated)
      • Hint: The filename prefix for CDR files. The default value is S
    • cdr_sftplicensekey
      • Hint: The license key to activate this feature
  • Open the program console, add your sftp host by the following command
    • add sftp host port username password dir
      • Hint: host can be hostname or IP address
      • Hint: default port of SSH is 22
      • Hint: username that created by your administrator for CDR retrieval over sftp protocol
      • Hint: encrypted password for sftp user. Use genpass command to generate an encrypted password
      • Hint: if dir is empty, the program uses the directory that specified in cdr_sftpdir
  • Input the following command to test the connectivity of sftp
    • sftptest
      • Hint: Test sftp login using the parameters provided by the add sftp command
  • The program will connect to your sftp hosts by every 30 seconds and imports the data to a file or database

Uninstallation

  • Stop the Window Service avayacdr
  • Open Windows Command Prompt, enter the following commands to uninstall the program from Windows Service
    • cd c:\program files\avayacdr
    • avayacdr -u

Writes CDR Data to MySQL or MSSQL Database

  • I have recorded a video for this section, please watch this video for the setup on AvayaCDR side and this for database side
  • The AvayaCDR is a 32bit application, make sure you use 32bit ODBC driver and use 32bit ODBC admin tool to create the DSN
  • In order to store CDR data directly to MYSQL or MSSQL, you need to define the following parameters
    • update parameter cdr_remotedb_dsn AVAYACDRDBCFG
      • Hint: the string AVAYACDRDBCFG is the system DSN for the remote CDR database
      • Hint: the following screen dump as an example for MySQL system DSN
      • avayacdrdbcfg
    • update parameter cdr_remotedb_username avayacdr
      • Hint: the string avayacdr is the username used to access the CDR database
    • genpass
      • Hint: use this command to generate an encrypted password
    • update  parameter cdr_remotedb_passwd encrypted-password-generated-from-genpass-command
      • Hint: copy the encrypted password from the genpass command
    • update parameter cdr_remotedb_table tAvayaCDR
      • Hint: the string tAvayaCDR is the table name of the CDR database that stored the CDR data
  • You also need to define mapping of CDR data items to the table column names of your database. If you use “customized” format, add paramcdr definitions according to your format. If you use “unformatted” format, please refer this document to define the paramcdr definitions.
    • add paramcdr 1 date 6 id_date text
      • Hint: this command maps Avaya CDR data item 1 (date) that is 6 character long to CDR database table column name (id_date) and its data type is text
      • Hint: use “mmddyy” or “ddmmyy” or “yymmdd” if you want to send the data to Splunk as well.
    • add paramcdr 2 space 1
      • Hint: this command maps Avaya CDR data item 2 (space) that is 1 character long, no mapping between the CDR database table
    • add paramcdr 3 time 4 id_time text
      • Hint: use “hh:mm” if you want to send data to Splunk as well.
    • add paramcdr 4 space 1
    • add paramcdr 5 duration 4 id_duration number
      • Hint: this command maps Avaya CDR data item 5 (duration) that is 4 characters long to CDR database table column name (id_duration) and its data type is integer
      • Hint: use “seconds” or “minutes” or “6s” if you want to use the duration to calculate telecom charge
      • Hint: use “h:mm:ss” if you simply want to display a readable string to user
      • Hint: use add paramcdr for other mappings.
      • Hint: make sure Avaya side end with line-feed because the program use it as terminator for each CDR record 
    • disp paramcdr
      • Hint: this command shows the mapping table
      • Hint: the following screen dump is my mapping, yours maybe different.
      •  paramcdr
    • reload
      • Hint: this command will reload the configuration and create the database connection according to the new parameters.
    • trace on asc
      • Hint: this command turns on debug messages for database logging

Convert CDR data into CSV file format 

  • In order to parse the CDR data and convert each record to CSV format, paramcdr must be defined
  • Use the console command add paramcdr to map your Avaya CDR data.  For example,
    • add paramcdr 1 date 6
    • add paramcdr 2 space 1
    • add paramcdr 3 time 4
  • Check if a parameter called “cdr_convertcsv” is set to “true” in the tParameter table. Or do it via program console
    • update parameter cdr_convertcsv true
    • reload 

Splunk Integration 

  • I have created a video for Splunk Logging, you can watch the video before the configuration
  • Use console command paramcdr to map your Avaya CDR data.  For example,
    • update parameter cdr_dateformatseparator –
      • Hint: Use the date format separator to separate day, month and year
      • Hint: Splunk need to know the date time in order index the CDR data
    • add paramcdr 1 date 6 id_date mmddyy 
      • Hint: Tells AvayaCDR the date format of CDR data, when cdr_dateformatsepartor is defined, the value will become “mm-dd-yy” which Splunk can recognize and import into it’s supported format
    • add paramcdr 2 space 1
    • add paramcdr 3 time 4 id_time hh:mm
      • Hint: Tells AvayaCDR to convert the time to hh:mm format which Splunk can recognize and import into it’s supported format
  • Update parameter “cdr_splunkhost”, for example
    • update parameter cdr_splunkhost 192.168.1.100
  • Update parameter “cdr_splunkport”, for example
    • update parameter cdr_splunkport 5100
  • Reload the configuration
    • reload 
  • Latest version of AvayaCDR supports backup Splunk configuration, please edit the following parameters
    • update parameter cdr_splunkbackuphost 192.168.1.101
    • update parameter cdr_splunkbackupport 5200
    • reload

Charge Logging 

  • To turn on Charge Logging, you should define the following parameters
    • cdr_charge_ditem_duration
      • Hint: this is the Avaya duration data item name in the CDR string
    • cdr_charge_dbcol_subtotal
      • Hint: this is the database column/field name for the charge total
    • cdr_charge_dbcol_currency
      • Hint: this is the database column/field name for the charge currency
    • cdr_charge_dbcol_writetime
      • Hint: this is the database column/field name for the time of the charge record
    • cdr_charge_dbcol_description
      • Hint: this is the database column/field name for the description of the charge rate
  • Add your rate one by one by the “add rate” command, for example
    • add rate 186 dialed-num 0.1 usd minute mobile call 
      • Hint: if dialed-num matched pattern 186, calculate the charge by 0.1/minute
    • add rate 354 code-used 186 dialed-num 0.3 usd minute fixed line 
      • Hint: multiple data items will be matched to calculate the charge

Names Lookup

  • AvayaCDR can lookup CDR data items and logs the mapped names to database and Splunk
  • To enable names lookup, first define the mapping of target data item name to database or Splunk field name, use the “add paramdict” command for the definition
    • add paramdict calling-num calling_name
    • add paramdict called-num called_name 
  • To define the mapping of number to its naming, use the “add dict” command
    • add dict * 52001 David 
    • add dict * 50002 John

Extended Logging 

  • Extended Logging is supported by latest version of AvayaCDR, please add/update the following parameters to enable extended logging.
    • update parameter cdr_extlog_sourceip pbxIP 
      • Hint: The IP address of PBX that sends the CDR will be logged and the field name id database or Splunk is called “pbxIP”
    • update parameter cdr_extlog_timestamp timestamp
      • Hint: The time at which the CDR data is received is logged to database or Splunk. This parameter specify the field name of the time stamp in database or Splunk.
    • Update parameter cdr_extlog_sourceinfo true
      • Hint: Information in the remark field of command “add tcp…” are sent to database or Splunk.

AvayaCDR as Avaya System Printer

  • You can configure AvayaCDR as System Printer for Avaya, thanks Patrick Prevoo sharing the following screens
  • CDR-1
  • CDR-4

Imports CDR Data to Database or Splunk from Archived File or Directory 

  • Follow the section of Writes CDR Data to MySQL or MSSQL Database or Splunk Integration to define the necessary parameters first
  • Open the program console and enter the following command to import archived data
    • import cdrfile c:\\avayacdr\\cdr20141027001.txt 
      • Hint: import single file
    • import cdrdir c:\\avayacdr\\
      • Hint: import whole directory

System Notification Email

  • The program sends email notification when error occurred. Errors included CDR link down, remote DB down, etc.
  • Edit the value of parameter tg_emailserverip to define the IP address of your email server.
  • Edit the value of parameter tg_emailsmtpport to define the SMTP port number of your email server. The default value is 25.
  • Edit the value of parameter tg_emailusername to define the  username of email agent which is responsible to send notification emails.
  • Telnet to localhost and port number 14004, enter username tcpgate and password tcpgate01 to access the program console. Type genpass in the program console to generate an encrypted password string for the email agent, copy the string for later use.
  • Edit the value of parameter tg_emailpasswd to define the password of the email agent. The password should be in encrypted format, you can use console command genpass to generate the encrypted password.
  • Edit the value of parameter tg_emailsenderdisplay to define the display name of the email agent.
  • Edit the value of parameter tg_emailauth to define whether authentication is required between your AvayaCDR machine and the email server. When your email server trust your machine, set it to false, otherwise set it to true.
  • Edit the value of parameter tg_emailssl to define whether SSL communication is required for the SMTP session. The default value is false, set it to true when SSL is required.
  • Enter the following command in the program console to add your email address
    • add email service@upinget.com
    • Hint: Replace my email address to yours
  • Enter the following command to test the configuration
    • test email

Email Alert for Blacklisted Number 

  • This feature is supported by AvayaCDR version >=1.3.1
  • To define dialed number in the blacklist number pattern, enter the following command in the program console
    • add blacklistnumber 911 dialed-num AlertWhoDial911
      • Hint: The number 911 is the pattern that we defined as blacklisted number
      • Hint: The dialed-num is the data item name that we defined in the paramcdr table
      • Hint: AlertWhoDial911 is the description text for this number pattern
  • To define calling number in the blacklist number pattern, enter the following command in the program console
    • add blacklistnumber 24682468 calling-num Competitor
      • Hint: The number 24682468 is defined as blacklisted number
      • Hint: The calling-num is the data item name that we defined in the paramcdr table
      • Hint: Competitor is the description text for this number pattern
  • To add blacklist country code, enter the following command
    • add blacklistnumber +123 called-number Blacklist Country +123
    • add dialprefix 001
      • When country code is defined, the dial prefix will be compared
  • You need to define email function parameters, please refer to section “System Notification Email” for more information.

Email Alert for Non-Office Hour Calls 

  • This feature is supported by AvayaCDR version >= 1.5.13
  • To enable this feature, enter the following command in the program console
    • update parameter cdr_nonofficehouralert true
    • update parameter cdr_officehourstart 9 
    • update parameter cdr_officehourend 21 
    • reload

CentOS 6.x Installation Guide

  • I have recorded a video for CentOS installation, you can watch the video before the installation
  • The following is for CentOS 6 only, the commands can be found in the centos6/installation.txt file
  • The executable file of 32bit version is “avayacdr”, the 64bit version is “avayacdr64”
  • Install the following packages
  • yum install mysql-server mysql libssh2
  • yum install mysql-connector-odbc unixODBC
  • Config and start MySQL
  • chkconfig mysqld on
  • service mysqld start
  • Create database and user
  • mysql -u root -p
  • create database avayacdr;
  • create user 'tcpgate'@'localhost' identified by 'tcpgate';
  • grant all on avayacdr.* to 'tcpgate'@'localhost';
  • flush privileges;
  • quit
  • mysql -h localhost -u tcpgate -p avayacdr < avayacdr.sql
  • Copy the following to /etc/odbc.ini file
    • [AVAYACDRCFG]
      Description = MySQL connection to AvayaCDR
      Driver = MySQL
      Server = localhost
      Port = 3306
      Database = avayacdr
    • [AVAYACDRDBCFG]
      Description = MySQL connection to AvayaCDR
      Driver = MySQL
      Server = localhost
      Port = 3306
      Database = avayacdr
  • Create user and copy files
  • useradd avayacdr
  • mkdir /usr/local/avayacdr
  • cp avayacdr /usr/local/avayacdr
  • chmod +x /usr/local/avayacdr/avayacdr
  • chown -R avayacdr.avayacdr /usr/local/avayacdr
  • mkdir -p /var/log/avayacdr/archive/sftp
  • chown -R avayacdr.avayacdr /var/log/avayacdr
  • Auto start the daemon after server reboot
  • echo "rm -f /tmp/avayacdr.log" >> /etc/rc.local
  • echo "su avayacdr -c '/usr/local/avayacdr/avayacdr'" >> /etc/rc.local

Ubuntu 14.04 Installation Guide

  • I have recorded a video for Ubuntu installation, you can watch the video before the installation
  • The following is for Ubuntu 14.04 only, the commands can be found in the ubuntu/installation.txt file
  • The executable file of 32bit version is “avayacdr”, the 64bit version is “avayacdr64”
  • Install the following packages
  • sudo apt-get install mysql-server mysql-client
  • sudo apt-get install libmyodbc unixodbc unixodbc-bin
  • sudo apt-get install libssh2-1 openssl unrar
  • Create database and user
  • mysql -u root -p
  • create database avayacdr;
  • create user 'tcpgate'@'localhost' identified by 'tcpgate';
  • grant all on avayacdr.* to 'tcpgate'@'localhost';
  • flush privileges;
  • quit
  • mysql -h localhost -u tcpgate -p avayacdr < avayacdr.sql
  • Copy the following to /etc/odbcinst.ini file for Ubunt 64bit
    • [MySQL]
      Description = ODBC for MySQL
      Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
      Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
      UsageCount = 1
  • Copy the following to /etc/odbcinst.ini file for Ubunt 32bit
    • [MySQL]
      Description = ODBC for MySQL
      Driver = /usr/lib/i386-linux-gnu/odbc/libmyodbc.so
      Setup = /usr/lib/i386-linux-gnu/odbc/libodbcmyS.so
      UsageCount = 1
  • Install the ODBC driver
  • sudo odbcinst -i -d -f /etc/odbcinst.ini
  • Copy the following to /etc/odbc.ini file
    • [AVAYACDRCFG]
      Description = MySQL connection to AvayaCDR
      Driver = MySQL
      Server = localhost
      Port = 3306
      Socket = /var/run/mysqld/mysqld.sock
      Database = avayacdr
    • [AVAYACDRDBCFG]
      Description = MySQL connection to AvayaCDR
      Driver = MySQL
      Server = localhost
      Port = 3306
      Socket = /var/run/mysqld/mysqld.sock
      Database = avayacdr
  • Install your system DSN
  • sudo odbcinst -i -s -l -f /etc/odbc.ini
  • Create user and copy files
  • sudo useradd avayacdr
  • sudo mkdir /usr/local/avayacdr
  • sudo cp avayacdr /usr/local/avayacdr
  • sudo chmod +x /usr/local/avayacdr/avayacdr
  • sudo chown -R avayacdr.avayacdr /usr/local/avayacdr
  • sudo mkdir -p /var/log/avayacdr/archive/sfp
  • sudo chown -R avayacdr.avayacdr /var/log/avayacdr
  • Auto start the daemon after server reboot, add the following before ‘exit 0’ to /etc/rc.local file
  • rm -f /tmp/avayacdr.log
  • su avayacdr -c '/usr/local/avayacdr/avayacdr'

Avaya (PBX side) CDR System Parameters Screen (Reference ONLY)

  • The following screen dumps are cdr configuration of my site, yours maybe different.
  • cdr1
  • cdr2
  • cdr3
  • Make sure Reliable Protocol is set to n if you simply use TCP to transmit CDR data
  •  disablereliable
  • I use standard 5331 port, so I enter the following command in the program console
    • add tcp 5331 * * custom cdr
  • If you want to use Reliable Session Protocol, enter the following command
    • add tcp 5331 * * custom rsp

Support and License

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s