CDR Tool

From Pbxnsip Wiki

Contents 1 Introduction 2 pbxnsip CDR Tool’s Key Functionality 3 Using the CDR Tool 3.1 Extension/Identification Field 3.2 From/To checkbox 3.3 Filter calls by duration: 3.4 Date ranges: 3.5 Run Report: 4 Using Navigation bars, Pop-up Calendar and Other Functions 4.1 Navigation Bar 4.2 Pop-Up Calendar 4.3 Save the results in HTML format (printer-friendly) 4.4 Export the results as a CSV file 4.5 Print the results without the clutter 4.6 Mail the results 5 Using the Traffic Analyzer 5.1 Monthly Traffic 5.2 Weekly Traffic 6 Administration and Settings 6.1 Database Settings 6.2 Application Settings 7 Extras 7.1 Configuration Back-up and Restoration 7.2 Viewing the CDR process log and XML dumps 8 Notes 8.1 Date & Time 8.2 Duration 8.3 Evaluation Rule 8.4 Basic CDR Only 8.5 Date Range Constraint 8.6 Grant Admin 8.7 User Mode 8.8 Delete Protection 8.9 Settings Info 8.10 Maximum Index 8.11 CAUTION

Introduction

A Call Detail Record (CDR) is recorded information generated by a PBX, which contains source and destination identities, initial connection time of the calls as well as the call termination time, billing information and other relevant fields. Often, several fields are calculated at processing time after the CDR is generated, i.e. time duration is derived from the termination time of the call and the time the call was connected. The CDR format are generally different from one telecommunication company to another, however IP-based PBX company, such as pbxnsip IP PBX, are using Simple Object Access Protocol (SOAP) as a standard format for CDRs.

The pbxnsip CDR Tool is a pragmatic call detail record reporting and statistical tool, which retrieves CDRs from the pbxnsip PBX server via Simple Object Access Protocol (SOAP) interface, and stores the records into an external databaseMySQL database, "the most popular open source database". With the combined power of the CDR tool logic and MySQL database one can monitor traffic, phone usage, and create post-paid bills.

pbxnsip CDR Tool’s Key Functionality

The pbxnsip CDR Tool provides you thrifty dynamic report generation, easily created by a user-friendly GUI, which performs advanced querying to the MySQL database so you don’t have to.

This tool provides you the criteria to choose from the following:

• Extension numbers, PSTN numbers or number friendly names,
• Inbound, outbound, or both calls, dependent on extension or PSTN number,
• Filter payable durations in terms of minutes and seconds, and
• Specify date ranges (Pop-up is calendar included)

After specifying the desired criteria, the report will output the following:

• All calls that meets the given criteria,
• Total number of calls and
• Total time of payable duration

The CDR Tool has two outputs available:

• Basic CDR, which displays the following
  • Date/Time of the Call ¹
  • Source Identity
  • Destination Identity
  • Payable Duration ²

• Extended CDR, includes the fields from basic CDR, as well as
  • Domain,
  • Language,
  • From User & To User of the current domain,
  • Charge Account & Charge Number,
  • Informational Duration ²
  • Date/Time of Connection & Disconnection ¹

With the available common functions, you can

• Save the results in HTML format (printer-friendly),
• Export the results as a CSV file,
• Print the results without the clutter, and
• Mail the results in HTML format with attached CSV file.

The pbxnsip CDR Tool also has a nice easy to use feature, which is a graphical Traffic Analyzer, based on:

• Month/Year, and
• Weeks (Pop-up is calendar included)

Lastly, The pbxnsip CDR Tool is equipped with authentication system interfaced with the MySQL database, as well as some of the basic account settings such as creating, editing and deleting users, and grant users with certain administrative rights. The administrator has the ability to alter the database and application settings.

Using the CDR Tool

As you can see, there is an interface at the top that consists of Extension fields, with From/To checkbox and date/time filter. In the middle is a display of the basic CDR output.

Extension/Identification Field

• Extension number can be used, as well as PSTN numbers.
• You can also use number friendly that shows up in the from/to column of the output. It is not case-sensitive
  • e.g. Compaq Snom1 instead of 104

see item 1

From/To checkbox

• These options are dependent on the extension field.
• Click on from checkbox to get a listing of all calls made from that extension field.
• Click on to checkbox to get a listing of all calls made to that extension field.
• Leave both unchecked to get a listing of all calls made from OR to that extension.
• Check Both to get a listing of all calls made from AND to that extension.

see item 2

Filter calls by duration:

• Gives you the choice to filter all calls within the billable duration specified in minutes and seconds field,
• Click on the Filter calls drop-down menu
  • Select longer than the given minutes and seconds. ³
  • Select shorter than to the given minutes and seconds. ³

