IP04 and Me

Submitted by Lew on Tue, 04/19/2016 - 15:11

For years, I've played with my 4-port embedded-linux Asterisk PBX (the IP04 from rowetel.com). Now, I've finally decided to move it out of the toychest and into real use.

I haven't come to this decision lightly; it is going to take some work to get this device configured to properly serve my home as it's PBX. First off, I have to set the IP04 up at the telephone demarkation point, with the POTS inbound line connected to the IP04 FXO port and the various inside lines connected to the three FXS ports. Along with that, I have to ensure a regular power supply for the IP04 and an ethernet connection from my LAN to the device.

Next up, channel definitions and dialplans. I've played around with both, and have a good handle on what I want. But there is a large difference between the setup I used to play with, and the setup I need for my home PBX. My intention is to have the PBX provide a "buffer" between incoming calls and my household. It will attempt to deter unsolicited commercial calls, and direct calls (to voicemail if necessary) based on time of day. So, no more telemarketer calls, and (if I wish) no incoming calls during dinner time or after bedtime.

I will manage some of the complexity of this buffer through a web interface, and (possibly) suitably timed cron scripts. So, I'm looking at how I can use the IP04's Asterisk Management Interface to give me the flexibility of control and simplicity of use that I need. Some server-side scripting, some IP04 web scripting, and I'll have a neat, easy-to-use control for my robot receptionist.

Finally, I want to track phone usage for billing and planning purposes. This means using the IP04's Asterisk Call Detail Recording system, and a set of MySQL tables and server-side scripts to import and manipulate data. I expect to produce monthly reports that I can match back to my telephone bills; Bell Canada, you've been served notice.

I also have a couple of blue-sky ideas that I'm looking to persue:

  1. Use the IP04 to connect to a VoIP provider under a 2nd phone number, so that I can have a cheaper POTS bill, and
  2. Permit selected SIP connections inside my LAN, to give the residents here more flexibility in their telephone choices

I have partially implemented local SIP connections, and have started researching bulk VoIP providers. Who knows? Bell might not have me as a customer for much longer.

About: 
cybernetic home
System Management: 
VoIP
IP04
Asterisk

Comments

Submitted by Lew on Tue, 04/19/2016 - 16:17.

I built an MySQL database to hold the Asterisk Call Data Record data, and an automated process to download CDR data into the database on a nightly basis.

The database looks like:

CREATE DATABASE switchboard
CHARACTER SET ascii COLLATE ascii_general_ci;

CREATE TABLE switchboard.cdr (
  cdrAcctCode		VARCHAR(80),
  cdrSrcCallerId	VARCHAR(80),
  cdrDstExtension	VARCHAR(30),
  cdrDstContext		VARCHAR(30),
  cdrTxtCallerId	VARCHAR(80),
  cdrSrcChannel		VARCHAR(80),
  cdrDstChannel		VARCHAR(80),
  cdrLastApp		VARCHAR(80),
  cdrLastData		VARCHAR(80),
  cdrCallStart		TIMESTAMP,
  cdrCallAnswer		TIMESTAMP,
  cdrCallEnd		TIMESTAMP,
  cdrCallDuration	INTEGER,
  cdrCallBillSecs	INTEGER,
  cdrDisposition	VARCHAR(30),
  cdrAMAFlags		VARCHAR(30),
  cdrRecordId		DECIMAL(15,3) UNSIGNED PRIMARY KEY
) COMMENT 'Asterisk Call Detail Record',
  ENGINE InnoDB,
  DEFAULT CHARACTER SET ascii COLLATE ascii_general_ci;


and the download job looks like

#!/usr/local/bin/rmonitor @ /bin/bash
# Retrieve Call Detail Records from the IP04 and load them into the database
if CDRFILE=$(tempfile -p CDR. -s .csv)
then
  chmod a+r "$CDRFILE"
  if rcp receptionist@receptionist:/var/log/asterisk/cdr-csv/Master.csv "$CDRFILE"
  then
    if mysql -u dataloader -ppassword <<-ENDSQL
	LOAD DATA INFILE '${CDRFILE}'
	IGNORE INTO TABLE switchboard.cdr
	FIELDS ENCLOSED BY '"' TERMINATED BY ','
	LINES TERMINATED BY '\n';
	ENDSQL
    then
      EMPTYCDR=$(tempfile -p CDR_EMPTY. -s .csv)
      rcp "${EMPTYCDR}" receptionist@receptionist:/var/log/asterisk/cdr-csv/Master.csv
      rm "${EMPTYCDR}"
      RC=0
    else
      echo mysql failed to load CDR data into switchboard.cdr >&2
      RC=3
    fi
  else
    echo rcp failed to retrieve CDR data from receptionist IP04 >&2
    RC=2
  fi
else
  echo Cant allocate a tempfile >&2
  RC=1
fi
exit $RC