Child pages
  • CDR Extras
Skip to end of metadata
Go to start of metadata

Setting up iReports on a Windows PC

  1. Download the iReports Windows installer
  2. Download the PostgreSQL JDBC4 driver
  3. Copy the JDBC driver to the lib directory of Jasper Reports: e.g. C:\Program Files\JasperSoft\iReport-2.0.4\lib
  4. Allow connections over TCP to the sipXecs database:
    1. Add the following line to /var/lib/pgsql/data/pg_hba.conf: host all all 192.168.5.0/24 trust
    2. Add the following line to /var/lib/pgsql/data/postgresql.conf: listen_addresses = '*'

Documentation

Additional user and developers documentation is available which can be found in the doc directory of the source tree. In particular please refer to:

In addition, this older document describes the existing CSE logging to XML, and an early proposal for CDR generation.

Using pgAdmin to view CDRs

pgAdmin III is the most popular and feature rich open source administration and development platform for the PostgreSQL database. It can be used to look at CSE / CDR records as they get stored in the database. In order to be able to connect to the sipX database from another host access rights have to be granted to that host in the PostgreSQL configuration file. The following example illustrates how to grant access to a specific host with the IP address 192.168.5.194:

{{Box File| /var/lib/pgsql/data/pg_hba.conf|

#Add the following line:
host all all 192.168.5.194 255.255.255.255 trust

}}

In addition, in the postgresql.conf file you have to enable the Postgresql server to actually listen on on the hosts Ethernet interface. Do this by uncommenting the parameter listen_addresses with the parameter * as follows (applies to postgresql-server release 8):

{{Box File| /var/lib/pqsql/data/postgresql.conf|

#Uncomment this line and set the parameter to *
listen_addresses = '*'

}}

After making this change, run pg_ctl as the user "postgres" to tell PostgreSQL to reload the configuration:

pg_ctl reload -D /var/lib/pgsql/data

You could just restart the PostgreSQL service, but if the PBX is running, then that would disrupt call state event logging.

The pgAdmin application is available for a wide range of platforms including Linux, Solaris, and Windows. The following example is taken from pgAdmin running on Windows XP. The fields Address and Description are mandatory. For Maintenance DB select template1. The default username is postgres with an empty password. Once the connection to the sipX server is established you can open the SIPXCDR database. Under Schemnas, Tables you will find the call_state_events and cdrs entries. In addition you can look at the database SIPXCONFIG, which includes the configuration parameters as used by the sipX configuration server.

Using the Query Tool to create a Report

Unfortunately you still have to know postgres query syntax to create reports. The following gives you an example.

Select the SIPXCDR database on the left and then select the Query tool from the Tools menu. In the Query window that opens type in a valid command such as:

select * from view_cdrs limit 5;

Now select Execute from the Query menu or select the execute button on the toolbar. This will display the first 5 entries. The fields have the following meaning:

  • id: The DB row ID
  • caller_aor: The Address of Record for the caller
  • start_time: The time when the call started
  • connect_time: The time when the call connected
  • end_time: The time the call ended
  • duration: Call duration
  • termination: A single character termination code
  • failure_status: If the call failed this field contains the SIP error code
  • failure_reason: If the call failed this field contains error message

Howto Export CDRs from the Database to a CSV File

  • You can use the psql command line tool to export data. For example:

    psql -At -F "," SIPXCDR -U postgres -c "select * from view_cdrs" > cdrs.csv

    will dump the important CDR data into a CSV file that can then be imported directly into Excel. See http://www.varlena.com/GeneralBits/40.php for more information. Unfortunately the CSV file won't have column headers.
  • Databases have powerful sorting/searching capabilities although Excel is more familiar/friendly – the database wins when you have large amounts of data, such as CDRs for a PBX with thousands of users. Here's an example of what you could do with the database:

    psql -U postgres -d SIPXCDR -c "select * from view_cdrs where caller_aor = 'sip:alice@example.com'"

    to show all CDRs where Alice is the caller.
  • By googling on "postgresql export data" we found a product that exports to formats like Excel. See PostgreSQL Export Tool.
    Caveat emptor: we have not evaluated this product.

Troubleshooting

Here is a command line that will tell you how many call state events have been logged:

psql -U postgres -d SIPXCDR -c "select count(star) from call_state_events;"

If CSEs are being logged correctly, then you should see the count go up steadily as people make calls. It will periodically drop when the CSE purge kicks in to purge old data, which we plan to do on a nightly basis. Here's a BASH command that will run this in a loop, every five minutes:

while true; do psql -U postgres -d SIPXCDR -c "select count(star) from call_state_events;"; sleep 300; done


  • No labels