Reference14r1:Concept App Service Reports

There are also other versions of this article available: Reference13r1 | Reference13r2 | Reference13r3 | Reference14r1 (this version)

The App Service Reporting is an App Service which can be installed on an innovaphone App Platform. It is used to provide call lists in myApps and to create call reports.

Applies To

innovaphone PBX from version 13r1

Technical Overview

Apps

Reports App (innovaphone-reporting)

This is the Reporting UI App.

Legend of used symbols:

You can display all or filtered calls and sort them by time, name, call and alert duration. The amount of displayed entries in the UI is limited to 10000.

Parameters:

Websocket

Call List App (innovaphone-calllist)

This is the Call list App.

Parameters:

Websocket

Call List API (innovaphone-calllist-api)

This is an App, which provides the Call list API (com.innovaphone.calllist). This API can be used to find the recent calls of a user.

Parameters:

Websocket

PBX Manager Plugins

Reporting

With the Reporting plugin App objects can be created, edited and deleted for the provided Apps.

Configuration

Following configuration options are offered:

CDR handling
- automatic deletion of CDRs
- deletion interval - default is 90 days. If configured, deletion is performed at 3 a.m. UTC.

Account for CDR authentication

Account for authentication for external apps (When username and password are defined external app will be able to fetch PDF,XML or CSV report with HTTP GET requests using HTTP Authentication.)

SMTP Configuration

When reports should be send by email the SMTP Server address and the login credentials must be specified. An example configuration can be seen on the screen shot.

Concepts

Database Structure

cdrs

The service receives CDRs in XML format at the URL http://AP-IP/DOMAIN/reporting/cdr . That URL must be configured at the PBX.

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.
Each CDR represents the call in the view of one PBX object. So there might be multiple CDRs for one call if multiple PBX objects are used within the call.

cdr fields:
- id
- guid
- user_guid
- conf
- sys
- pbx
- node
- phys
- device
- h323
- e164
- cn
- dn
- email
- dir
- external
- licensed
- more_calls: set inside the CDR XML if there are multiple calls for the same call and user (e.g. multiple registrations)
- conn_duration
- alert_duration
- call_duration
- billing_duration
- utc_stamp: unix timestamp in milliseconds since 1970
- cause
- remote_e164
- remote_h323
- remote_dn
- call_list: CDR which is suitable for calllist or report queries (this flag is set depending on data received inside a CDR)
- missed_call
- status: the final state of the CDR (co: connected, al: alerting, er: error, bu: busy)
- further: flag is set if CDR has been cancelled with cause 26 (non selected user clearing) indicating that there will be another more suitable CDR for the same call
- user_id
- callback_e164
- callback_h323
- callback_dn
- callback_time
- remote_clir: this flag is set if the remote side suppresses the calling information (number, name etc.)

event fields:
- id
- cdr_id
- msg
- ext
- e164
- h323
- dn
- conf
- cause
- time: the kernel uptime of the sending gateway in seconds. Substract time from a newer event (higher id) of a previous event to get the elapsed seconds inbetween.
- clir

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 table cdrp_properties from version 10 has been removed. Most columns can be now found directly in the table cdrs.
If you need the callflow in a similar way, you can JOIN the events table (GROUP BY cdr_id) and run a query which creates an array string from the events table:

array_agg(array[ev.msg, COALESCE(ev.e164,), COALESCE(ev.h323,), COALESCE(ev.dn,)] ORDER BY ev.id) AS callflow

cdr_users

This table contains a timestamp missed_calls_time for the last call list access by a user sip. It is used to retrieve information about missed calls since the last use of mypbx/myApps.
The timestamp clear_report_time indicates the last clearance time.

Database query samples

Calllist query

SELECT cdr.id, cdr.cn, cdr.conf, dir, external, alert_duration, call_duration, conn_duration, billing_duration, 
   sys, pbx, node, phys, cdr.cause, utc_stamp, status, further, 
   CASE WHEN remote_clir THEN '' ELSE remote_e164 END, 
   CASE WHEN remote_clir THEN '' ELSE remote_h323 END, 
   CASE WHEN remote_clir THEN '' ELSE remote_dn END, 
   callback_e164, callback_h323, callback_dn, callback_time, cdr.e164, cdr.h323 
FROM cdrs cdr 
JOIN cdr_users cu ON cdr.user_id = cu.id 
WHERE cu.cn NOT IN ('_ADMIN_','_KADMIN_','_EXTERN_','_UNKNOWN_','_MASTER_','_STANDBY_','_ACTIVE_','_MOH_','_HTTP_','_CONF_') AND (cu.sip = 'sip' OR cu.guid = '00000000000000000000000000000000') 
   AND cdr.call_list = true 
   AND (cu.clear_report_time IS NULL OR utc_stamp > cu.clear_report_time) 
ORDER BY utc_stamp DESC, cdr.id DESC LIMIT '5' OFFSET '0'

Reporting query

