On Sunday 01 July 2007 3:26 am, Reza - Asterisk Enthusiast wrote:
> 1. How many channels are being used all together from a SIP Trunk
> provider/v-pri. 2. How many inbound channels are being used at a given
> time from a vpri. 3. How many outbound channels are being used at a given
> time for a vpri. 4. Ultimately how many users are calling each other
> internally within an Asterisk system at a given time.
> All of these information can be obtained from the CDR flat files and/or SQL
> database that contains the CDRs. Before I start writing my queries in
> mysql, is there anything out there other than "Asterisk-Stat"?
I've done some very rudimentary work in this regard, and yes, it's a pain in
SQL. I've gone and looked over it and unfortunately it's not quite what
you're looking for.
Off the top of my sleep-addled mind and pre-coffee, I'd say you want to run a
query that looks for start records that have no matching stop record up to
minute 'x'. There may be many starts and stops for that channel, but you're
only interested in the most recent start without a stop.
For my realestate code, I gave up on CDRs (see my recent rants on
asterisk-dev) and just used res_odbc to write start and stop records, which I
could then correlate. The SQL gets nasty:
SELECT a.red_mbox AS "Property", to_char(sum(b.red_ctime -
c.red_ctime),'MI:SS') AS "Time Spent", cdr.clid AS "Caller*ID" FROM
realestate_detail a,
(SELECT red_uid, red_mbox, red_ctime from realestate_detail WHERE
red_type IN
('exit','hangup')) b,
(SELECT red_uid, red_mbox, red_ctime from realestate_detail WHERE
red_type = 'enter') c,
(SELECT cdr.clid, cdr.uniqueid from cdr) AS cdr
WHERE a.red_uid = b.red_uid
AND a.red_uid = c.red_uid
AND b.red_uid = c.red_uid
AND b.red_mbox = c.red_mbox
AND a.red_mbox <> '?'
AND cdr.uniqueid = a.red_uid
GROUP BY a.red_uid, a.red_mbox, cdr.clid;
:-)
I'd like to take this and add the recent RTCP manager event logging to get
realtime call quality stats for any given time of day. So many projects, so
little time...
> If such product is not yet available, I'm ready to write my own AJAX app,
> that will give me a real time info on how many channels are being used.
> So basically the product I am looking for is a channel logger/analyzer.
> Anything out there that anyone can recommend before I go crazy writing an
> app?
First your newborn, and now this... Your sanity is in grave danger.
-A.
