You are here:

Softdial Publisher™

SQL Table Definitions

This topic shows details of each of the reporting tables populated by Softdial Publisher™. For setup details, see Softdial Publisher™.

Agent Tables

agents

A list of all agents who have logged in to Softdial CallGem™, by tenant

Show details

Derived from	Agent Data [AZ]
Updated	When an agent logs in for the first time, i.e: Detail State (DS) = 2 (Logged in and Unavailable) AND Old State (OS) = 0 OR 1 (Not Logged In)
Populated by	history az.xslt

Table Fields

Field	Parameter	Description
agent	Agent Name (AN)	The agent Identifier of an agent that has logged in to Softdial CallGem™
description	Agent Description (AD)	The agent description

agentlogins

A list of all agent login and logout events, by tenant, campaign and agent

Show details

Derived from	Agent Data [AZ]
Updated	Every time an agent logs out, i.e: Old State (OS) = 2 (Logged In and Unavailable) AND Detail State (DS) = 0 (Not Logged In)
Populated by	history az.xslt

Table Fields

Field	Parameter	Description
campaign_name	Campaign Name (CN)	The campaign that the agent was logged in to for this login session
agent_name	Agent Name (AN)	The agent identifier
avail_timestamp	Z0	The time the agent logged in (OADateTime)
unavail_timestamp	Z1	The time the agent logged out (OADateTime)
time_available	Z2	The duration of this login session (secs)

agentavailability

Contains 1 row for each period of an agent being ACD available. In the course of an agent login session, the agent may go available and unavailable several times. This will result in multiple rows being output to the agentavailability table.

Agents may also be made unavailable (and available again) under program control for automated call blending. When this occurs, it results in completion of an agent's availability on one campaign and the start of availability on another. This will result in a row being output to the agentavailability table.

Show details

Derived from

Agent Data [AZ]

Updated

If Not a Follow Up Call, i.e:

Old State (OS) != 8
AND
Detail State (DS) = 2

Populated by

history az.xslt

Table Fields

Field	Parameter	Description
campaign_name	Campaign Name (CN)	The campaign that the agent was logged in to for this login session
agent_name	Agent Name (AN)	The Agent Identifier
avail_timestamp	Z0	The time the agent went available (OADateTime)
unavail_timestamp	Z1	The time the agent went unavailable (OADateTime)
time_available	Z2	The duration of this available session
agent_extension	Agent extension (AE)	The agent extension
tenant	Tenant ID (TD)	The tenant to which the agent is logged in.
free_reason	Free Reason (FR)	A user defined coded representing the reason why an agent went unavailable
free_description	Free Reason Description (FD)	A short text explanation for the accompanying Free Reason

agent_states

Timestamp and duration details for the various agent states (e.g. waiting, talking, wrapping, previewing etc) by tenant, campaign and agent.

Show details

Derived from	Agent Data [AZ]
Populated by	history az.xslt

Table Fields

Field	Parameter	Description
tenant	Tenant ID (TD)	The tenant to which the agent is logged in.
campaign_name	Campaign Name (CN)	The campaign that the agent was logged in to for this login session
agent_name	Agent Name (AN)	The agent identifier
start_global	Z0	If the agent is logged in and unavailable, Z0 contains the timestamp for the agent login; otherwise it contains the timestamp for the agent going available. (OADateTime)
stop_global	Z1	If the agent is logged in and unavailable, Z1 contains the current timestamp. (OADateTime)
duration_global	Z2	Contains the time difference between Z0 and Z1. (secs)
state_global	Agent State (ZA)	The Agent State - 0 = Not logged in 1 = Logged In and Available or Working 2 = Logged in and Unavailable
start	Z3	Contains the timestamp of the last agent state change. (OADateTime)
detail_state	Detail State (DS)	The detailed agent state. See the Agent Data [AZ] message for options.
old_state	Old State (DS)	The previous or old agent state - See the Agent Data [AZ] message for options.
duration	Z4	Contains the time difference between Z3 and the timestamp of the last event Timestamp (TS) parameter. (in secs)
agent_description	Agent Description (AD)	The agent description
extension	Agent Extension (AE)	The agent extension
reason	Free Reason (FR)	A user defined coded representing the reason why an agent went Unavailable
description	Free Reason Description (FD)	A short text explanation for the accompanying Free Reason
data	Data (DT)	Data associated with this session
campaign_session	Campaign Unique ID (YC)	An internally generated unique session id for the campaign identified in the Campaign Name (CN) parameter
agent_session	Agent Unique ID (YA)	An internally generated unique session id for the agent identified in the Agent Name (AN) parameter
login_time	Agent Login Time (W0)	The timestamp of the last Agent Login [AL] message (OADateTime)
avail_time	Agent Extension (AE)	The timestamp of last Agent Available [AA] message (OADateTime)