see item 3

Date ranges:

• To fill out the date, simply click on either from/to date field, and a calendar will prompt you to select the date
• To get all calls occurring between the start date and end date, specify both fields.
• To get all calls occurring on or after the start date, define the from date only.
• Likewise, to get all calls occurring on or before the end date, define the to date only.

see item 4a and 4b

Run Report:

• Will execute a query based on the selected criteria.
• By default, all CDR listings will be shown in descending order of the call time.
• Each criteria component can be combined, however:
  • From and to check box is dependent on the extension/identification field.

see item 5

---

After specifying the desired criteria, the report will output the following:

1. All calls that meets the given criteria,
2. Total number of calls and
3. Total time of payable duration
4. Navigation bar

The following example is the result where extension is set as “anonymous” with no other criteria. You will see that there are 56 calls made by “anonymous” with the total sum of duration of 6 hours, 37 minutes and 28 seconds. Also, the navigation bar will be provided to reduce load time and easily browse through the CDR listings.

Another example is demonstrated below where the duration of call is longer than 25 minutes.

An extended CDR layout would look similar to the following:

Using Navigation bars, Pop-up Calendar and Other Functions

Navigation Bar

• The navigation bar is a pretty straightforward tool
  • It has a first and last page link,
  • Next and previous link, and
  • A list of linked page numbers to choose from

• The current page will be denoted by a parenthesis.
• The navigation arrow will turn black once you reach to the beginning or end of the page.

---

Pop-Up Calendar

• The pop-up calendar will be available when you click on applicable date textbox

• To choose today’s date:
  • click on Today or hit enter.

• To choose any other date:
  • Click on any of the number or use the left and right key to navigate through the days
  • Then hit enter to select the day.

• If you prefer to type the date:
  • click on the “x” button or hit escape,
  • Then type using DD-MM-YYYY (DD/MM/YYYY is also acceptable).

---

Save the results in HTML format (printer-friendly)

• You can save the current CDR listings, by simply pressing the save button. ⁴
• For your convenience, the output will not have page breaks, navigation bar or criteria interface.
• You can also print out the saved format.

Export the results as a CSV file

• Similar to the save function, you will get a comma separated value of the CDR listings.
• The current layout are as follows:
  • Row 1: The first column is labeled “date”, and the second column is a timestamp of the time the file was saved.
  • Row 2: Column names of the CDR format as described in extended CDR section.
  • Row N: extended CDR records.

• This is useful if you want to import the CDR listings to a different database or programmatically display the CDR listings in a certain way.

Print the results without the clutter

• This function has the same effect as saving the CDR listing and then print it out. ⁴
• The output will also not have page breaks, navigation bar or criteria interface.

Mail the results

• Simply fill out the textbox next to the “Mail to” button and then hit that button.
• The CDR listings will be in HTML format in the body of the email, and
• The CSV file will also be attached.

Using the Traffic Analyzer

The Traffic Analyzer is very useful for visualizing the call activity, and can aid in determining future needs of the telecommunication company. Currently, the traffic analyzer uses line graphs, and it is laid out as number of calls per time.

Monthly Traffic

• There are only 2 input required, the month, which you can select from a drop down menu, and year.

Weekly Traffic

• There are also 2 input required, the start date and end date. However, you only need to either specify the start date or the end date, and the alternate field will be automatically populated. ⁵
• For example, set the start date as 5/1/2007 and the end date will be automatically set as 5/8/2007.

Administration and Settings

The pbxnsip CDR tool has a built-in authentication system interfaced with the MySQL database. There are 2 user modes, administrator and normal. The difference is the administrator has the ability to add, edit and delete the users, as well as assign administrative rights for certain users. Also, the administrator also can alter the application and database settings. The username and password are case-sensitive.

• To create an account:

1. Click on the create submenu
2. If necessary, click on the administrative privileges checkbox ⁶
3. Fill out the account name, must be at least 5 characters
4. Fill out the email address
5. Fill out the password, must be at least 4 characters

---

• To edit an account ⁷:

1. Click on the Edit submenu
2. Edit any of the fields as needed
3. Hit the save button.

---

• To edit a different account (administrator only):

1. If you are not currently viewing the account list, click on the show list submenu
2. Click on the edit next to the account name that needs to be edited.
3. Follow the same step as described in “To edit an account”

---

• To delete an account ⁸:

1. If you are not currently viewing the account list, click on the show list submenu
2. Click on the delete next to the account name that needs to be deleted.
3. You will be prompted to delete the account for verification, click on OK.

---

• To change settings:

1. Ensure that you are in administrator mode
2. Click on the Settings submenu
3. Change any settings as required. ⁹ See below for reference.
4. Hit the save button

