Database Views Schema
Call Reporting Database Views
The three main call reporting views within the reporting database are:
- VW_CALLS - Records each call made into the Genesy Intelligent Automation application.
- VW_CALL_STEPS - Records each step made by the caller.
- VW_BUSINESS_TASKS - Records which business tasks started and ended, along with details of each instance.
The diagram below describes the relationship between the three views:
These views are configured so they will not lock the database when they run. Genesys recommends that you always filter based on:
- company_id.
- is_test_call.
- call_start_date.
Refer to the Useful SQL Queries section to find queries that might be useful when working with the database
VW_CALLS
This view records each call made into the Intelligent Automation application.
Calls might be physical or not. For example, if a caller started a phone call to Intelligent Automation but was transferred out of Intelligent Automation to a routing strategy and then returned. In this case, two rows are recorded into this view.
Column | DataType | Length | Description | Example |
---|---|---|---|---|
call_id | bigint | Unique identifier for each call. This is an incrementing number. | 10001 | |
company_id | int | Link to Intelligent Automation company identifier. The company ID can be found in the GUI (look in Administration > Companies). | 3 | |
voice_platform_session_id | nvarchar | 100 | Media Platform Identifier for the call within Intelligent Automation (for example, Genesys Voice Platform). | 26A92695-9557-444A-A9B2-CCB4D71C1B69-1791 |
voice_platform_full_call_id | nvarchar | 100 | Unique identifier to link multiple Intelligent Automation interactions together within a single phone call. | 10KMMSG8LL37TETGHTNQOF4KK80001NV |
start_site_id | int | The module ID of the Inbound application from which the call started. | 1 | |
start_site_name | nvarchar | 100 | The name of the Inbound application from which the call started. | SIM Activation |
call_start_time | datetime | The timestamp for when the call started. | 2014-02-13 15:35:31.737 | |
call_start_date | datetime | The date timestamp for when the call started (time is always 00.00.00.000). | 2014-02-13 00:00:00.000 | |
call_start_hour | int | The hour of the day for when the call started, in 24-hour format. | 15 | |
call_end_time | datetime | The timestamp for when the call ended. | 2014-02-13 15:36:02.640 | |
call_end_date | datetime | The date timestamp for when the call ended (time is always 00.00.00.000). | 2014-02-13 00:00:00.000 | |
call_end_hour | int | The hour of the day when the call ended, in 24-hour format. | 15 | |
call_end_site_id | int | The ID of the last module accessed on the call. | 3 | |
call_end_site_name | nvarchar | 100 | The name of the last module accessed on the call. | Transfer to RS – With Announcement |
call_end_block_type | tinyint | 1 | Type of the last block accessed on the call.
Possible values are:
|
2 |
call_end_block_name | nvarchar | 100 | The name of the last block or step accessed on the call. | End Call |
call_end_result | nvarchar | 100 | The resulting outcome description. Possible values are:
|
hangup |
has_recent_failure | bit | 1 | Internal flag to determine if an error or recognition failure occurred in the callflow step/block. This flag can be reset, as controlled by VUI preferences, if the succeeding Menu or Custom Question step/block is successful. | 0 |
is_test_call | bit | 1 | Flag to determine if call is for the test or production version of the module. Values can be 1 for Test or 0 for production. | 1 |
call_duration | int | Call duration in seconds. | 30 | |
cli | nvarchar | 45 | Calling Line Identifier (the number that the caller is calling from). | 3100 |
dnis | nvarchar | 45 | Dialed number that is associated to the Intelligent Automation application. | 1234 |
cluster_id | int | The ID of the Intelligent Automation server cluster that handled the call. Cluster information can be found in the Intelligent Automation GUI (look in Administration > Clusters). | 1 | |
cluster_name | nvarchar | 100 | The name of the Intelligent Automation server cluster that handled the call. Cluster information can be found in the Intelligent Automation GUI (look in Administration > Clusters). | Default Voice Cluster |
cti_fields | nvarchar | 1000 | Computer Telephony Integration (CTI) attached data. Fields are pipe separated, with each field represented as a key-value pair separated by colon. Literal colons, pipes, or percent symbols within keys or values are represented as %c, %p, or %%, respectively. You can define the list of allowed CTI variables that are included in this field in the Intelligent Automation GUI (look in Administration > Default Server Settings). |
Segment:Gold |
last_menu_block_type | tinyint | 1 | The type of the last Menu or Custom Question block accessed on the call.
Possible values are:
|
5 |
last_menu_block_name | nvarchar | 100 | The name of the last Menu or Custom Question block accessed on the call. | If the callflow visited the following blocks: Welcome Message > Proceed With Activation Menu >Process Request Script > Successful Message > End Call This value is Proceed With Activation Menu. |
cli_type | smallint | 1 | Representation of whether the caller is using a landline or a mobile handset. Possible values are:
The list of CLI mobile number prefixes that are defined in server settings is used to determine if the CLI is a mobile number. |
1 |
server_id | int | The ID of the Intelligent Automation server that handled the call. Server information can be found in the Intelligent Automation GUI (look in Administration > Servers). | 1 | |
start_channel | int | The channel in which the call started. Possible values are:
|
2 |
VW_CALL_STEPS
Each row in this view details a single block within the callflow that the caller progressed through.
Column | DataType | Length | Notes | Example |
---|---|---|---|---|
id | int | Unique call step identifier within call. This is an incrementing number. | 1 | |
call_id | int | See VW_CALLS.call_id. | 10001 | |
call_start_site | int | See VW_CALLS.start_site_id. | 1 | |
call_start_date | date | See VW_CALLS.call_start_date. | 2014-02-13 | |
cli_type | smallint | 1 | See VW_CALLS.cli_type. | 1 |
company_id | int | See VW_CALLS.company_id. | 3 | |
is_test_call | bit | 1 | See VW_CALLS.is_test_call. | 1 |
site_id | int | The ID of the Intelligent Automation module where this block/step (in the callflow) belongs. | 265 | |
site_name | nvarchar | 100 | The name of the Intelligent Automation module where this block/step (in the callflow) belongs. | Call Initialization |
is_submodule | bit | 1 | Flag to determine if the module is flagged as an Inbound application or just a module. | 1 |
block_type | tinyint | 1 | Block type indicator.
Possible values are:
|
1 |
block_name | nvarchar | 100 | Name of the block or step in the callflow. | Start |
block_detail | nvarchar | 500 | Internal field used to store additional information (if any) about the block. | For example, if this is a URL Transfer block, this field will show the module it will transfer to. (Link to module : 5) |
start_time | datetime | Timestamp for when the callflow step/block was first visited. | 2014-02-13 15:35:34.770 | |
start_date | datetime | Date timestamp for when the callflow step/block was first visited (time is always 00.00.00.000). | 2014-02-13 00:00:00.000 | |
start_hour | int | Hour of the day when the callflow step/block was first visited, in 24-hour format. | 15 | |
end_time | datetime | Timestamp for when the callflow step/block ended. | 2014-02-13 15:35:34.780 | |
end_date | datetime | Date timestamp for when the callflow step/block ended (time is always 00.00.00.000). | 2014-02-13 00:00:00.000 | |
end_hour | int | Hour of the day for when the callflow step/block ended, in 24-hour format | 15 | |
duration | int | Duration, in seconds, spent within the step/block. | 0 | |
result | nvarchar | 100 | Resulting outcome description. Possible values are:
|
Success |
result_detail | nvarchar | 100 | Additional information relating to the result (for example, transferred telephone number). | tel://123456789 |
error_messages | nvarchar | 500 | Detailed error messaging (if any) | |
wav_filename | nvarchar | 200 | Only applicable for a Recording block. This is the filename of the saved recording. | temprecording_123456.wav |
is_recording_saved | bit | 1 | Only applies for a Recording block. This is the flag to determine if there is a wav file recording saved. Possible values are 0 if no recording is saved or 1 if a recording exists. | 1 |
recognition_type | int | Internal recognition type.
Possible values are:
|
1 | |
is_dtmf | bit | 1 | Flag that indicates if block/step is DTMF enabled. Value will be set to 1 if its DTMF enabled; otherwise, this value is 0. | 1 |
num_retries | tinyint | Count of no-match entries by caller in this callflow step/block. This field populates only if the caller leaves this block (i.e. doesn’t hang up). | 2 | |
num_timeouts | tinyint | Count of no-input entries by caller in this callflow step/block. This field populates only if the caller leaves this block (i.e. doesn’t hang up). | 1 | |
num_helps | tinyint | Number of times the help command was used in this callflow block/step. This field populates only if the caller leaves this block (i.e. doesn’t hang up). | 1 | |
num_repeats | tinyint | Number of times the repeat command was used in this callflow block/step. This field populates only if the caller leaves this block (i.e. doesn’t hang up). | 1 | |
num_recovery_attempts | int | Number of times the callflow step/block was visited when the caller failed to be recognized and took the Intelligent Automation recovery route. | 1 | |
num_nbest | tinyint | Number of best possible matches (nbest) recognized in Automatic Speech Recognition (ASR). For an answer provided via DTMF, this will always have a value of 1. | 10 | |
nbest_meaning_1 | nvarchar | 45 | First highest match from the ASR against the SRGS grammar in context. | 07712344401 |
nbest_rawanswer_1 | nvarchar | 100 | First highest synonym match from the ASR against the SRGS grammar in context. | Oh seven seven one two three four four four oh one |
nbest_confidence_1 | int | Confidence scoring out of 1000 of first highest match against the SRGS grammar in context. For DTMF, this value is always 1000. | 700 | |
nbest_slots_1 | nvarchar | 100 | First highest slot content from the ASR against the SRGS grammar in context. | Type:Mobile|Number:12344401 |
nbest_recognition_type_1 | int | Internal recognition type reference for the first highest match.
Possible values are:
|
2 | |
nbest_meaning_2 | nvarchar | 45 | Second highest match from the ASR against the SRGS grammar in context. | 07712344501 |
nbest_rawanswer_2 | nvarchar | 100 | Second highest synonym match from the ASR against the SRGS grammar in context. | Zero seven seven one two three four four five oh one |
nbest_confidence_2 | int | Confidence scoring out of 1000 of second highest match against the SRGS grammar in context. For DTMF, this value is always 1000. | 10 | |
nbest_slots_2 | nvarchar | 100 | Second highest slot content from the ASR against the SRGS grammar in context. | Type:Mobile|Number:12344501 |
nbest_recognition_type_2 | int | Internal recognition type reference for the second highest match.
Possible values are:
|
2 | |
nbest_meaning_3 | nvarchar | 45 | Third highest match from the ASR against the SRGS grammar in context. | 07712345401 |
nbest_rawanswer_3 | nvarchar | 100 | Third highest synonym match from the ASR against the SRGS grammar in context. | Zero seven seven one two three four five four zero one |
nbest_confidence_3 | int | Confidence scoring out of 1000 of third highest match against the SRGS grammar in context. For DTMF, this value is always 1000. | 10 | |
nbest_slots_3 | nvarchar | 100 | Third highest slot content from the ASR against the SRGS grammar in context. | Type:Mobile|Number:12345401 |
nbest_recognition_type_3 | int | Internal recognition type reference for the third highest match.
Possible values are:
|
2 | |
output_node_name | nvarchar | 100 | Name of the path that leads to this callflow step/block. | success |
sequence_in_call | int | Given the list of callflow steps/blocks that were visited within the call, this is the position this step/block was visited within the sequence. | 3 | |
sequence_in_site | int | Given the list of callflow steps/blocks that were visited within the module, this is the position this step/block was visited within the sequence. | 1 | |
persona_name | nvarchar | 100 | The name of the persona active during the current call step. An empty string is saved if using the default persona. | French |
channel | int | The channel that is being used by the caller during this call step. Possible values are:
|
2 |
VW_BUSINESS_TASKS
Each row in this view details the business task that was processed (started, ended, and so on) within the callflow that the caller progressed through.
Column | DataType | Length | Notes | Example |
---|---|---|---|---|
id | bigint | Unique business task identifier within the call. This is an incrementing number. | 10008 | |
call_id | int | See VW_CALLS.call_id. | 10001 | |
voice_platform_session_id | nvarchar | 100 | See VW_CALLS.voice_platform_session_id. | 26A92695-9557-444A-A9B2-CCB4D71C1B69-1791 |
voice_platform_full_call_id | nvarchar | 100 | See VW_CALLS.voice_platform_full_call_id. | 10KMMSG8LL37TETGHTNQOF4KK80001NV |
company_id | int | See VW_CALLS.company_id. | 3 | |
is_test_call | bit | 1 | See VW_CALLS.is_test_call. | 1 |
start_site_id | int | The ID of the module where this business task belongs. | 10 | |
start_site_name | nvarchar | 100 | The name of the module where this business task belongs. | Payment By Full Balance |
start_time | datetime | Timestamp for when this business task started. | 2014-02-13 15:36:31.367 | |
start_date | datetime | Date timestamp for when this business task started (time is always 00.00.00.000). | 2014-02-13 00:00:00.000 | |
start_hour | int | Hour of the day for when this business task started, in 24-hour format. | 15 | |
end_time | datetime | Timestamp for when this business task ended. | 2014-02-13 15:36:50.367 | |
end_date | datetime | Date timestamp for when this business task ended (time is always 00.00.00.000). | 2014-02-13 00:00:00.000 | |
end_hour | int | Hour of the day for when this business task ended, in 24-hour format | 15 | |
duration | int | Duration, in seconds, between when the time business task started and ended. | 19 | |
name | nvarchar | 100 | Name of the business task (for example, postal address lookup). | Payment |
outcome_category | tinyint | 1 | Outcome category identifier.
Possible values are:
|
3 |
outcome_description | nvarchar | 100 | Outcome category description. | Invalid balance |
details | nvarchar | 100 | Additional information regarding the business task outcome. | Balance = null |
call_start_site | int | See VW_CALLS.start_site_id. | 1 | |
call_start_date | date | See VW_CALLS.call_start_date. | 2014-02-13 |
Useful SQL Queries
This page describes SQL queries that might be useful when working with the database.
Get complete call details for a given call ID
SELECT
calls.*, call_steps.*
FROM VW_CALLS calls
INNER JOIN VW_CALL_STEPS call_steps
ON calls.call_id = call_steps.call_id
AND calls.call_id = xxx
Get complete business task details for a given call ID
SELECT
calls.*, business_tasks.*
FROM VW_CALLS calls
INNER JOIN VW_BUSINESS_TASKS business_tasks
ON calls.call_id = business_tasks.call_id
AND calls.call_id = xxx
Get complete call details for a company, filtered by call date and module version
SELECT
calls.*, call_steps.*
FROM
VW_CALLS calls,
VW_CALL_STEPS call_steps
WHERE
calls.company_id = call_steps.company_id
AND calls.company_id = 1
AND calls.is_test_call = call_steps.is_test_call
AND calls.is_test_call = 0
AND calls.call_start_date = '2017-01-01'