transfers

Stores event data relating to a single call transfer. A call session may be transferred to several different parties through its lifetime. The transfers table stores a row for each time a call is transferred from an agent to another party.

For each transfer:

all event timestamps are recorded
ring and conference times are permuted

Show details

Derived from	TransferData [XZ]
Populated by	sql xz.xslt

Table Fields

Field	Parameter	Description
session_guid	Campaign Name (CN)	Concatenation of tenant description, session identifier, and timestamp parameters in the form TD_SI_TS.
tx_timestamp	Timestamp (TS)	The timestamp of the current event (OADateTime)
sessionid	Session ID (SI)	The ID of the current call session.
phonenum	Telephone Number (TN)	The telephone number of the source call. In inbound terms, this is the CLI/ ANI.
campaign_name	Campaign Name (CN)	The campaign that the agent was logged in to for this login session
src_agent_name	Agent Name (AN)	The agent initiating the transfer.
dest_agent_name	Second Agent (A2)	The agent that the call session is being transferred to.
transfer_address	-	Reserved for future use.
dest_type	-	Reserved for future use.
dest_address	Address (EX)	The transferee address
offer_timestamp	Timestamp1 (Z1)	The timestamp of the transfer offer.
accept_timestamp	Timestamp2 (Z2)	The timestamp of the transfer accept.
complete_timestamp	Timestamp3 (Z3)	The timestamp of the completion of the transfer.
drop_timestamp	Timestamp3 (Z3)	The timestamp if the transfer was dropped.
outcome	Resource Status (RS)	Indicates the outcome of the transfer attempt. 0 = Success 1 = Rejected 2 = Failed
ring_time	-	The ring time for the transfer (Z2 - Z1)
conference_time	-	The conference time (Z3 - Z2)
talk_time	Timestamp3 (Z4)	The time at which the agent who received the call finished with it (due to it ending or a subsequent transfer).

Campaign/ Queue Tables

queuestatistics

Contains a snapshot of statistics (e.g. calls queued, dropped, overflowed, within SLA etc.) for each inbound queue. 1 row is output for each queue at a predetermined time frequency (which can be varied by editing the Publisher configuration).

The statistics recorded for the queue are statistics from the point that the queue was created.

Queues may come into and go out of service several times during their lifetime. A queue is normally created against an inbound campaign when that campaign starts, and is destroyed when the campaign ends. Queues can, however, be created and destroyed in real-time.

Show details

Derived from	Queue Metrics [MZ]
Populated by	sql mz.xslt

Table Fields

