Reference13r3:Concept App Service Reports
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 or myPBX 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:
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
mypbx
This is the interface for the support of the old myPBX client.
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
- compatibility to use reporting for for myPBX call lists. Configured account data also have to be used in the MyPBX configuration.
Concepts
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.
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
- 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-access
- 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
CSV export
The export as CSV or PDF has a limit of 20000 entries, unless it is an export of only one day.
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
- co (connected)
- al (alert)
- er (error)
- bu (busy)
- 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!
Appendix
Missed calls
Missed calls count queries are just done for the last 30 days to increase the performance of these queries.
Currently this just affects myPBX, as myApps doesn't trigger this query.
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!