Fusionpbx – export cdr as csv file for all tenants, freepbx – export ampusers as csv file

outgoing calls passing specific carrier in specific period

find postgres password in file /etc/fusionpbx/config.php, 

psql --host=127.0.0.1 --username=fusionpbx -t -c "copy (select domain_name, start_stamp,hangup_cause,duration,billmsec,recording_file,uuid,bridge_uuid,direct
ion,billsec,caller_id_name,caller_id_number,source_number,destination_number,accountcode,answer_stamp,sip_hangup_disposition,pdd_ms,rtp_audio_in_mos from v_x
ml_cdr where direction = 'outbound' and start_stamp < '2020/12/01 00:00' and start_stamp >=  '2020/10/01 00:00' order by start_stamp) to '/tmp/out.csv' csv h
eader;"

pass specific gateway IP address

psql --host=127.0.0.1 --username=fusionpbx -t -c "copy (select domain_name, start_stamp,network_addr,hangup_cause,duration,uuid,bridge_uuid,direction,billsec
,caller_id_name,caller_id_number,source_number,destination_number,answer_stamp,sip_hangup_disposition,pdd_ms,rtp_audio_in_mos from v_xml_cdr where direction
= 'outbound' and network_addr = '208.79.54.220' and start_stamp < '2020/12/01 00:00' and start_stamp >=  '2020/10/01 00:00' order by start_stamp) to '/tmp/pe
erless_208.79.54.220.csv' csv header;"

fusionpbx: restore database

root@fusionpbx:~/srv1# more dbrestor.sh
echo "1CQorF0aQYmBnRIpNklTUz3pU"
pg_restore --username=fusionpbx -h 127.0.0.1 -d fusionpbx fusion_1_17_11_17.sql

calls for specific tenant

#!/bin/bash
export PGPASSWORD="1CQorF0aQYmBnRIpNklTUz3pU"

psql --host=127.0.0.1 --username=fusionpbx -t -c "copy (select domain_name, start_stamp,duration,billsec,caller_id_name,caller_id_number,destination_number,hangup_cause,network_addr from v_xml_cdr where direction = 'outbound' and domain_name = 'fh.tenant.com' and start_stamp < '2020/12/01 00:00' and start_stamp >=  '2020/11/01 00:00' and length(destination_number) >= 10  and billsec > 0 order by start_stamp) to '/tmp/fh.csv' csv header;"

mysql ampusers export

SELECT * FROM (
    SELECT 'username','password'
    UNION ALL
    (
        SELECT `username`,`password` FROM `ampusers` WHERE `username` != 'admin' order by username
    )
) resulting_set
INTO OUTFILE '/tmp/login.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';