Field	Parameter	Description
tx_timestamp	Timestamp (TS)	The timestamp of the current event (OADateTime)
tenant_id	Tenant Descriptor (TD)	The tenant ID
campaign_name	Session ID (SI)	The ID of the current call session.
queue_address	Group Address (GA)	The queue for which metrics are being reported.
queue_desc	-	Reserved for future use.
queue_length	-	The number of calls currently waiting in the queue.
active_agents	Active Agents (Z1)	The number of agents actively able to handle calls from the queue.
total_agents	Total Agents (Z2)	The number of agents who are members of the queue, including those who cannot currently handle calls.
queued_count	Queued Count (Z3)	The number calls that have enetered the queue.
dequeued_count	Dequeued Count (Z4)	The number calls that have left the queue and been transferred to an agent.
abandon_count	Abandon Count (Z5)	The number of calls that were abandoned by the caller
oflow_count	Overflow Count (Z6)	The number calls that overflowed to another queue
drop_count	Drop Count (Z7)	Number calls that were dropped because the timeout was reached.
mean_answer_time	Average Answer time (Z8)	Average time in seconds that calls took to be answered by an agent (of the calls that were successfully answered).
pct_calls_within_sla	Percentage Calls Within SLA (Z9)	The percentage of calls that were answered within the SLA specified time period (of the calls that were successfully answered)
mean_answer_time_window	WindowAverageAnswerTime (Y0)	Average time in seconds that calls took to be answered by an agent (of the calls that were successfully answered), within the recent period as configured with the Time Window (TW) parameter.
pct_calls_within_sla_window	WindowPercentageCallsWithinSLA (Y1)	Percentage of calls that were answered within the SLA specified time period (of the calls that were successfully answered), within the recent period as configured with the Time Window (TW) parameter.
id
y2	QueuedCountRecent (Y2)	The number of calls that have entered the queue since the last update.
y3	DequeuedCountRecent (Y3)	The number of calls that have left the queue and been transferred to an agent since the last update.
y4	AbandonCountRecent (Y4)	The number of calls that were abandoned by the caller since the last update.
y5	OverflowCountRecent (Y5)	The number of calls that overflowed to another queue since the last update.
y6	DropCountRecent (Y6)	The number of calls that were dropped because the timeout was reached since the last update.

queuetransactions

Records each call session (or media session) that passes through a queue.

For each call session:

the enqueue time, dequeue attempts and final dequeue/ expiry time are recorded
queued_time and agent_time are permuted

Show details

Derived from	Queue Data [QZ]
Populated by	sql qz.xslt

Table Fields

Field	Parameter	Description
session_guid	-	Concatenation of Tenant Descriptor (TD), Session ID (SI), and Timestamp (TS) parameters in the form TD_SI_TS.
tx_timestamp	Timestamp (TS)	The timestamp of the current event. (OADateTime)
sessionid	Session ID (SI)	The ID of the current call session.
cli	Telephone Number (TN)	The telephone number of the source call.In inbound terms this is the CLI/ ANI.
dnis	Dialed Number (DN)	The number that the caller dialed.
tenant_id	Tenant Descriptor (TD)	Tenant ID.
campaign_name	Campaign Name (CN)	The campaign that the agent was logged in to for this login session.
queue_address	Group Address (GA)	If specified, the queue (on the system campaign) to transfer the call to.
dequeue_agent	Agent Name (AN)	The agent to whom the call is dequeued.
enqueue_timestamp	Timestamp0 (Z0)	The time the call entered the queue.
first_offer_timestamp	Timestamp1 (Z1)	The timestamp of the transfer offer.
final_offer_timestamp	Timestamp2 (Z2)	The timestamp of the transfer accept.
accept_timestamp	Timestamp3 (Z3)	The timestamp of the completion of the transfer.
expiry_timestamp	Timestamp4 (Z4)	The timestamp if the transfer was dropped.
expiry_reason	Resource Status (RS)	Indicates the outcome of the transfer attempt 0 = Success 1 = Rejected 2 = Failed
queued_time	-	If RS = 0 (Z3 - Z0) If RS = 1 (Z4 - Z0) If RS = 2 (TS - Z0)
agent_time	-	The agent time (Z4 - Z3)

Call Cycles Tables

callcycles

Contains normalised data on a call session. Each call session processed through Softdial CallGem™ will result in one row being written to a callcycles table.

Show details

Derived from	Session Data [IZ]
Populated by	history iz.xslt

Table Fields

