asterisk users - Sep 2003 - Request for comments on queue statistics

All,

I've been doing a lot of work on the Queue application and have some ideas
I wanted to get some feedback on.  Many people, myself included, have
stated their desire for more/better queue statistics.  We ought to be able 
to bring things up to a standard comparable to the established ACD apps.

While some basic metrics (completed/abandoned calls, which agents last
took a call, etc) can be kept in memory for real-time use, any numbers 
handled in this way are neither auditable or storable for management 
review.

As a first step towards getting real queue statistics going (a project 
arguably adjunct to Asterisk itself), I propose that the Queue app be 
modified to produce its own "Queue Log," which could be stored either
as
text or in a SQL table.  For simplicity I would probably just implement a 
MySQL backend.

The "Queue Log" could record events such as when an agent took a call,
when a call was abandoned, when a call enters the queue, etc.

It's been suggested that queue stats can be gleaned from CDR, but I submit
that they really can't be, as there is no way to tell queue holdtime,
whether a call is abandoned, etc.  In theory the Queue Log could link back
into the CDR by way of the unique identifier field and serve as an
enhancement to CDR; but it can exist be independent of CDR.  Let CDR do 
its thing, let the queue app worry about queue logging.

Once a rich Queue Log is available, it becomes fairly easy to generate the
sorts of statistics people have been asking for.  Thoughts?

Mark, does this contradict any sort of master plan on the subject?

Regards,
Dave

====================================================================David C.
Troy   [dave@toad.net]                   410-384-2500 Sales
ToadNet - Want to go fast?                        410-544-1329 FAX
570 Ritchie Highway, Severna Park, MD 21146-2925  www.toad.net

Hi David, group..

I definitely agree that some kind of external statistics would be of great
benefit. I implemented some event logging on an IVR system I worked on
recently, where events were stored in a table related back to the unique ID
in the CDR record (so one CDR, many events) and it worked quite well - nice
and easy to get answers to the questions management like to ask.

I also like the idea of logging to text files - it's plain and simple and
doesn't require the use of MySQL (not sure where we stand on licensing for
something like that?) (I guess not a big issue if this is an optional
thing?).

But bounce back to the other side of the fence, I'd have to say my
preference would be to have stats in a MySQL database purely for ease of
access. Have some pretty web pages displaying near real time queue
statistics etc.. sexy!

I guess one of the questions is whether you go with an event logging type
system, one call having multiple records, with the downside of having to
maybe do some other processing later (working out the time between events
etc?) or have a single record per call with multiple fields defining the
events of the call's lifetime. The record could only be written at the end
of the call which means no information would be available for calls in
progress, although maybe in-progress calls could have records written and
updated every xx seconds with a flag showing when the call/record is
complete? *shrugs* just tossing ideas around there..

I've played with LED readerboad signs before and they're pretty easy to
program up - Does anyone else on the list have any interest in making these
ACD stats available in this way?

Cheers
Paul

>As a first step towards getting real queue statistics going (a project
>arguably adjunct to Asterisk itself), I propose that the Queue app be
>modified to produce its own "Queue Log," which could be stored
either as
>text or in a SQL table.  For simplicity I would probably just implement a
>MySQL backend.

This is always a thorn, I would argue this should be done via a
database astraction layer, database like opinons, everybody has thier own
>The "Queue Log" could record events such as when an agent took a
call,
>when a call was abandoned, when a call enters the queue, etc.
>
>It's been suggested that queue stats can be gleaned from CDR, but I
submit
>that they really can't be, as there is no way to tell queue holdtime,
>whether a call is abandoned, etc.  In theory the Queue Log could link back
>into the CDR by way of the unique identifier field
AGREED this is most certainly the correct way to do it, then we can make
simple
joins to the the cdr & the queue table  via this foreign key
relationship
>and serve as an enhancement to CDR; but it can exist be independent of CDR.
Let CDR do
>its thing, let the queue app worry about queue logging.
TOTALLY AGREED
>Once a rich Queue Log is available, it becomes fairly easy to generate the
>sorts of statistics people have been asking for.  Thoughts?
Yup this is the right high level approach we now need to agree on the
schema(s),
and the events of interest
To get the ball rolling & very incomplete, off the top of my head :)
I'd suggest with have a
queueEntry- the header table 1 for each unique call
 -QueueEntryID - PK for this table
 -UniqueCallID - FK to the CDR entry
 -QueueName
 -StartTime
 -WaitTime
 -Agent
 -Group
 ...kitchen sink
queueEntryEvent - many events that are recorded against a given unique
queueEntry
I guess i could also submit to a denormalized single table as well :)

queueEntryEvent - the header table 1 for each unique call
 -QueueEntryEventID - FK for this table
 -QueueEntryID - FK to table queueEntry
 -QueueEventType
 -StartTime
 -End Time
 -Note (arbitrary stuff that is unique to an event type)
...
Then in queue.conf
queueDB=csv, mysql, ..whatever
queueLog - yes/no to log calls on this queue
queueEvents -definintion of events of interest that will trigger entry into
the queueEntry Tables
                  - that is defined for each queue
                  -a comma delimited list of event names of interest
                   eg join, leave, hungup, exitToContext, Duration, Parked,
Transfered ...

>I've played with LED readerboad signs before and they're pretty easy
to
>program up - Does anyone else on the list have any interest in making these
>ACD stats available in this way?
I have done some trival work with matrix orbital lcd to show some stats
counts, calls parked etc
Just find lcd a bit small do you have lead on bigger LED signs that
you have used b4 ??