Reference9:Concept Reporting: Difference between revisions
Line 11: | Line 11: | ||
If there is no error during the installation you will see at the end "Installation was succesfull". Otherwise, | 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. | 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. | |||
<br> | |||
<br> | |||
Upload this hotfix archive [[Reference9:Concept_Linux_Application_Platform#Upload.2FUpdate|here]]. | |||
==Configuration== | ==Configuration== |
Revision as of 16:18, 2 August 2011
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.
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)
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.
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.
Remote Interface
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> .... <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)
- ev
- 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.
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.
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
- [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.
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.