SELECT calldate, did, src, cnam, CONCAT(dcontext, ',', dst) AS 'dst-context',
  disposition, duration, userfield, channel, dstchannel
FROM cdr ORDER BY calldate DESC

I spent much of January designing, writing, testing, and implementing a process for whitelisting callers into a FreePBX system based on the CallerID number and name as supplied by the trunk on the inbound route. The following six blog entries are the result of this effort.

1. Introduction to Whitelist-AGI
2. Whitelist-AGI: Whitelisting strategy
3. Whitelist-AGI Design goals
4. Setting up Whitelist-AGI
5. Whitelist-AGI: Whitelisting a number from your extension
6. Whitelist-AGI Implementation notebook

From the Wikipedia article (permalink):

Pekwachnamaykoskwaskwaypinwanik Lake is a lake of Manitoba. The name is Cree for “where the wild trout are caught by fishing with hooks.” It is the longest place name in Canada at 31 letters long. It is located just southeast of Red Sucker Lake in northeastern Manitoba, near its border with Ontario.

I don’t know how Cree separates its words into syllables, but one possibility is this:

  Pek-wach-na-may-kos-kwask-way-pin-wa-nik Lake

It wasn’t tagged on OpenStreetMap, so I created an account and added the name. Here it is.

Contents

I wrote these notes because the MySQL/MariaDB documentation uses Stored Procedure, Stored Routine, Stored Program, and Stored Function, and I wanted to tease out the difference between them.

MariaDB has two statements for creating stored entities:

• CREATE PROCEDURE - A PROCEDURE is invoked using CALL
• CREATE FUNCTION - FUNCTION is invoked using SELECT function-name(params)

In the MariaDB documentation, “Stored Procedures” is a topic under “Stored Routines” in Programming and Customizing MariaDB.

Read more (14 paragraphs) …

Contents

These are notes I created while designing, writing, and testing Whitelist-AGI. I’ve included them here because they may be useful to someone somewhere on the Web at some unknown time.

Read more (40 paragraphs) …

Contents

FreePBX CID Lookup: SQL query for NextCloud database

SELECT cp2.value AS DB_NAME FROM oc_cards_properties AS cp1
LEFT JOIN oc_cards_properties AS cp2 ON cp2.addressbookid=cp1.addressbookid
  AND cp2.cardid=cp1.cardid AND cp2.name='FN'
WHERE cp1.name='TEL' AND 
  (cp1.value LIKE '[NUMBER]%' OR cp1.value LIKE CONCAT('%', [NUMBER]-10000000000))
LIMIT 1

But it didn’t work. The above query is 294 characters after eliminating newlines and unneeded whitespace, and FreePBX silently truncates the query to 255 characters! I managed to trim it down to the following:

SELECT b.value oc_cards_properties AS a
LEFT JOIN oc_cards_properties AS b ON b.addressbookid=a.addressbookid
  AND b.cardid=a.cardid AND b.name='FN'
WHERE a.name='TEL' AND 
  (a.value LIKE '[NUMBER]%' OR a.value LIKE CONCAT('%', [NUMBER]-10000000000))

That’s only 247 characters, and it works as expected.

To get this to work I had to grant access to the oc_card_properties table in in the nextcloud database on my server:

[root@penguin]SQL: GRANT SELECT ON nextcloud.oc_cards_properties TO 'asterisk'@'pbx'
  IDENTIFIED BY '************'"

Converting all English .ulaw files to .wav

I wrote the following little script to copy all the .ulaw files in /var/lib/asterisk/sounds/en to /tmp/asterisk-sounds-en-wav and convert them to WAV format. This makes it easer to play them on another computer because most systems know how to play WAV files but likely aren’t able to play u-law files.

mkdir /tmp/asterisk-sounds-en-wav
[ $? == 0 ] && cd /var/lib/asterisk/sounds/en
[ $? == 0 ] && find . -name '*.ulaw' | xargs tar cf - | tar x -C /tmp/asterisk-sounds-en-wav -f -
[ $? == 0 ] && cd /tmp/asterisk-sounds-en-wav
if [ $? == 0 ]; then
  echo -n "Converting files "
  find . -name '*.ulaw' | sort | while read F; do
    echo -n .; sox -r 8000 -c 1 -e u-law -t raw $F ${F/.ulaw/.wav}
    touch -r $F ${F/.ulaw/.wav}; rm -f $F
  done
  echo
fi

Contents

Introduction to these notes

This is nowhere near an exhaustive treatise on Asterisk. I wrote these notes while learning enough about Asterisk to do something that FreePBX doesn’t seem to do: whitelisting callers. I needed a handler that would allow me to send calls from known CIDs directly to an extension, and send calls from unknown CIDs to an AVR (automated voice response) script.

As such these notes are incomplete in many areas because I didn’t see the need to learn much about them, being as they were outside the scope of the whitelist project. For example, I have some detail on Dialplan and AGI (Asterisk Gateway Interface) because those are what I used, but I merely mention ARI (Asterisk REST Interface) because it didn’t need it.

I’m also an experienced programmer, so I made only enough notes to get an understanding of how Asterisk Dialplan works. For complete reference information you’ll need to visit the Asterisk Documentation Wiki.

These notes are based on Asterisk 16. As of January 2020, Asterisk 17 is available but was not part of FreePBX.

Read more (50 paragraphs) …