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.

Hotfix

If you have already installed the latest version of Reporting, simply download the Linux...HotfixIncremental for your platform (VM or IPxx10) or if you have missed some hotfixes, download the Linux...HotfixCumulative archive, which contains all hotfixes since hotfix1.

Upload this hotfix archive here.

Configuration

Device

License

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.

CDR Gateway

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 for REMOTE-AP
• Port: 443 for REMOTE-AP-S
• 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 (recommended)
• configure ap/cdr.fcgi as public web path on the innovaphone application platform Public Web Paths

Configure a second CDR interface, if you use Replication.

Enable PBX CDRs

Enable the "Generate CDRs" flag under Reference9:PBX/Config/General .

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.

Replication

You can configure a second innovaphone application platform with an installed innovaphone Reporting for replication purposes.

• Mode:
  • Off: no replication
  • Master: the local server is the master server
  • Standby: the local server is the standby server, filters will be replicated from the master and can't be changed here
• Master/Standby Server: the IP address of the master or standby server
• Database password: the PostgreSQL database password of the other server (default is reporting).
• Synchronisation interval: the interval in minutes (range 1-60) in which a sync is done

Now configure a second CDR Gateway for the standby server.

The current status can be seen here too. If the status is Failed take a look at the log file innovaphone Reporting Replication under Diagnostics/Logs or look at the last trace output at the bottom of the page.

How replication works is described here.

LDAP

Several LDAP servers and dialing locations can be configured here.

PBX field must correspond to a name of a PBX since PBX's names are available in CDRs. The received CDR is parsed to get the PBX name and this name will be used to find the corresponding LDAP server and dialing location needed to resolve names.

Each configured LDAP server and dialing location can be activated/deactivated with the active flag.

Mails

Automatic report generation can be configured here.

Interval: daily, weekly or monthly.

You can also set up at which time the report will be sent.

Relay CDRs

Relay CDRs can be stored in log files by the Reporting application. Configure a CDR gateway as already described and relay CDRs will be written to the webdav file /cdr/cdr.x.txt.

innovaphone-reporting web access

You can configure a separate authentication for the innovaphone-reporting web site here.
One can just login on the reporting site with this access.

Filters

General

Filters are set up to create reports based on certain 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. Conditions inside same section are OR associated while different sections are AND associated.

(Object == Terra OR Object == Uranus) AND (PBX == sifi) AND (Number == 0049%)

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

Using Patterns

You can use these PostgreSQL Patterns for every condition.

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).
One or multiple filters can be defined (OR joined). If you define one or more filters, the Object (Long Name) will be ignored.

The result can be grouped by Date or Object.

You may also replace the last digits of an external number with a '*'. LDAP must be configured in order to use this capability but if you want no LDAP funtionality, just unset the Active Box in LDAP.

Print

Click the print icon to open a printable version of the report.

Save

You can save the report as pdf or as xml by using the corresponding icon.

Sorting

Each sortable column has a clickable column head. This sorting will be also used when opening the printable version or saving the report as PDF.

Icons

The icons are explained in the appendix.

Remote Interface

See Remote Interface Appendix

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

PostgreSQL Patterns

For filter conditions or for the pbx object value you can use the following patterns inside a string:

• %: matches any string of zero or more characters
• _: matches any single character

Example:
Sat% will match all pbx objects, which start with the string Sat.

We do always perform case insensitiv searches!

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"/>
   <cronjobs>
       <cronjob username="www-data" script="/usr/local/bin/reporting_cleanup.sh"/>
       <cronjob username="www-data" script="/usr/local/bin/reporting_replication.sh"/>
       <cronjob username="www-data" script="/usr/local/bin/reporting_replication.out"/>
   </cronjobs>
   <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>
       <conf>/home/innovaphone-reporting/reporting.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>/usr/bin/sudo /usr/local/bin/uninstall_script.sh -f "innovaphone-reporting.xml" -linux_user yes -linux_group yes -database yes -database_user yes -log_script "/var/www/innovaphone/log/appl_uninstall.log"</uninstall>
   </application>

Installed Debian Packages

The following packages (and their dependencies) are installed with innovaphone Reporting:

• php5-pgsql
• php5-gd
• postgresql-8.4
• postgresql-common

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

Data Files

If you backup the innovaphone reporting database, you'll get a gz archiv, which contains the whole innovaphone-reporting database.

Report XML

 <report>
   <record>
     ....
     2011-03-28 11:19:26
     <obj>Saturn</obj>
     <id>870998fee909d311b2ec009033105dc9</id>
     <call>int</call>
     <alert-duration seconds="0">0:00</alert-duration>
     <flow>
       <ev caller="ep1" ep1_number="101" ep1_name="saturn" ep1_dn="Saturn" 
                        ep2_number="102" ep2_name="uranus" ep2_dn="Uranus" status="co" entry="true"/>
     </flow>
     <call-duration seconds="2">0:02</call-duration>
     <billing-duration seconds="2">0:02</billing-duration>
     <conn-duration seconds="2">0:02</conn-duration>
     <groups>
       <group>test</group>
       <group>test2</group>
     </groups>
     <node>root</node>
     <pbx>.</pbx>
     <cause></cause>
     <dir>o</dir>
     <status>co</status>
     <type/>
     <remote number="102" name="uranus" dn="Uranus"/>
     <callback  number="456" name="abc" dn="123" time="1301305962">2011-03-28 11:52:42</callback>
   </record>
   ....
 </report>

