Reference9:Concept Reporting

From innovaphone wiki
Revision as of 12:45, 9 March 2016 by Ckl (talk | contribs) (→‎LDAP)
Jump to navigation Jump to search
There are also other versions of this article available: Reference9 (this version) | Reference10 | Reference12r1

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 Reporting...HotfixIncremental for your platform (VM or IPxx10) or if you have missed some hotfixes, download the Reporting...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 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.

For the PgAdmin III it is imporant to use innovaphone-reporting as Service-DB (Wartungs-DB). Default login credentials - User: innovaphone-reporting - Password: reporting

Delete CDRs

You can delete CDRs for certain/all users in a certain timespan. If you enter % as object, all users are selected.

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.

Ldap update.png

If the country where you configure LDAP does not have Trunk/National prefix you should leave "National Prefix" and "Area Code" empty.
Name and Number attributes are mandatory.

In the number Attributes field please do not add any additional characters to the attributes like ":P", ":D" or "@", for instance "homePhone:P, mobile:M, telephoneNumber:D". This does not work. That would be correct: "homePhone, mobile, telephoneNumber".

Report Mails

Automatic report generation can be configured here:

  • time: daytime at which the report is sent
  • interval: daily, weekly or monthly report
  • day: the day, at which the report is sent
  • mail adresses: recipients of the report (comma separated)
  • compress: compress the attachment or not
  • sort by
  • sort order
  • language
  • send time: the time of a day, when the report is sent

The explanation for the other settings can be found here.

Mails.png


Images

You may upload images to be used as logo or footer in pdf reports.

Logos must be 32x32 and footer can have a maximum width of 450 pels and a maximum height of 50 pels.

These images can have jpeg or png format.

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.txt. The file is rotated on 1 MB size. The first rotated file is uncompressed, further files will be gz compressed. Max six files are kept. Older files will be deleted:

  • cdr.txt
  • cdr.txt.1
  • cdr.txt.2.gz
  • cdr.txt.3.gz
  • cdr.txt.4.gz
  • cdr.txt.5.gz

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

Filter.png

There are five or seven condition sections depending on what is selected under States. Conditions inside same section are OR associated while different sections are AND associated.

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

Base filter

If you select another filter as base filter, the conditions of the base filter are implicitly AND conjuncted, when the current filter is used.
So if the base filter defines two PBX values, e.g. "Berlin" and "Hamburg" and the current filter defines one PBX value, e.g. "Berlin", only objects of the PBX "Berlin" will be filtered:

(pbx="Berlin") AND (pbx="Berlin" OR pbx="Hamburg")

If a user defines an own filter for his reports, he has to use a base filter, which he is allowed to use. So each filter, which he uses, will have at least one base filter condition, which the admin has defined.

You have to take care with the conditions of the current filter and the base filter, 
as it is now easy to create a filter with mutually exclusive conditions, e.g. if 
the filter has pbx="Berlin" and the base filter pbx="Hamburg" => no matching records.

Anonym

If you check this flag, a report created with this filter or with a filter, where this filter is a basefilter, will by anonymized.

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

Call Duration

Time the call was connected.

This condition will be AND with the others if all States are selected.
If Busy, No Channel or No Response is unselected, then the filter will search for Connected calls with connected time greater or smaller than Call Duration.

Alert Duration

Time the call was ringing independent of the status of the call afterwards.

This condition will be AND with the others if all States are selected.
If Busy, No Channel or Connected is unselected, then the filter will search only for Not Connected Calls that were ringing more or less time than Alert Duration.

Report times

Define the time where calls should be counted.

Images

  • Logo: This must be 32x32 pixel image in jpeg or png format.
  • Footer: Maximum width of 450 pels and maximum height of 50 pels. Also jpeg or png format.

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.

Report hide.png

  • Object (Long Name): corresponds to the PBX Long Name assigned to a specific user (% is a special postgresql character, which matches any string)
  • Filter: one or multiple filters can be defined (OR joined)
  • Group by: the result can be grouped by Date or Object
  • Hide last X digits: you may 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.
    • Actually, the pbx/phys value of a call is matched against the LDAP pbx value and if the number starts with the external line prefix.
  • Anonym: do not show internal names/numbers
    • If anonym is set, hide last digits is set to 3, if not set
  • Filter Info: the report will contain information about the configured settings and filter information
  • Displayed Duration:
    • Call Duration (Total): the total call duration, e.g. with the duration of a subsequent transfer
    • Call Duration (User): just the time, the user was connected
    • Billing Duration: the duration, a user must be billed for
    • None of these durations contain the alert duration, which is separatly listed