Field	Parameter	Description
ci_timestamp	Timestamp1 (Z1)	The timestamp of the Call Initiate [CI] event. (OADateTime)
sessionid	Session ID (SI)	The ID of the current call session.
phonenum	Telephone Number (TN)	The telephone number of the source call.In inbound terms this is the CLI/ANI.
campaign	Campaign Name (CN)	The campaign that the agent was logged in to for this login session.
agent	Agent Name (AN)	The Agent Identifier.
team	-	Reserved for future use.
turret	-	The turret to which the agent was logged in for this session
media_type	-	The media type of this session
callyear	-	The 'year' component derived from the Z1 timestamp.
callmonth	-	The 'month' component derived from the Z1 timestamp.
callday	-	The 'day' component derived from the Z1 timestamp.
callhour	-	The 'hour' component derived from the Z1 timestamp.
callminute	-	The 'minute' component derived from the Z1 timestamp.
callsecond	-	The 'second' component derived from the Z1 timestamp.
proceeding_timestamp	Timestamp2 (Z2)	The Setup Complete time derived from the Delay Notification [DN] message. (OADateTime)
physicalconn_timestamp	Timestamp7 (Z7)	The timestamp of the actual connect (before AMD). If the call fails, set to Disconnect time derived from the Now Disconnected [ND] message. (OADateTime)
outcome_timestamp	Timestamp3 (Z3)	The Call Outcome timestamp derived from either the Line Connected [LC] message or the Call Failed [CF] message. (OADateTime)
ac_timestamp	Timestamp4 (Z4)	The time that the call was connected to an agent. Derived from the Agent Connect [AC] message. (OADateTime)
z8_timestamp	Timestamp8 (Z8)	Timestamp of a successful or failed transfer. If successful, derived from the Transfer Acknowledge [TA] message If the transfer fails, derived from the Now Disconnected [ND] message. (OADateTime)
sw_timestamp	Timestamp5 (Z5)	The timestamp of the agent going into wrap. Derived from the Now Disconnected [ND] message. (OADateTime)
tc_timestamp	Timestamp6 (Z6)	The timestamp of the completed call session. Derived from the Transaction Complete [TC] message. (OADateTime)
cd_timestamp	Timestamp5 (Z5)	The timestamp of the call being disconnected. Derived from the Now Disconnected [ND] message. (OADateTime)
ao_parameter	Agent Outcome (AO)	The agent disposition associated with an agent-classified call.
ca_parameter	Reason (RE)	This code indicates the outcome of all calls whether successful or not. See the Session Data [IZ] message for details of valid Reason codes.
waiting_launch_time	TimeInStateAwaitingLaunch (J0)	The total amount of time spent in an 'awaiting launch' state.
launched_not_offering_time	TimeInStateLaunched (J1)	The total amount of time spent in an 'launched' state prior to any signaling from the network to indicate acceptance, progress or connection.
proceeding_time	TimeInStateProceeding (J2)	The total amount of time spent between the network accepting the call and the call being signaled as ringing or connected.
ring_time	TimeInStateAlerting (J3)	The total amount of time spent ringing
connect_signaled_parked_time	TimeInStateConnectSignaled (J4)	The total amount of time the call spent in a connected state prior to being enqueued or connected directly to an agent.
queued_time	TimeInStateEnqueued (J5)	The total amount of time spent in a queued state awaiting dequeue to an agent.
expired_time	TimeInStateExpired (J6)	The total amount of time spent in an expired state awaiting requeue or drop.
agent_offering_time	TimeInStateOffering (J7)	The total amount of time spent in an 'offering to agent' state before the agent accepts.
agent_connected_time	TimeInStateConnected (J8)	The total amount of time spent connected to an agent.
hold_time	TimeInStateOnHold (J9)	The total amount of time spent on hold.
mute_time	TimeInStateMuted (K0)	The total amount of time spent with one or more parties to the call muted.
transfer_offer_time	TimeInStateTXOffer (K1)	The total amount of time spent offering a transfer to a third party.
transfer_conference_time	TimeInStateTXConference (K2)	The total amount of time spent in conference during a supervised transfer or 3 party conference.
disconnect_requested_time	TimeInStateDisconnectRequested (K3)	The total amount of time spent waiting for a call disconnect request to be accepted.
disconnected_time	TimeInStateDisconnected (K4)	The total amount of time spent after the call is disconnected, prior to the call being finally disposed.
setup_time	-	The call setup time. Outbound - proceeding_timestamp(Z2) - ci_timestamp(Z1). (secs). Inbound - 0
ring_time	-	The ring time. Outbound - If Z7 is zero - outcome_timestamp(Z3) - proceeding_timestamp(Z2). (secs) If Z7 is not zero - physicalconn_timestamp(Z7) - proceeding_timestamp(Z2). (secs) Inbound - 0
queue_time	-	The queueing time. Outbound - ac_timestamp(Z4) - outcome_timestamp(Z3). (secs) Inbound - If Z4 is not zero - ac_timestamp(Z4) - ci_timestamp(Z1). (secs) If Z4 is zero - sw_timestamp(Z5) - ci_timestamp(Z1). (secs)
talk_time	-	The agent talk time. Outbound - z8_timestamp - ac_timestamp(Z4). (secs) Inbound - If Z4 is not zero - z8_timestamp - ac_timestamp(Z4). (secs) If Z4 is zero - 0
wrap_time	-	The agent wrap time. Derived from tc_timestamp(Z6) - sw_timestamp(Z5). (secs)
wait_time	-	The agent wait time. If (Z0) is zero - 0 If (Z0) is not zero - ac_timestamp(Z4) - Agent Ready timestamp (Z0). (secs)
success_param	Success Rate (SR)	Used to record a success rating for reporting purposes. Valid values are 0 to 100.
conversion_param	Conversion (CO)	Used to indicate the scale of conversion or commitment.
trunkid	Trunk Identifier (TI)	The trunk identifier for the trunk used for this call session.
record_filename	Recording Filename (RF)	The filename of the recording created for this call session.
original_agent	Agent Alternative Identifier (AG)	Used in agent specific callbacks to specify an agent other than the original agent.
ts	Timestamp (TS)	The timestamp for this event. (OADateTime)
id	-
queue	Group Address (GA)	The queue associated with this call session.
ya	Agent Unique ID (YA)	An internally generated unique session id for the agent identified in the Agent Name (AN) parameter
yc	Campaign Unique ID (YC)	An internally generated unique session id for the campaign identified in the Campaign Name (CN) parameter.

