Shuttle - Agent Idle Time

The agent idle time is calculated by getting the difference between the login time and the pause and the talk time.

Here are the MySQL instructions in order to create a simple view to collect the agent idle time.

Requirements:

1. You need to identify a queue as a reference in order to calculate the total PAUSE and LOGIN time. That queue must be assigned to all agents. 

   Example

   Create a queue called "internal" and assign all agents to it. We'll use the "internal" queue in the MySQL view creation.

2. The agents must use always the PAUSE status for all queues. If they use the partial pause, the IDLE time calculation could not be accurate.
   
   
3. The outbound calls are not included in the total talk time. The agents must be paused (e.g. pause type "Outbound") while they are in outbound call conversation.

Add the MySQL views

 Run the following MySQL queries:

remember to replace the queue 'internal' with the queue name you are using as a reference.

/*Get the total LoginTime */
create or replace view sl2 as SELECT agent,enterdtm,SEC_TO_TIME(SUM(duration)) AS LoginTime FROM agent_log WHERE TYPE='REMOVEMEMBER' AND queuename='internal' group by agent,enterdtm;
/*Get the total PauseTime*/
create or replace view sp2 as SELECT agent,enterdtm,SEC_TO_TIME(SUM(duration)) AS PauseTime FROM agent_log WHERE EVENT='PAUSE' AND queuename='internal' group by agent,enterdtm;
/*Get the total TalkTime*/
create or replace view st2 as SELECT agent,rtdtm,SEC_TO_TIME(SUM(calltime)) AS TalkingTime FROM call_log WHERE connect=1 GROUP BY agent,rtdtm;

and now we add the view to get the IDLE time

CREATE OR REPLACE VIEW agent_timing AS
 SELECT sl.agent agent, sl.enterdtm enter_date, sl.LoginTime logged_time, st.TalkingTime talk_time, sp.PauseTime pause_time,
             TIMEDIFF(sl.LoginTime, ADDTIME(st.TalkingTime,sp.PauseTime))  idle_time
 FROM sl2 sl, sp2 sp, st2 st
WHERE sl.agent=sp.agent
   AND sl.agent=st.agent
   AND sl.enterdtm=sp.enterdtm
   AND sl.enterdtm=st.rtdtm;

Get the IDLE time

SELECT * from agent_timing where agent="john.doe"