• time: attribute time is a unix timestamp (referring to the local time stamp of the CDR) and the node contains a formatted time string
• obj: the PBX object name (also called cn)
• id: the conference id of the call
• call: int (internal) or ext (external) call
• flow: the call flow
  • ev
    • ep1 (ep1_number, ep1_name, ep1_dn): the left side of the call
    • ep2 (ep2_number, ep2_name, ep2_dn): the right side of the call
    • caller: ep1, ep2 or empty, if unknown
    • status: co (connected), al (alert), er (error) or bu (busy) state of the current event
    • entry: true for the entry event of the local object
    • type: cf (call forward), ct (call transfer)
• groups: the groups of the PBX object
• node: the node of the PBX object
• pbx: the pbx of the PBX object
• cause: the decimal disconnect reason of the call (see Reference:ISDN_Cause_Codes)
• 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
• alert-duration: alert time
• 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
• callback: name, number, dn and time if this call has been callbacked using mypbx

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.

user_properties

This table contains a timestamp missed_calls_time for the last report access by a user cn with mypbx. It is used to retrieve information about missed calls since the last use of mypbx.
The timestamp clear_report_time indicates the last clearance time.

replication

Contains information of the last replication run, which is used to sync master/standby installations.

Replication

The replication between the master and standby servers is divided into two steps:

CDRs

Both master and standby server keep an information about the last replicated CDR. From this CDR on, they retrieve all CDR guids (32 bytes) from all new CDRs from the other server. If a guid already exists in the local database, the CDR is skipped, otherwise it is replicated.
So there is a minimal data exchange for already existing CDRs and each new CDR is only processed once.

Filters

The master server does nothing here, but the standby deletes all filters and replicates all filters from the master.
So all filters from the master server can be used, but not changed, on the standby server.

Remote Interface

You can retrieve the report xml/pdf with a remote interface.
Make a POST or GET HTTP request to http(s)://Linux-IP/apps/innovaphone-reporting/report.php with the following parameters:

• remote: mandatory for a remote request!
  • 1
• from : the from date/time as UNIX timestamp OR date/time string as described [ http://www.php.net/manual/de/datetime.formats.php | here ].
• to: the to date/time (format see from)
• [format]: the return format, allowed values:
  • xml: default
  • pdf
• [sortby] : the report sorting, allowed values:
  • time: default
  • obj: sort by pbx object name
  • conn-duration
  • dir: sort by call direction
  • status: sort by call status
• [sortorder]: the report sort order, allowed values:
  • ascending: default
  • descending
• [groupby]: report grouping
  • default empty/not set: no grouping
  • local_stamp: group by time
  • cn: group by pbx object name
• hidedigits: hide the last X digits of external numbers
• pbxobject: search for this pbx object or
• filterX: use a filter with its name. You can use multiple filters with filter1=test, filter2=test2...

Parameters in [] are optional.

pbxobject supports  PostgreSQL Patterns .
If pbxobject is used together with filters, it will be ignored!

Example:
https://172.16.14.123/apps/innovaphone-reporting/report.php?remote=1&from=2011-03-29&to=2011-03-30&format=xml&pbxobject=Saturn
Gets all records between 2011-03-29 and 2011-03-30 for the pbx object Saturn.

Example:
https://172.16.14.123/apps/innovaphone-reporting/report.php?remote=1&from=2011-03-29&to=2011-03-30&format=xml&filter1=test&filter2=test2
Gets all records between 2011-03-29 and 2011-03-30 for the filters test and test2.

Tools

NetDrive

NetDrive is a usefull webdav client, which can be used to access webdav of the innovaphone application platform.

PGAdmin

PGAdmin is an administration tool for PostgreSQL databases.

Putty

Putty is SSH client to connect to the linux application platform.

Call list icons

The direction of the icon arrow depends on the call flow.

• : a connected call
• : busy destination
• : no response from destination
• : an error occurred
• : call transfer
• : call transfer with consultation
• : call forward
• : call direction

A small arrow on a call state icon indicates a callback, e.g.:

• 

Known Problems

Replication

Callback information

The callback information get lost in the following scenario:

• The callback CDR is already replicated on both master and standby
• Master or standby is down
• A user calls back the CDR from point 1

Missed calls

The information for missed calls is not replicated. So the first time, a standby server is used with mypbx, the missed calls information will be wrong. The second time will be correct again.

CDRs

If the innovaphone Reporting is not running, for whatever reaseon, the device, which sends CDRs, currently stores up to 300kB CDRs data. So if one CDR has up to 2kB, 150 CDRs can be stored until the innovaphone Reporting should be up again, to avoid data loss.