SHORETEL IS NOW PART OF MITEL. See how powering connections in the cloud is now brilliantly simple.

Queue Call Statistics

Jan 03, 2017 07:53 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:15 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.
Feb 17, 2017 03:32 AM PST
Sooriya Prabhu (c)
ShoreTel
Hi  David Jonsson,
 
Going through your request on this forum, I understand ShoreTel’s Professional team can help you. I would request you to contact ShoreTel professional service with the following contact details.

Email: professionalservices@shoretel.com
Phone: 8004259385  x3331

 

To reply to this post please Sign In