Possible to Query Asterisk CEL info directly from the database? (PostgreSQL)

Question

Hy,

I am looking for a way to query my VOIP info directly from the database using PostgreSQL. The VOIP service is the CEL (row per event) Asterisk. My database looks like this:

|Field name  | Value/Example            | Notes
|eventtype   | CHAN_START               | The name of the event. The list of events that may occur can be found in Table 24.7, “CEL event types”.
|eventtime   | 2010-08-19 07:27:19      | The time that the event occurred.
|cidname     | Julie Bryant             | The caller ID name set on the channel associated with this event.
|cidnum      | 18435551212              | The caller ID number set on the channel associated with this event.
|cidani      | 18435551212              | The Automatic Number Identification (ANI) number set on the channel associated with this event.
|cidrdnis    | 18435551234              | The redirecting number set on the channel associated with this event.
|ciddnid     | 18435550987              | The dialed number set on the channel associated with this event.
|exten       | 101                      | The extension in the dialplan that is currently being executed.
|context     | LocalSets                | The context for the extension in the dialplan that is currently being executed.
|channame    | SIP/0004F2060EB4-00000010| The name of the channel associated with this event.
|appname     | Dial                     | The name of the dialplan application currently being executed.
|appdata     | SIP/0004F2060E55         | The arguments that were passed to the dialplan application that is currently being executed.
|amaflags    | DOCUMENTATION            | The Automatic Message Accounting (AMA) flag associated with this call. This may be one of the following: OMIT, BILLING, DOCUMENTATION, or Unknown.
|accountcode | 1234                     | An account ID. This field is user-defined and is empty by default.
|uniqueid    | 1282218999.18            | The unique ID for the channel that is associated with this event.
|userfield   | I like waffles!          | User-defined event content.
|linkedid    | 1282218999.18            | The per-call ID. This ID helps tie together multiple events from multiple channels that are all a part of the same logical call. The ID comes from the uniqueid of the first channel in the call.
|peer        | SIP/0004F2060E55-00000020| The name of the channel bridged to the channel identified by channame. 

I want to get all answered calls for today's date. I tried the flowing:

    --COMPLETE PHONECALL
SELECT  T.ID, T.EVENTTYPE, T.EVENTTIME, T.CID_NAME, T.CID_NUM, T.CID_ANI, T.CID_DNID, T.CID_RDNIS, T.EXTEN, T.CONTEXT, T.CHANNAME,
    T.APPNAME, T.APPDATA, T.AMAFLAGS, T.ACCOUNTCODE, T.PEERACCOUNT, T.UNIQUEID, T.LINKEDID
FROM PUBLIC.CEL T
WHERE LINKEDID IN (
    SELECT LINKEDID
    FROM PUBLIC.CEL T1
    WHERE DATE(T1.EVENTTIME) = (SELECT MAX(DATE(EVENTTIME)-1) FROM PUBLIC.CEL)
    AND EVENTTYPE = 'CHAN_START'
    AND CID_NUM NOT LIKE '20%'
    AND EXISTS(
        SELECT * 
        FROM PUBLIC.CEL T2
        WHERE 1=1 --DATE(T2.EVENTTIME) = (SELECT MAX(DATE(EVENTTIME)) FROM PUBLIC.CEL)
        AND EVENTTYPE = 'APP_START'
        AND T1.ID < T2.ID
        AND T1.UNIQUEID = T2.UNIQUEID
        AND EXISTS(
            SELECT * 
            FROM PUBLIC.CEL T3
            WHERE 1=1 --DATE(T3.EVENTTIME) = (SELECT MAX(DATE(EVENTTIME)) FROM PUBLIC.CEL)
            AND EVENTTYPE = 'ANSWER'
            AND T2.ID < T3.ID 
            AND T2.UNIQUEID = T3.UNIQUEID
            AND EXISTS(
                SELECT * 
                FROM PUBLIC.CEL T4
                WHERE 1=1 --DATE(T4.EVENTTIME) = (SELECT MAX(DATE(EVENTTIME)) FROM PUBLIC.CEL)
                AND EVENTTYPE = 'HANGUP'
                AND T3.ID < T4.ID 
                AND T3.UNIQUEID = T4.UNIQUEID
                AND EXISTS(
                    SELECT * 
                    FROM PUBLIC.CEL T5
                    WHERE 1=1 --DATE(T5.EVENTTIME) = (SELECT MAX(DATE(EVENTTIME)) FROM PUBLIC.CEL)
                    AND EVENTTYPE = 'CHAN_END'
                    AND T4.ID < T5.ID
                    AND T4.UNIQUEID = T5.UNIQUEID
                    )
                )
            )
        )
    GROUP BY T1.LINKEDID
    ORDER BY T1.LINKEDID ASC
)
ORDER BY T.LINKEDID ASC

But that didn't work!. Another thing i tried was:

SELECT *
FROM PUBLIC.CEL
DATE(EVENTTIME) = (SELECT MAX(DATE(EVENTTIME)) 

I then used birt to filter on the desired output.. which didn't give me the desired output.

Is there some doc about how to use SQL to query CEL database? I dont want to use tools like 'Asternic'

Thanks

ps. here is a link that explains the CEL 'table' database http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/Monitoring_id246945.html#Monitoring_id255410

Answer 1

If you just want the calls that were answered for the current day, then do this:

select * from cel WHERE eventtype = 'ANSWER' and eventtime > curdate() limit 1 GROUP BY linkedid;

But I assume there's more info you're looking for? Can you clarify what exactly you're looking for? Are you looking for the length of time for all answered calls? Or do you just want all the events for answered calls?

To get all events for answered calls, you could try something like:

SELECT * FROM cel WHERE eventtime > curdate() and linkedid IN (select DISTINCT linkedid from cel WHERE eventtype = 'ANSWER' and eventtime > curdate());

Let me know in the comments exactly what information you're trying to get and I'll see what I can come up with.