WITH inp AS (SELECT 1611187200000::BIGINT AS from, 1611211143042::BIGINT AS to, 'sip'::VARCHAR AS sip, 'cn'::VARCHAR AS cn, 'TRUE'::BOOLEAN AS check_objects) 
SELECT cdr.id, cdr.cn, cdr.conf, dir, external, alert_duration, call_duration, conn_duration, 
   billing_duration, sys, pbx, node, phys, cdr.cause, utc_stamp, status, further, remote_e164, 
   remote_h323, remote_dn, callback_e164, callback_h323, callback_dn, callback_time, cdr.e164, 
   cdr.h323, cdr.dn, 
   array_agg(array[ev.msg, COALESCE(ev.e164,''), COALESCE(ev.h323,''), COALESCE(ev.dn,''), 'false'] ORDER BY ev.id) 
FROM inp, cdrs cdr 
JOIN cdr_users cu ON cdr.user_id = cu.id 
JOIN events ev ON ev.cdr_id = cdr.id 
WHERE cdr.call_list = true 
   AND cdr.licensed = true 
   AND utc_stamp >= inp.from 
   AND utc_stamp < inp.to 
   AND (inp.check_objects=false OR LOWER(cu.sip) LIKE LOWER(inp.sip) OR LOWER(cu.cn) LIKE LOWER(inp.cn)) 
GROUP BY cdr.id 
ORDER BY utc_stamp ASC 
LIMIT 10000

Database access

You have to grant database access in the AP-Platform.
The database user and password can be configured for every Reporting instance inside the AP Manager if you edit the instance.
These database credentials are just used internally and not e.g. inside the PBX App Objects.

Write an own app

Currently there can be two ways for an own App:

You receive the CDRs from the CDR interfaces yourself and store them into your own database with your own structure.
You hand the Reporting database credentials to your own App and connect directly to the Reporting database within your own App. This saves you the parsing and storing of the data, but you must maintain your queries if something in the Reporting database is changed. It is important, that you should just access this data readonly and that you do not modify table data!

Example for automatic download of existing CSV files:

establish a web socket connection for reporting using PHP
request the session key with { "mt": "Start" }
receive the session key which e.g. looks like {"mt": "StartResult", "session": "189", "key": "552ca6fe05a19"}
while the web socket connection is established, a GET request using received data can be sent like this:
- http://AP-IP/DOMAIN/reporting/reports/report-20200408-1015.csv?session=189&key=552ca6fe05a19&lang=de&tz=Europe/Berlin&type=csv&sip=&from=1586304000000&to=1586333749607

Remote Interface

You can retrieve the PDF/XML/CSV report with a remote interface. After the configuration in the PBX Manager Plugin (Set user name and password under Account for external applications) make a GET HTTP request to http://AP-IP/DOMAIN/reporting/ext/ with the following parameters:

   from : the from date/time as a local timestamp
   to: the to date/time as a local timestamp
   type: the return format, allowed values:
       xml
       pdf
       csv
   [anonymous]: anonymizes names and numbers ("true" or "false"), default is true
   
   [filterNames]: use a filter with its name. You can use multiple filters inside of a filter array filterNames=[{"name":"filter1"},{"name":"filter2"},...]
   [lang]: the language, e.g. "en" or "de"
   tz: the time zone corresponding to the time stamp
   [sip]: the SIP or Cn (Long Name) of the object you want to filter for

Parameters in [] are optional.

Example:

http://ap.innovaphone.com/innovaphone.com/reporting/ext/report.xml?lang=en&tz=Europe/Berlin&type=xml&sip=&from=1692136800000&to=1698056896320&filterNames=&anonymous=true

Gets all records between 2023-08-16 and 2011-10-23 in time zone Europe/Berlin.

http://ap.innovaphone.com/innovaphone.com/reporting/ext/report.xml?lang=en&tz=Europe/Berlin&type=xml&sip=&from=1692136800000&to=1698056896320&filterNames=[{"name":"Only Incoming  Calls"}]&anonymous=true

Gets all "Incoming calls" records between 2023-08-16 and 2011-10-23 in time zone Europe/Berlin. (The filter must be defined beforehand)

CSV, XML or PDF export

There is no limit of entries anymore concerning the export as XML,CSV or PDF (Previously there was a limit of 20000 entries). But keep in mind, that the creation of large reports will take a considerable amount of time and load. Depending on your hardware the download of very large reports may take several minutes and currently no feedback is given by the service when a "Download report" request is made. (This will be added in future versions).

The field description applies to the CSV columns!

