> For the complete documentation index, see [llms.txt](https://docs.pascom.net/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.pascom.net/en/operations/analytics/statistics-custom.md). # Customised PASCOM Analytics After you have familiarized yourself with the basics of PASCOM Analytics you can use this chapter to learn here how you to make your own adjustments. ## Overview {% hint style="info" %} You can only customize database queries. The data sources for **Live Dashboards** are **statically** generated by PASCOM. {% endhint %} With PASCOM Analytics you can create not only your own dashboard with existing graphs but also customize the underlying data sources. The **requirement** for this is that you are familiar with the **basics of PASCOM Analytics** and **SQL**. ## Getting Started The easiest way of understanding the SQL queries used is simply to edit existing graphs. First copy an existing dashboard (see [Create Edit Custom Dashboards](/en/operations/analytics/statistics.md#create-edit-custom-dashboards)). Then you can edit graphs and tables and adjust the SQl. Click on any graph or tablet on **Edit**: ![PASCOM Analytics Edit graph](https://2713225-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FVIw0BpSv358R2Pr2HflD%2Fuploads%2Fgit-blob-e48c140c536778e52d58d1b9faaf4291814fc2a0%2Fgraph_edit.png?alt=media) Now you can adapt and test the SQL: ![PASCOM Analytics Graph Details](https://2713225-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FVIw0BpSv358R2Pr2HflD%2Fuploads%2Fgit-blob-11a5a976613da7940489f7cabfac571589434373%2Fgraph_details.png?alt=media) Further information, in particular on variables, can be found in the editor under **Show Help**. The database used is PostgreSQL. Information about PostgreSQL in connection with Grafana (Analytics Tool used by PASCOM) can be found here [Using PostgreSQL in Grafana](https://grafana.com/docs/grafana/latest/datasources/postgres/). ## SQL functions PASCOM provides SQL functions for querying data. Similar to tables, these can be used in SQL queries. ### filter\_calls () This function returns **all completed** calls. Active calls i.e. live calls that are ongoing at the time the query is made are not included in the filter. #### Parameters ```sql filter_calls (     from_timestamp,     to_timestamp,     call_type,     filter_user,     filter_label,     filter_from_name,     filter_from_number,     filter_to_name,     filter_to_number ) ``` | Parameters | Description | | -------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | | from\_timestamp | Calls from this specific time. Usually the dasboard variable ***$\_\_timeFrom()*** for the selected period. | | to\_timestamp | Calls up to this point. Usually the dashboard variable ***$\_\_timeTo()*** for the selected period. | | call\_type | Possible values: ***all*** = all calls, ***inbound*** = only incoming calls, ***outbound*** = only outgoing calls, ***internal*** = only internal calls | | filter\_user | Filter by username (not display name). ***\**** = All users. Multiple users per comma-separated list possible e.g. 'user1, user2'. | | filter\_label | Filter by label name. ***\**** = All labels. Multiple labels per comma separated list possible e.g. 'label1, label2'. | | filter\_from\_name | Filter by caller name. Display name of the caller, e.g. from the phone book if available. Leave the parameter ***empty*** in order not to apply a filter. | | filter\_from\_number | Filter by caller number. Leave the parameter ***empty*** in order not to apply a filter. | | filter\_to\_name | Filter by called name. Display name of the called party, e.g. from the phone book if available. Leave the parameter ***empty*** in order not to apply a filter. | | filter\_to\_number | Filter by called number. Leave the parameter ***empty*** in order not to apply a filter. | #### Return Values | Return Value | Description | | ----------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | record\_timestamp | Time at which this call was started | | record\_id | ID of this record | | from\_internal | Internal caller?: ***t*** = yes, ***f*** = no | | from\_number | Caller number | | from\_name | Caller display name, e.g. from phone book if available | | to\_internal | Internal call?: ***t*** = yes, ***f*** = no | | to\_number | Called number | | to\_name | Called display name, e.g. from phone book if available | | status | Possible values: ***hangup*** = call was hung up normally, ***transfer*** = call was transferred, ***noanswer*** = call was not answered | | direction | Possible values: ***inbound*** = incoming call, ***outbound*** = outgoing call, ***internal*** = internal call | | total\_duration | Total duration of the call. The call starts as soon as the user or team is called. IVR, call router, etc. are not included in the call duration | | ringing\_duration | Time taken for the call to be answered by a user | | talking\_duration | Duration of the conversation including \*\*\* hold\_duration \*\*\* | | hold\_duration | Duration of the call on hold | | from\_trunk\_id | ID of the trunk used for the inbound call | | from\_trunk\_name | Name of the trunk used for the inbound call | | from\_rule\_id | ID of the inbound rule used | | from\_rule\_name | Name of the inbound rule used | | to\_trunk\_id | ID of the trunk used for the outbound call | | to\_trunk\_name | Name of the trunk used for the outbound call | | to\_rule\_id | ID of the outbound rule used | | to\_rule\_name | Name of the outbound rule used | | data | | | record\_chain | Each call is linked to several individual actions via the ***record\_chain***. The individual actions are in the table ***mdphonecallrecord***, the chain in the column ***phonecallrecord\_chain*** | ### filter\_queue\_calls () This function returns all **completed** **incoming** calls made to a **team**. Active calls that are still ongoing at the moment of the query (i.e.live) are not included. #### Parameters ```sql filter_queue_calls (     from_timestamp,     to_timestamp,     filter_team,     filter_user,     filter_from_name,     filter_from_number,     filter_label ) ``` | Parameters | Description | | -------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------- | | from\_timestamp | Calls since a specific time (timestamp without time zone). Usually the dasboard variable ***$\_\_timeFrom()*** for the selected period. | | to\_timestamp | Calls up to this point in time (timestamp without time zone). Usually the dashboard variable ***$\_\_timeTo()*** for the selected period. | | filter\_team | Filter by team name. ***\**** = All teams. Multiple teams per comma separated list possible e.g. 'team1, team2'. | | filter\_user | Filter by username (not display name). ***\**** = All users. Multiple users per comma-separated list possible e.g. 'user1, user2'. | | filter\_from\_name | Filter by caller name. Display name of the caller, e.g. from the phone book if available. Leave the parameter ***empty*** in order not to apply a filter. | | filter\_from\_number | Filter by caller number. Leave the parameter ***empty*** in order not to apply a filter. | | filter\_label | Filter by label name. ***\**** = All labels. Multiple labels per comma separated list possible e.g. 'labe1, label2'. | #### Return values | Return value | Description | | ----------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | id | ID of this record | | chain | Each call is linked to several individual actions via the ***record\_chain***. The individual actions are described in the table ***mdphonecallrecord***, the chain in the column ***phonecallrecord\_chain*** | | from\_internal | Internal caller?: ***t*** = yes, ***f*** = no | | from\_number | Caller number | | from\_name | Caller display name, e.g. from phone book if available | | direction | Call direction (e.g., ***inbound***, ***outbound***) | | total\_duration | Total duration of the call. The call starts as soon as the team is called. IVR, call router, etc. are not counted. | | talking\_duration | Duration of the conversation including ***hold\_duration*** | | moh\_duration | Duration of music played on hold. | | hold\_duration | Duration the call was held exclusive ***moh\_duration*** | | status | Possible values: ***hangup*** = call was hung up normally, ***transfer*** = call was transferred, ***noanswer*** = call was not answered | | queue\_timestamp | Time at which this call was started | | queue\_name | Team Name | | from\_trunk\_id | ID of the trunk used for the inbound call | | from\_trunk\_name | Name of the trunk used for the inbound call | | from\_rule\_id | ID of the inbound rule used | | from\_rule\_name | Name of the inbound rule used | | to\_trunk\_id | ID of the trunk used for the outbound call | | to\_trunk\_name | Name of the trunk used for the outbound call | | to\_rule\_id | ID of the outbound rule used | | to\_rule\_name | Name of the outbound rule used | | data | | | agent\_id | ID of the team member who answered or transferred the call | | agent\_record\_id | ID of the team member record. All ***agent\_\**** fields are only filled if the call has been answered or transferred. | | agent\_chain | Same value as \*\*\* chain \*\*\*. | | agent\_timestamp | Time when the team member is called | | agent\_name | Username (not display name) of the team member | | agent\_number | Internal extension of the team member | | agent\_ringing | Ringing duration of the team member | ## SQL tables ### mdphonecallrecord In **mdphonecallrecord** every single step that a call takes through the PASCOM cloud phone system is saved. A call therefore creates many entries in the mdphonecallrecord. The individual entries are linked to a call via the **phonecallrecord\_chain** field. In the main, please only use the SQL functions offered by PASCOM and only access this table if needed for any details. #### Example attended transfer ![Attended Transfer](https://2713225-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FVIw0BpSv358R2Pr2HflD%2Fuploads%2Fgit-blob-89ca203f77ac2586607278ad7c56e216ea89071a%2Fattended_transfer.en.png?alt=media) In the **mdphonecallrecord** an entry is created for each step. As a step, we mean every object in PASCOM that can be reached via an extension. In our example, the call flow is as follows: * Caller calls user A directly (101) * User A puts caller on hold and calls user B (102) * User A connects caller to user B (102) The sample call flow therefore creates three entries in the database (only excerpts): | Field name (phonecallrecord\_) | Step 1 | Step 2 | Step 3 | | ------------------------------ | ------------------- | ------------------- | ------------------- | | id | 1 | 2 | 3 | | timestamp | 2020-04-03 12:47:32 | 2020-04-03 12:47:56 | 2020-04-03 12:48:13 | | srcnumber | 004989123123 | 101 | 004989123123 | | dstname | User A | User B | User B | | dstnumber | 101 | 102 | 102 | | parentid | | 1 | 2 | | chain | 158589 9721877\_10 | 158589 9721877\_10 | 158589 9721877\_10 | | result | transfer | transfer | hangup | | result details | dst | src | | | via | | | transfer | | viadetails | | | | | duration | 41 | 16 | 18 | | connected | 29 | 12 | 18 | | holdduration | 16 | 0 | 0 | The following can be seen from the data: * The caller spoke to user A for 13 seconds (connected - holdduration) * User A spoke to user B for 12 seconds and then transferred the caller to user B. * User A called user B for 4 seconds (duration - connected) before he answered #### Example Call via a IVR / Team ![Call flow example](https://2713225-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FVIw0BpSv358R2Pr2HflD%2Fuploads%2Fgit-blob-6ef9f2bd36699f74139e826d805a4871ef960bf8%2Fpascom_cdr.en.png?alt=media) In the **mdphonecallrecord** an entry is created for each step. As a step, we mean every object in PASCOM that can be reached via an extension. In our example, the call flow is as follows: * Caller is prompted to select a team in IVR menu and chooses 1 (501) * The team greets the caller with an announcement and calls the members in parallel (201) * User A is called and does not answer the call (101) * User B is called and answers the call (102) The example call flow therefore creates four entries in the database (only excerpts): | Field name (phonecallrecord\_) | Step 1 | Step 2 | Step 3 | Step 4 | | ------------------------------ | ------------------- | ------------------- | ------------------- | ------------------- | | id | 1 | 2 | 3 | 4 | | timestamp | 2020-04-03 09:42:13 | 2020-04-03 09:42:17 | 2020-04-03 09:42:19 | 2020-04-03 09:42:19 | | srcnumber | 004989123123 | 004989123123 | 004989123123 | 004989123123 | | dstname | IVR | Team | User A | User B | | dstnumber | 501 | 201 | 101 | 102 | | parentid | | 1 | 1 | 1 | | chain | 158589 9721877\_10 | 158589 9721877\_10 | 158589 9721877\_10 | 158589 9721877\_10 | | result | transfer | hangup | noanswer | hangup | | result details | dst | caller | elsewhere | caller | | via | | queue | queue | queue | | viadetails | | caller | agent | agent | | duration | 4 | 33 | 10 | 30 | | connected | 0 | 20 | 0 | 20 | | holdduration | 0 | 6 | 0 | 6 | The following can be seen from the data: * Steps 2 to 4 run in parallel. This is recognizable by the same **parentid**. * The team (step 2) runs until the call has been processed by a user. * The total duration of the call is therefore the duration of step 1 and step 2, i.e. 37 seconds. * User A and user B were called in parallel (see **timestamp**). * For user A, the call rang for 10 seconds but was not answered (duration - connected = 10). * User B answered the call after 10 seconds (duration - connected = 10) and spoke for 20 seconds. * User B put the caller on hold for 6 seconds. #### Field Description mdphonecallrecord | Field name | Description | | ------------------------------------ | ---------------------------------------------------------------------------------------------------------- | | phonecallrecord\_id | Id of this step | | phonecallrecord\_timestamp | When this step was started | | phonecallrecord\_parentid | ID of the previous step | | phonecallrecord\_chain | The steps in a call are linked using this ID | | phonecallrecord\_result | Result of this step. For possible return values, see table ***phonecallrecord\_result***. | | phonecallrecord\_resultdetails | Result details of this step. Possible return values ​​see table ***phonecallrecord\_resultdetails***. | | phonecallrecord\_via | Which previous step triggered this step? For possible return values, see table ***phonecallrecord\_via***. | | phonecallrecord\_viadetails | Details on the previous step. Possible return values ​​see table ***phonecallrecord\_viadetails***. | | phonecallrecord\_recordid | ID of the recording, if available | | phonecallrecord\_duration | Total duration of this step | | phonecallrecord\_connected | Duration of the conversation in this step. (Announcements and music on hold do not count as conversations) | | phonecallrecord\_srcinternal | Internal caller?: ***t*** = yes, ***f*** = no | | phonecallrecord\_srcuserid | Caller user ID. For internal participants only | | phonecallrecord\_srcusername | Caller username. For internal participants only | | phonecallrecord\_srcname | Caller display name, e.g. from phone book if available | | phonecallrecord\_srcdeviceid | Caller device ID. For internal participants only | | phonecallrecord\_srcdevicename | Caller device name. For internal participants only | | phonecallrecord\_srclocationid | Caller location ID. For internal participants only | | phonecallrecord\_srclocationname | Caller location name. For internal participants only | | phonecallrecord\_srcprefix | Caller prefix. Only for incoming callers. Prefix of the trunk through which the call came | | phonecallrecord\_srcnumber | Caller phone number | | phonecallrecord\_srcextension | Caller extension | | phonecallrecord\_dstinternal | Internal call?: ***t*** = yes, ***f*** = no | | phonecallrecord\_dstuserid | Called user ID. For internal participants only | | phonecallrecord\_dstusername | Called username. For internal participants only | | phonecallrecord\_dstname | Called display name, e.g. from phone book if available | | phonecallrecord\_dstdeviceid | Called device ID. For internal participants only | | phonecallrecord\_dstdevicename | Called device name. For internal participants only | | phonecallrecord\_dstlocationid | Called location ID. For internal participants only | | phonecallrecord\_dstlocationname | Called location name. For internal participants only | | phonecallrecord\_dstprefix | Called prefix. Only for outgoing calls. Prefix of the trunk through which the call was made | | phonecallrecord\_dstnumber | Called phone number | | phonecallrecord\_dstextension | Called extension. For internal participants only | | phonecallrecord\_srctrunkid | ID of the inbound trunk | | phonecallrecord\_srctrunkname | Name of the inbound trunk | | phonecallrecord\_srcruleid | ID of the inbound rule | | phonecallrecord\_srcrulename | Name of the inbound rule | | phonecallrecord\_dsttrunkid | ID of the outbound trunk | | phonecallrecord\_dsttrunkname | Name of the outbound trunk | | phonecallrecord\_dstruleid | ID of the outbound rule | | phonecallrecord\_dstrulename | Name of the outbound rule | | phonecallrecord\_srcphonebookentryid | Caller ID of the phone book entry, if available | | phonecallrecord\_dstphonebookentryid | Called ID of the phone book entry, if available | | phonecallrecord\_data | Labels, voicemail, queue details | | phonecallrecord\_holdcount | How many times the call was on hold during this step | | phonecallrecord\_holdduration | How long was the call held during this step? | | phonecallrecord\_phonecallid | ID for internal use | **phonecallrecord\_result** | Return Value | Description | | ------------ | ------------------------------------ | | noanswer | Not answered in this step | | hangup | Handled in this step | | transfer | Connected from this step to the next | **phonecallrecord\_resultdetails** | Return Value | Description | | ------------ | --------------------------------------------------------- | | voicemail | Step ended in voicemailbox | | src | Caller performed the transfer | | dst | Called party performed the transfer | | abandon | Caller hung up before the called party answered | | elsewhere | Another party answered the call (not in this step) | | timeout | Timeout reached in this step | | picked | Pickup has been made | | caller | Caller took the last action | | agent | The called party performed the last action | | disabled | All devices assigned to callee are disabled via follow-me | **phonecallrecord\_via** | Return Value | Description | | ------------ | ------------------------------------------ | | noanswer | Call was not answered in the previous step | | transfer | Call was transferred in the previous step | | queue | The call is a team call | | fax | The call is a fax | **phonecallrecord\_viadetails** | Return Value | Description | | --------------- | ----------------------------------------------------------------- | | action | Triggered via action from the previous step | | src | Triggered via transfer of the caller from the previous step | | dst | Triggered via transfer of the called party from the previous step | | caller | Triggered by a Team call from the caller's point of view | | agent | Triggered by a Team call from the team member's point of view | | picked | Triggered by a pick up from the previous step | | agent\_outbound | Outbound call via queue | --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.pascom.net/en/operations/analytics/statistics-custom.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `build a script that syncs our docs to a CMS` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.