---

Database Settings

• Hostname
  • Default is localhost. This is the address of the MySQL database.
• Username
  • No default username. If you setup the local web server or the preconfigured AMP, the username is usually not set, so it can be any name. It is strongly urged that you setup up the username and password for security.
• Password
  • No default password. See Database Username description.
• Database Name
  • Default is pbxnsip_cdr. The name of the database to use with the pbxnsip CDR Tool web application.
• Table Name
  • Default is CDR. The name of the table to use with the CDR Tool web application.

Application Settings

• Working Directory
  • Default is /cdrsys/. The name of the working directory. The forward slash on each end is required. Changing the working directory in this configuration file does not change the actually directory; in that case, the actual working directory needs to be change manually.
• Image Directory
  • Default is /cdrsys/img/. The name of the image directory. See Working Directory description.
• CDR Listings Per Page (CDR Entries Per Page in setup.php)
  • Default is 30. Number of Call Detail Records viewed per page.
• 12 Hour Format
  • Default is FALSE. Display the time in 12-hour format or 24-hour format.
• Database Logging
  • Default is FALSE. Shows debugging information on SQL transaction and file system operation on the PHP/SOAP server (processCDR2.php). The logs are in incremental file name denoted with *.log extension, found in /(working directory)/cdr/.
• Database Debugging
  • Default is FALSE. Shows debugging information on SQL transaction and others. Normally used for support and troubleshooting provided by pbxnsip Inc. Debugging information will be displayed in the main page.
• XML Dump
  • Default is FALSE. Dumps the actual XML/SOAP file sent by the pbxnsip software through the processCDR2.php server. The dumps are in incremental file name denoted with *.xml extension, found in /(working directory)/xml/.

Extras

Configuration Back-up and Restoration

Making a mistake when changing configuration can render the web application useless. Fortunately, any changes you make in the settings page, the configuration is automatically backed up in the “config“ directory. In the event that that the CDR Tool no longer work, due to bad configuration (e.g. wrong password, incorrect working directory):

1. Simply go to the address bar of the browser,
2. Delete anything after the working directory. In this case, cdrsys is the default working directory.

1. Type “config/restore_config.N.php” at the address bar, where N is the maximum index of the configuration directory. For example. If I altered the settings exactly 30 times, I would type “config/restore_config.30.php”. ¹⁰

1. This will revert to the most recent settings, and redirect you to the settings page. ¹¹

Viewing the CDR process log and XML dumps

It’s a real hassle to open up and view the logs in this manner, 1.log, 2.log ….n.log. There is already a script that will view all of the logs, as well as the XML dump called “viewLog.php”. From there, you can also delete the logs and dumps to make more room. Viewing the CDR process logs: hit the “view CDR log” button

Viewing the XML dumps: hit the “view XML Dump” button

Notes

Date & Time

¹ The Date/Time of the call refers to time the call started, whereas the date/time of connection & disconnection refer to as the call connected, and the call terminated, respectively.

Duration

² Payable duration, or billable duration, is a calculated field derived from the difference of the termination time of the call and the connection time of the call. Informational duration is also a calculated field derived from the difference of the termination time of the call and the time of the call started.

Evaluation Rule

³ The term longer/shorter than, will be evaluated as longer/shorter than or equal to.

Basic CDR Only

⁴ Only basic CDR information is available when saving, and printing the CDR listings.

Date Range Constraint

⁵ You will not be able to specify the time period longer than 7 days. Also, data is not collected correctly if the selected week range includes both months, such as 5/29/2007 thru 6/5/2007.

Grant Admin

⁶ Alternatively, you can click on the admin checkbox on the account list.

User Mode

⁷ In normal user mode, "Edit Account" feature is the only action the account holder can do.

Delete Protection

⁸ The administrator cannot delete itself nor change administrator mode, while currently online.

Settings Info

⁹ There are several uses for setting changes. For instance, changing a table name every year would be useful for having a list of CDR tables by year. Doing so requires you to run setup.php (very similar to the settings page) and run install_db_2_x.php which will not overwrite the database, but will create a new table based on setup information in setup.php. For more information please see the read_me documentation that came with pbxnsip CDR tool package.

Maximum Index

¹⁰ You can achieve getting the maximum index by browsing to the config directory using the ftp browser or similar and look for the highest number displayed.

CAUTION

¹¹ Please exercise caution when trying to repair the configuration, or restoring the configuration to the previous value by what you remember, because in doing so, another back up is created, which will also contain the new configuration that may not work. If you perform the appropriate steps as described, and the configuration still does not work, repeat the step but using /config/restore_config.N-1.php, until the proper configuration data is restored.