Queue Call Statistics

Jan 03, 2017 07:54 AM PST
David Jonsson
Teleopti
Hello All,

I am a Technical Consultant for Teleopti, and I am working on a ShoreTel integration for one of our customers using Teleopti's standard integration files. I must apologize for my lack of understanding here, but I am not very familiar with the ShoreTel ECC database and that is why I am reaching out to you folks.

This customer does not like how we are presenting the queue data and has asked us to change how we are retrieving the queue data. We are currently retrieving the queue call data using the following select statement:

select
g_date as [date],
g_time_string as [time],
group_id as [queue],

--offd_direct_call_cnt (Offered Direct Call Count)
isnull(grps.g_acd_ovfin_aband_sti1,0)+
isnull(grps.g_acd_ovfin_aband_sti2,0)+
isnull(grps.g_acd_ovfin_aband_sti3,0)+
isnull(grps.g_acd_ovfin_aband_sti4,0)+
isnull(grps.g_acd_ovfin_aband_sti5,0)+
isnull(grps.g_acd_ovfin_aband_sti6,0)+
isnull(grps.g_acd_direct_aband_sti1,0)+
isnull(grps.g_acd_direct_aband_sti2,0)+
isnull(grps.g_acd_direct_aband_sti3,0)+
isnull(grps.g_acd_direct_aband_sti4,0)+
isnull(grps.g_acd_direct_aband_sti5,0)+
isnull(grps.g_acd_direct_aband_sti6,0)+
isnull(grps.g_acd_callback_activated,0)+
isnull(grps.g_acd_direct_intfl_out,0)+
isnull(grps.g_acd_ovfin_intfl_out,0)+
isnull(grps.g_acd_direct_answd_sti1,0)+
isnull(grps.g_acd_direct_answd_sti2,0)+
isnull(grps.g_acd_direct_answd_sti3,0)+
isnull(grps.g_acd_direct_answd_sti4,0)+
isnull(grps.g_acd_direct_answd_sti5,0)+
isnull(grps.g_acd_direct_answd_sti6,0)+
isnull(grps.g_acd_ovfin_answd_sti1,0)+
isnull(grps.g_acd_ovfin_answd_sti2,0)+
isnull(grps.g_acd_ovfin_answd_sti3,0)+
isnull(grps.g_acd_ovfin_answd_sti4,0)+
isnull(grps.g_acd_ovfin_answd_sti5,0)+
isnull(grps.g_acd_ovfin_answd_sti6,0)+
isnull(grps.g_acd_direct_answd_another,0)+
isnull(grps.g_acd_ovfin_answd_another,0)
as [offd_direct_call_cnt],

--overflow_in_call_cnt (Overflow In Call Count)
isnull(grps.g_acd_ovfin_aband_sti1,0)+
isnull(grps.g_acd_ovfin_aband_sti2,0)+
isnull(grps.g_acd_ovfin_aband_sti3,0)+
isnull(grps.g_acd_ovfin_aband_sti4,0)+
isnull(grps.g_acd_ovfin_aband_sti5,0)+
isnull(grps.g_acd_ovfin_aband_sti6,0)+
isnull(grps.g_acd_ovfin_intfl_out,0)+
isnull(grps.g_acd_ovfin_answd_sti1,0)+
isnull(grps.g_acd_ovfin_answd_sti2,0)+
isnull(grps.g_acd_ovfin_answd_sti3,0)+
isnull(grps.g_acd_ovfin_answd_sti4,0)+
isnull(grps.g_acd_ovfin_answd_sti5,0)+
isnull(grps.g_acd_ovfin_answd_sti6,0)+
isnull(grps.g_acd_ovfin_answd_another,0)+
isnull(grps.g_mail_ovfin_intfl,0)+
isnull(grps.g_mail_ovfin_answd_sti1,0)+
isnull(grps.g_mail_ovfin_answd_sti2,0)+
isnull(grps.g_mail_ovfin_answd_sti3,0)+
isnull(grps.g_mail_ovfin_answd_sti4,0)+
isnull(grps.g_mail_ovfin_answd_sti5,0)+
isnull(grps.g_mail_ovfin_answd_sti6,0) as [overflow_in_call_cnt],

--aband_call_cnt (Abandoned Call Count)
isnull(grps.g_acd_direct_aband_sti1,0)+
isnull(grps.g_acd_direct_aband_sti2,0)+
isnull(grps.g_acd_direct_aband_sti3,0)+
isnull(grps.g_acd_direct_aband_sti4,0)+
isnull(grps.g_acd_direct_aband_sti5,0)+
isnull(grps.g_acd_direct_aband_sti6,0)+
isnull(grps.g_acd_ovfin_aband_sti1,0)+
isnull(grps.g_acd_ovfin_aband_sti2,0)+
isnull(grps.g_acd_ovfin_aband_sti3,0)+
isnull(grps.g_acd_ovfin_aband_sti4,0)+
isnull(grps.g_acd_ovfin_aband_sti5,0)+
isnull(grps.g_acd_ovfin_aband_sti6,0) as [aband_call_cnt],