A normal report. The summary shows incoming / outgoing summaries

A grouped report. Each group has an own summary

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.
You can also download a report in csv format without call history. The report uses comma as delimeter character, " as text qualifier and PBX Object/Remote Party columns should be defined as Text (avoid telephone numbers to be considered as numbers by Excel). UTF-8 must be used as character set.

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.

If you restore the reporting database, you should keep in mind, that this might take several hours for a system
on a CF card, depending on the size of the restored database. The backup process itself is quite fast with a few minutes.

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>php5-ldap</package>
       <package>postgresql-8.4</package>
       <package>postgresql-common</package>
   </packages>
   <database name="innovaphone-reporting" username="innovaphone-reporting"/>
   <cronjobs>
       <cronjob script="/etc/cron.d/reporting_replication"/>
       <cronjob script="/etc/cron.d/reporting_cleanup"/>
       <cronjob script="/etc/cron.d/reporting_mails"/>
   </cronjobs>
   <log-files>
       <log title="Postgresql-8.4" no-clear="true">/var/log/postgresql/postgresql-8.4-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 title="innovaphone Reporting Replication">/var/www/innovaphone/apps/innovaphone-reporting/log/reporting_replication.log</log>
   </log-files>
   <config-files>
       <conf>/etc/postgresql/8.4/main/pg_hba.conf</conf>
       <conf>/etc/postgresql/8.4/main/postgresql.conf</conf>
       <conf>/home/innovaphone-reporting/reporting.conf</conf>
       <conf>/home/root/ssl_cert/server.key</conf>
       <conf>/home/root/ssl_cert/server.crt</conf>
       <conf>/etc/cron.d/reporting_replication</conf>
       <conf>/etc/cron.d/reporting_cleanup</conf>
       <conf>/etc/cron.d/reporting_mails</conf>

<conf>/var/www/innovaphone/apps/innovaphone-reporting/report_images</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 restart</action>
           <action>/etc/init.d/cron restart</action>
       </command> 
       <command title="Restore innovaphone Reporting database" cmd="restoreinnovaphone-reportingdb" script=""/>
   </restore>
   <uninstall script="/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'">
       <to-delete>
           <to-del path="/usr/local/bin/reporting_cleanup.sh"/>
           <to-del path="/usr/local/bin/reporting_replication.out"/>
           <to-del path="/usr/local/bin/reporting_replication.sh"/>
           <to-del path="/home/innovaphone-reporting/tmp"/>
       </to-delete>
   </uninstall>
   <lighttpd-auth>1</lighttpd-auth>
   <public-web-paths>
       <path>mypbx</path>
       <path>user</path>
   </public-web-paths>

Installed Debian Packages

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

  • php5-pgsql
  • php5-gd
  • php5-ldap
  • 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

The node description also applies for the CSV columns!

 <report>
   <record>
     ....
     
     <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
    • empty: direct call
    • ct: call transfer
    • cf: call forward
    • cct: call transfer with consultation
    • pu: call pickup
  • 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/LDAP/Report Mails/Users

The master server does nothing here, but the standby deletes all filters, ldap servers, report mails and users and replicates all from the master.
So these items 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
    • csv
  • [sortby] : the report sorting, allowed values:
    • time: default
    • obj: sort by pbx object name
    • call-duration
    • alert-duration
    • dir: sort by call direction
  • [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...
  • [filterinfo]: show the filter information in a created report ("true" or "false")
  • [lang]: the language, e.g. "en" or "de"
  • [displayedduration]:
    • call_duration: default Call Duration (Total)
    • conn_duration: Call Duration (User)
    • billing_duration: Billing Duration
  • [anonym]: anonymize internal names/numbers ("true" or "false")
Parameters in [] are optional.
pbxobject supports  PostgreSQL Patterns .
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.

  • Reporting conn.jpg: a connected call
  • Reporting busy.jpg: busy destination
  • Reporting no res.jpg: no response from destination
  • Reporting error.jpg: an error occurred
  • Reporting ct.jpg: call transfer
  • Reporting cct.jpg: call transfer with consultation
  • Reporting cf.jpg: call forward
  • Reporting pickup.jpg: call pickup (or SOAP redirect)
  • Reporting dir.jpg: call direction

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

  • Reporting busy callback.jpg

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.