build CUIC Detailed Call CSQ Agent Report from UCCX db in sql query

[Ning Jiang]

Hello,

Can anyone help me with the sql query that how to build table that exactly like CUIC stock report 'Detailed Call CSQ Agent Report'? I am trying to pull data from UCCX database and display the same records, but there are always some records not matching up with the one that in CUIC report with my sql query. I am wondering if there is any default sql query that can be applied?

*Is it normal that there is no way I can click into 'report definition' in CUIC? Where I guess it should store the query that how to build this table into a report in sql?

Thank you for the help in advance!

[Jonathan Schulenberg]

Run the report and the click the SQL button to see the query. It will execute a stored procedure.

Report definitions are only accessible in the CUIC Premium license. That was traditionally a separate VM but I thought the latest versions of CCX Premium gave you limited CUIC Premium features/access as well.

Also a disclaimer that querying the database over ODBC for anything other than wallboards (the RT tables only) or custom CUIC report development/testing is not officially supported. The throttle mechanism to prevent a DB query from impacting call processing is implemented within CUIC, not the underlying DB connection.

[Ning Jiang]

Hi Jonathan,

Thank you so much for the help and kind explanation. I am trying to use jdbc url connection to access uccx db, pull data from tables and build a delta table that has exact same fields in Detailed Call CSQ Agent Report plus some other fields from other tables like ContactCallDetail, AgentConnectionDetail,etc.

Do you know where I can actually look at the stored procedure of this table? Because I tried to click the SQL button after run the report but it gives me call sp_call_csq_agent(parameters), but I want to look at the sql query that in the stored procedures. Thank you again for your kind help!

[Thomas G. Johannesen]

Hey Ning

Here is the sp

CREATE FUNCTION sp_call_csq_agent( p_starttime DATETIME YEAR TO SECOND,
                                   p_endtime DATETIME YEAR TO SECOND,
                                   p_sortby INT DEFAULT 0,
                                   p_calledNumber LVARCHAR(4000) DEFAULT 'NULL',
                                   p_callingNumber LVARCHAR(4000) DEFAULT 'NULL',
                                   p_applicationName LVARCHAR(4000) DEFAULT 'NULL',
                                   p_contactType NVARCHAR(100) DEFAULT 'NULL',
                                   p_originatorType NVARCHAR(100) DEFAULT 'NULL',
                                   p_destinationType NVARCHAR(100) DEFAULT 'NULL',
                                   p_resourceName LVARCHAR(4000) DEFAULT 'NULL',
                                   p_csqName LVARCHAR(4000) DEFAULT 'NULL',
                                   p_durationGreaterThanEqualTo INT DEFAULT -1,
                                   p_durationLessThanEqualTo INT DEFAULT -1,
								   p_user LVARCHAR(4000) DEFAULT 'NULL')
RETURNING SMALLINT AS node_id,
          DEC(18, 0) AS session_id,
          SMALLINT AS sequence_num,
          VARCHAR(35) AS session_id_seq, 
          DATETIME YEAR TO SECOND AS start_time,
          DATETIME YEAR TO SECOND AS end_time, 
          SMALLINT AS contact_disposition, 
          NVARCHAR(30) AS originator_dn,
          NVARCHAR(30) AS destination_dn,
          NVARCHAR(30) AS called_number, 
          NVARCHAR(30) AS application_name, 
          SMALLINT AS qindex, 
          NVARCHAR(255) AS csq_names,
          SMALLINT AS queue_time, 
          NVARCHAR(50) AS agent_name, 
          SMALLINT AS ring_time, 
          INT AS talk_time, 
          SMALLINT AS work_time,
          DATETIME YEAR TO SECOND AS latestSynchedTime;
       
DEFINE l_session_id DEC (18, 0);
       DEFINE l_session_id_seq VARCHAR(35);
       DEFINE l_start_time, l_end_time, l_latestsynchedtime DATETIME YEAR TO SECOND;
       DEFINE l_contact_type, l_contact_disposition, l_originator_type, l_qindex,l_destination_type, l_nodeid, l_sessionseqnum SMALLINT;
       DEFINE l_originator_id, l_originator_dn, l_destination_id, l_destination_dn,l_called_number, l_original_called_no, l_application_name NVARCHAR(50);
       DEFINE l_queue_time,l_talk_time, l_hold_time, l_work_time, l_ring_time INT;
       DEFINE l_resourcename NVARCHAR(100);
       DEFINE l_csq_names NVARCHAR(255);
       DEFINE l_resourcetype integer;   
     
  DEFINE l_supervisorType integer;    
       DEFINE l_resourceloginid LVARCHAR(4000);
       FOREACH EXECUTE FUNCTION sp_ccdr(p_starttime, p_endtime, p_sortby, 'NULL', p_calledNumber,
                                        p_callingNumber, p_applicationName, p_contactType,
                                        p_originatorType, p_destinationType, p_durationGreaterThanEqualTo,
                                        p_durationLessThanEqualTo, p_resourceName, p_csqName,'t',p_user)
               INTO l_session_id, l_sessionseqnum, l_nodeid, l_session_id_seq, l_start_time, l_end_time,
                    l_contact_type, l_contact_disposition, l_originator_type,
                    l_originator_id, l_originator_dn, l_destination_type,
                    l_destination_id, l_destination_dn, l_called_number,
                    l_original_called_no, l_application_name, l_queue_time,
                    l_talk_time, l_ring_time, l_hold_time, l_work_time, l_resourcename, l_csq_names,
                 l_qindex, l_latestsynchedtime
                    RETURN l_nodeid, l_session_id, l_sessionseqnum, l_session_id_seq, l_start_time, 
                           l_end_time, l_contact_disposition, l_originator_dn, l_destination_dn,
                     l_called_number, l_application_name, l_qindex, l_csq_names, l_queue_time,
                           l_resourcename, l_ring_time, l_talk_time, l_work_time, l_latestsynchedtime
                    WITH RESUME;
       END FOREACH;
END FUNCTION
DOCUMENT "This procedure generates the Detailed Call, CSQ, Agent Report.";