--overflow_out_call_cnt (Overflow Out Call Count)
isnull(grps.g_acd_direct_intfl_out,0)+
isnull(grps.g_acd_ovfin_intfl_out,0) as [overflow_out_call_cnt],

--answ_call_cnt (Answered Call Count)
isnull(grps.g_acd_direct_answd_sti1,0)+
isnull(grps.g_acd_direct_answd_sti2,0)+
isnull(grps.g_acd_direct_answd_sti3,0)+
isnull(grps.g_acd_direct_answd_sti4,0)+
isnull(grps.g_acd_direct_answd_sti5,0)+
isnull(grps.g_acd_direct_answd_sti6,0)+
isnull(grps.g_acd_ovfin_answd_sti1,0)+
isnull(grps.g_acd_ovfin_answd_sti2,0)+
isnull(grps.g_acd_ovfin_answd_sti3,0)+
isnull(grps.g_acd_ovfin_answd_sti4,0)+
isnull(grps.g_acd_ovfin_answd_sti5,0)+
isnull(grps.g_acd_ovfin_answd_sti6,0) as [answ_call_cnt],

--queued_and_answ_call_dur (Queued and Answered Call Duration)
isnull(grps.g_acd_from_enter_pbx_to_agent,0) as [queued_and_answ_call_dur],

--queued_and_aband_call_dur (Queued and Abandoned Call Duration)
isnull(grps.g_acd_accumulated_abndn,0) as [queued_and_aband_call_dur],

--talking_call_dur (Talking Call Duration)
isnull(grps.g_acd_talk,0)+
isnull(grps.g_outb_acd_talk,0)+
isnull(grps.g_total_hold_time,0)+ --Hold Time option 1
isnull(grps.g_mail_contacts_time,0) as [talking_call_dur],

--wrap_up_dur (Wrap Up Duration)
isnull(grps.g_acd_wrap_up,0)+
isnull(grps.g_outb_acd_wrap_up,0)+
--isnull(grps.g_total_hold_time,0)+ --Hold Time option 2
isnull(grps.g_mail_wrap_up_time,0) as [wrap_up_dur],

--queued_answ_longest_que_dur (Queued and Answered Longest Queue Duration)
isnull(grps.g_acd_longst_until_answered,0) as [queued_answ_longest_que_dur],

--queued_aband_longest_que_dur (Queued and Abandoned Longest Queue Duration)
isnull(grps.g_acd_longest_abndn,0) as [queued_aband_longest_que_dur],

--avg_avail_member_cnt (Average Available Member Count)
0 as [avisnull(grps.g_avail_member_cnt],

--ans_servicelevel_cnt (Answered ServiceLevel Count)
isnull(grps.g_acd_calls_before_target_asa,0) as [ans_servicelevel_cnt],

--wait_dur (Wait Duration)
0 as [wait_dur],

--aband_short_call_cnt (Abandoned Short Call Count)
isnull(grps.g_num_calls_too_short,0) as [aband_short_call_cnt],

--aband_within_sl_cnt (Abandoned Within ServiceLevel Count)
isnull(grps.g_abndn_within_tasa,0) as [aband_within_sl_cnt]

from [ShoreTel].grpo grps

It is my understanding that this select statement is pulling data for 'skill groups' and not for individual queues and that the individual queue data may be in a separate database called CCIR?

Our customer would like us to pull data for individual queues and not the group_id, which I understand represents 'skill group' call data. I need advice for retrieving the same/similar data at the queue level rather than a ‘skill group’ level. Any advice on how I might best solve this problem would be greatly appreciated.
Jan 06, 2017 08:16 AM PST
David Jonsson
Teleopti
I don't seem to be able to edit the original post so I am using reply to post some more information.

I have received some more information regarding our question and where a solution might be found. As I mentioned in my forum post we have been told that the queue call statistics data we need might be found in the CCIR database but there are some concerns about using this database.

1: Would we have access to the CCIR DB or is access to it restricted? 2: If access to CCIR is restricted is it possible to allow access to 3rd party applications? 3: Is the data in the CCIR DB transient? If it is transient how long is the data persisted before it is purged? 4: Is the schema stable or are there regular updates to the schema?

Thank you for any help you can render with these issues. Reply to Post

To reply to this post please Sign In