Reference9:Concept Reporting

Requirements

It is needed to have the application platform installed and running.

Installation

Download the latest version of innovaphone reporting.

Log into the application platform, go to the Applications tag, click on Upload/Update and upload the downloaded file. The installation will start automatically and the page will refresh every two seconds showing the installation process. If there is no error during the installation you will see at the end "Installation was succesfull". Otherwise, you will get "installation failed" and the reason why it went wrong.

Configuration

Device

It is required to have a Reporting license installed on your device. Check under Reference9:General/License if you already have one.

Set the Reporting flag for each PBX object, which should be reported on the objects properties page.

Any innovaphone device which sends CDRs to the reporting application must have at least one CDR interface configured under Reference9:Gateway/CDR :

• Type: LOCAL-AP or REMOTE-AP/REMOTE-AP-S

If HTTP/HTTPS is selected then some other parameters must be set:

• Address: ip address of the application platform
• Port: 80
• Method: POST/GET
• Path: ap/cdr.fcgi

The application platform is by default password protected, so you'll have to perform one of these steps:

• configure an authenticated URL for ap/cdr.fcgi under Reference9:Services/HTTP/Client
• configure ap/cdr.fcgi as public web path on the innovaphone application platform Public Web Paths

innovaphone Application Platform

If the reporting URL/user/password is not configured in your device as authenticated URL, configure ap/cdr.fcgi as public web path on the innovaphone application platform Public Web Paths .

Database

Reporting installs postgreSQL version 8.4 as database management system and creates the innovaphone-reporting database to store CDRs sent from any innovaphone devices (appendix 7.6 explains the database structure). PostgreSQL is also available for other applications and any of them could create its own database.

Password

Reporting creates the database user innovaphone-reporting with default password reporting. This password may be changed at the innovaphone Reporting page under Config/Database.

Remote Access

There are tools (PgAdmin III) that allow to connect to application databases remotely. It is first needed to configure the IP you are connecting from under Config/Database at the innovaphone reporting page.

Filters

General

Filters are set up to create reports based on certain filter conditions. A filter can have multiple conditions and multiple filters can be combined on report creation (combine is performed with an OR).
A filter has a unique name with an optional description.

Conditions

There are five condition sections. Each condition in one of this sections is OR associated with another condition in the same section.
The sections are AND associated.

Local

Define one ore more conditions for the local party, defined by:

• Object: the cn/PBX object name
• Number: the number
• Groups: a group of the PBX object

Remote

Define one ore more conditions for the remote party, defined by:

• Number: the number
• Remote display name: the display name

PBX

Define one ore more conditions for the PBX, defined by:

• PBX: the PBX name
• Node: the node name

States

• No Response
• Connected
• Busy
• No Channel

Directions

• Incoming
• Outgoing
• Transfer: calls, which have been transferred to the current local party
• Call Forward: calls, which have been forwarded to the current local party

Report Creation

General

It is possible to generate call lists for single users or more complex reports by means of filters.

Web Interface

Reporting application provides a web interface to generate reports.

Object (Long Name) corresponds to the PBX Long Name assigned to a specific user (% is a special postgresql character, which matches any string)

The result can be grouped by Date or Object.

Backup and Restore

You can both manually and automatically backup the database and configuration files for Reporting under Administration/Backup in the application platform.

Configuration details for the update server can be found here.

Configuration Files

saveinnovaphone-reportingcfgs is the command used to automatically save configuration files with the Command File.

Data

saveinnovaphone-reportingdb is the command to automatically backup the whole innovaphone Reporting database with the Command File.

Logs

saveinnovaphone-reportinglogs to save log files with the Command File.

Appendix