callcyclesbusinessdata

Customised call session data (requires customisation of Softdial Publisher - contact Sytel Support for advice.)

Show details

Derived from	Outcome Data [OZ]
Populated by	history oz.xslt

Table Fields

Field	Parameter	Description
transaction_timestamp	Timestamp (TS)	The timestamp of the current event (OADateTime)
sessionid	Session ID (SI)	The ID of the current call session.
phonenum	Telephone Number (TN)	The telephone number of the source call. In inbound terms, this is the CLI/ ANI.
campaign	Campaign Name (CN)	The campaign that the agent was logged in to for this login session
agent	Agent Name (AN)	The agent identifier.
ao_parameter	Agent Outcome (AO)	The agent disposition associated with an agent-classified call.
Field1		User Customisable - Data must be taken from the Outcome Data [OZ] message Data (DT) parameter (formatted as XML)
Field2
Field3
Field4
Field5

Other Tables

As part of the schema there are a number of tables containing metadata for outcome name lookups. These exist to facilitate third-party reporting applications producing meaningful data.

Table	Description
agents	Agent ID/ name cross reference.
agentoutcomes	Agent disposition code / name cross-reference. Should replicate the settings in Namespace Web Console
switchoutcomes	Telephony outcome code / name cross-reference.
teams	Team ID / name cross reference. Reserved for future use

There are additional tables in the schema provided to support Softdial Reporter™. These are not in any normalised form and are used solely to deliver the data storage needs of Softdial Reporter™.

An Entity Relationship Diagram for these tables is shown in Fig. 1.

Fig. 1 - Entity Relationship Diagram for Reporting SQL Tables

For ease of table creation, the table definitions above are also provided in ready-to-execute form, on the SCC web server (by default) at :

C:\Softdial\WebServer\home\admin\www\downloads\schemas

Files are available for the following databases:

Database	Filename
MS SQL Server Data Engine	reporter.msde.sql
MS SQL Server	reporter.mssql.sql
MySQL	reporter.mysql.sql
Oracle	reporter.oracle.sql
Postgres	reporter.postgres.sql