time: the node contains a formatted time string of the local time
object: the PBX object name (also called cn)
conference id: the conference id of the call
call: int (internal) or ext (external) call
Original Called,: In case of Call flow; The original called device
Diverting,: In case of Call flow; The diverting device
Transferring,: In case of Call flow; The transferring device
node: the node of the PBX object
pbx: the pbx of the PBX object
phys: physical location name of the pbx
cause: the decimal disconnect reason of the call (see Reference:ISDN_Cause_Codes)
remote:
- outgoing calls -> dialed endpoint
- incoming calls -> first ep2 above entry event or entry event if this is the first one
- incoming calls with incoming transfer after entry event -> transfer target
status:
- outgoing calls -> best found status in call flow
- 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
direction: o (outgoing) if the first event is the entry event and the caller is ep1
direction: i (incoming) if not o
alert-duration: alert time
Billing Duration: the duration, a user must be billed for
- outgoing call -> duration from the first event until the last event in the flow
- incoming call -> duration from the first transfer/forward event until the last event in the flow
call-duration/Call Duration (Total): duration of the whole call, e.g. with the duration of a subsequent transfer
conn-duration/Call Duration (User): just the time, the user was connected. Duration from the entry event until the event, where the local endpoint isn't connected any more
Further Registration: If more than one devices is registered to the same object, each device creates a CDR. This field identifies the additional CDRs for the same call
Object Info: combination of object names and number
Date: extracted date only from the time_string
Time_2: extracted time only from the time_string

Note that a CSV export doesn't contain the whole call flow!

Filters

General

Filters are set up to create reports based on certain conditions. ( Attention must be taken as the filters are AND combined to the date filters From and To and the quick filter Name or SIP). A filter can have multiple conditions (Name, Number, Group, PBX Name. Time... ) and multiple filters can be combined on report creation. Multiple filters are OR combined. So in the following picture you would see all incoming calls plus all transferred calls plus all calls made on the PBX Berlin. A filter has a unique name.

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.

(Name == Terra OR Name == 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")

Anonymous

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

Local

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

   Name or SIP: the cn/PBX object name/sip
   Number: the number
   Groups: a group of the PBX object

Remote

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

   Name: cn or sip
   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 (calls which were initiated by a transfer action)
   Call Forward: calls, which have been forwarded to the current local party

Filter Definition lower part

Call domain

   Internal calls
   External calls

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. You can optionally define the weekday(s) of calls.

Using Patterns

You can use these PostgreSQL Patterns for every condition.

Report Mails

You can send daily, weekly or monthly emails with attached reports and apply filters on them. There is no limit concerning the size of the reports but keep in mind, that your email provider might not allow attachments exceeding a certain size e.g. 20 MB. The automatic report generation can be configured here:

Report Email Settings

User reports

Creates for each matching user a separate report Users are matched by Object (Long Name). A user report only contains calls for this user. The email address of the user is built by its H323 name and the System Name of its PBX (h323@systemname) or determined by the E-Mail field of the user

Name

Quick filter for the cn/sip

Filter

Applied filter to the report

Output Format

PDF,CSV,XML (CSV and XML can be gzip compressed)

Displayed Duration

used for PDF reports (call duration total/user, billing duration)

Interval

daily: Mail will be send daily with the automatic period "last day" weekly: Mail will be send weekly with the automatic period "last week" monthly: Mail will be send daily with the automatic period "last month"

Day

the day, at which the report is sent

E-mail adresse(s)

recipients of the report (comma separated)

Compress

compress the attachment or not

Language

language used for the report

Sorting

sort entries by time, name, call or alert duration

Send time

the time of a day, when the report is sent

Time zone

the time zone corresponding to the send time

Simple Statistics

Report Email Settings

The Reports App as well as PDF Reports offer simple statistics where the number of incoming and outgoing calls are displayed as well as their respective total and average call and alert duration. Be aware that a transferred call with multiple participants may be counted multiple times as in fact CDRs are displayed.

Troubleshooting

Trace flags for App instance on App Platform:

App
App Database
Webserver traffic (shows the communication with the PBX)
App Websocket (shows the communication with the client in browser)

Trace flags for myAPPS Client:

Browser Console

Known issues

Missed calls query takes long

In some scenerios, the missed calls query takes quite long. 13r3 addresses this issue by adding a new combined index. You can add this index yourself in earlier version through SSH (as root user):

psql -d reporting_db_name
CREATE INDEX cdrs_user_id_utc_stamp_index ON cdrs (user_id,utc_stamp);

Do not change the index name, as updates to 13r3 will create a second index otherwise!

Reference14r1:Concept App Service Reports

Contents

Applies To

Technical Overview

Apps

Reports App (innovaphone-reporting)

Call List App (innovaphone-calllist)

Call List API (innovaphone-calllist-api)

PBX Manager Plugins

Reporting

Configuration

Concepts

Database Structure

cdrs

cdr_users

Database query samples

Calllist query

Reporting query

Database access

Write an own app

Remote Interface

CSV, XML or PDF export

Filters

Report Mails

Simple Statistics

Troubleshooting

Known issues

Missed calls query takes long

Navigation menu

Reference14r1:Concept App Service Reports

Applies To

Technical Overview

Apps

Reports App (innovaphone-reporting)

Call List App (innovaphone-calllist)

Call List API (innovaphone-calllist-api)

PBX Manager Plugins

Reporting

Configuration

Concepts

Database Structure

cdrs

cdr_users

Database query samples

Calllist query

Reporting query

Database access

Write an own app

Remote Interface

CSV, XML or PDF export

Filters

Report Mails

Simple Statistics

Troubleshooting

Known issues

Missed calls query takes long

Navigation menu

Search