Querying call data

[scg_chris]

How does one go about querying the call data? I see that this information is stored on a SQLite instance, and I have not really worked with that directly before. Any tips on how to do this process?

Thanks!

 

[Adrian Fretwell]

I'm sorry, I don't really understand what you are asking.
Most installs of FusionPBX store data about completed calls in a Postgresql table called v_xml_cdr. If you want to get information about calls that are in progress, you can query the Freeswitch event socket or watch debug information in fs_cli. There should be no need to interact with Freeswitch's SQLite database, infact if you caused a table lock, it would be harmful to the Freeswitch process using it.

 

[nktech1135]

By query it's unclear if you mean via some db management tool? or just in general, how can i see the call records?
If the latter, log into fusion and click applications>call detail records.

Hope it helps.

 

[scg_chris]

@Adrian Fretwell thanks! I got connect and see the call data in the v_xml_cdr.

@nktech1135, yes I am using a DB tool to make a connection and run queries to create reports needed. The call detail records are not sufficient for our purposes. Thanks for your input!

 

[scg_chris]

Ok I have gotten everything I need from the database calls but my inexperience working with json with Postgres is making getting call times very difficult. I can see that call flow give us the times for each section of the call.

select
    xml_cdr_uuid,
    json->'callflow'
from
    fusionpbx.public.v_xml_cdr
where
    start_stamp > now() - '1 day'::interval;

which gives me arrays of times like this..

[
    {
        "times": 
        {
            "hangup_time": "1588604603083829",
            "bridged_time": "1588604592603811",
            "created_time": "1588604575783849",
            "answered_time": "1588604575803817",
            "progress_time": "1588604575803817",
            "transfer_time": "0",
            "last_hold_time": "0",
            "resurrect_time": "0",
            "hold_accum_time": "0",
            "progress_media_time": "0",
            "profile_created_time": "1588604582343873"
        },
        "extension": {...},
        "@attributes": {...},
        "caller_profile": {...},
    },
    {
        "times": 
        {
            "hangup_time": "0",
            "bridged_time": "0",
            "created_time": "1588604575783849",
            "answered_time": "1588604575803817",
            "progress_time": "1588604575803817",
            "transfer_time": "1588604582343873",
            "last_hold_time": "0",
            "resurrect_time": "0",
            "hold_accum_time": "0",
            "progress_media_time": "0",
            "profile_created_time": "1588604575783849"
        },
        "extension": {...},
        "@attributes": {...},
        "caller_profile": {...},
    },
    {
        "times": 
        {
            "hangup_time": "0",
            "bridged_time": "0",
            "created_time": "1588604575783849",
            "answered_time": "0",
            "progress_time": "0",
            "transfer_time": "1588604575783849",
            "last_hold_time": "0",
            "resurrect_time": "0",
            "hold_accum_time": "0",
            "progress_media_time": "0",
            "profile_created_time": "1588604575783849"
        },
        "extension": {...},
        "@attributes": {...},
        "caller_profile": {...},
    }
]

What I am trying to get is the length of call after outgoing transfer. Does any one have any suggestions on the Postgres json functions that could help get this data out?