innovaphone Reporting XML

   <?xml version="1.0" encoding="utf-8"?>
   <application>
   <name>innovaphone Reporting</name>
   <desc>Reporting software</desc>
   <directory>innovaphone-reporting</directory>
   <href>report.php</href>
   <linux-username>innovaphone-reporting</linux-username>
   <packages>
       <package>php5-pgsql</package>
       <package>php5-gd</package>
       <package>postgresql-8.3</package>
       <package>postgresql-common</package>
   </packages>
   <database name="innovaphone-reporting" username="innovaphone-reporting"/>
   <cronjob username="www-data" script="reporting_cleanup.sh"/>
   <log-files>
       <log title="Postgresql-8.3">/var/log/postgresql/postgresql-8.3-main.log</log>
       <log title="Reporting cleanup">/var/www/innovaphone/apps/innovaphone-reporting/log/reporting_cleanup.log</log>
       <log title="innovaphone Reporting">/var/www/innovaphone/apps/innovaphone-reporting/log/innovaphone-reporting.log</log>
   </log-files>
   <config-files>
       <conf>/etc/postgresql/8.3/main/pg_hba.conf</conf>
       <conf>/etc/postgresql/8.3/main/postgresql.conf</conf>
   </config-files>
   <backup>
       <command title="Save innovaphone Reporting configuration files" cmd="saveinnovaphone-reportingcfgs" script=""/>
       <command title="Save innovaphone Reporting database" cmd="saveinnovaphone-reportingdb" script=""/>
       <command title="Save innovaphone Reporting log files" cmd="saveinnovaphone-reportinglogs" script=""/>
   </backup>
   <restore>
       <command title="Restore innovaphone Reporting configuration files" cmd="restoreinnovaphone-reportingcfgs" script="">
           <action>/etc/init.d/postgresql-8.3 restart</action>
       </command> 
       <command title="Restore innovaphone Reporting database" cmd="restoreinnovaphone-reportingdb" script=""/>
   </restore>
   <uninstall/>
   </application>

Configuration Files

pg_hba.conf is the client authentication configuration file and controls which hosts are allowed to connect, how clients are authenticated, which PostgreSQL user names they can use and which databases they can access.

postgresql.conf is the PostgreSQL configuration file

Report XML

 <report>
   <record>
     ....
     <remote name="saturn" number="101" dn="Saturn"/>
     <status>co</status>
     <type>ct</type>
     <dir>i</dir>
     <billing-duration seconds="40">0:40</billing-duration>
     <call-duration seconds="40">0:40</call-duration>
     <conn-duration seconds="20">0:20</conn-duration>
   </record>
 </report>

• remote: outgoing calls -> dialed endpoint
• remote: incoming calls -> first ep2 above entry event or entry event if this is the first one
• remote: incoming calls with incoming transfer after entry event -> transfer target
• status: outgoing calls -> best found status in call flow
• status: incoming calls -> status of entry event
• type: the type of the entry event or, if not set, the type of the next event, if given
• dir: o (outgoing) if the first event is the entry event and the caller is ep1
• dir: i (incoming) if not o
• billing-duration: outgoing call -> duration from the first event until the last event in the flow
• billing-duration: incoming call -> duration from the first transfer/forward event until the last event in the flow
• call-duration: duration of the whole call
• conn-duration: duration from the entry event until the event, where the local endpoint isn't connected any more

Database Structure

cdrs

Each received cdr-xml is formed by a cdr tag and an undefined number of event and group tags. (Refer to: CDR)

This section is composed by four tables: cdrs, events, groups and cdr_properties. The first three tables contain the corresponding fields to store the information contained in the cdr, event and group tags.

• cdr fields: id, guid, sys, pbx, node, device, cn, e164, h323, dir, utc, local.
• event fields: id, msg, type, e164, h323, conf, cause, time, cdr_id, order_index.
• group fields: id, name, active, type, cdr_id.

id field is assigned by postgresql for each entry and has nothing to do with the information present in the cdr. It is different for each entry inside the table.

The events table has two additional fields, called cdr_id, to associate these events to the corresponding cdr entry and order_index to keep their position inside the cdr. Groups table has also the cdr_id field to associate the group entries to the corresponding cdr and events entries.

The cdr_properties table contains relevant information associated with the call such as call_length, alert_length or call_flow. It also presents a cdr_id field.

filters

This section is composed by three tables: filter_columns, filter_conditions and filters.

• filter_columns is not used for the time being. This is supposed to contain the columns the user wants to see in his report, but right now, just default columns (Time/Object/Duration/Left/Direction/State/Right) are shown.
• filter contains the filter name and description plus two conditions call_state (No Response, Connected, Busy and No Channel) and direction (Incoming, Outgoing, Transfer and Call Forward).
• filter_conditions contains the defined conditions for a filter

All three tables are related through an id.

missed_calls

This table contains a timestamp for the last report access by a user with mypbx. It is used to retrieve information about missed calls since the last use of mypbx.