Can Asterisk interact with customer database?

Cwhipple · October 25, 2019, 11:38am

Good morning! I am a developer whom is new to phone systems, and am curious to know if Asterisk can support my current need for a project.

I am looking to set up a phone system which interacts with my customer database. The workflow I envision looks something like this:

Customers calls my business line -> PBX checks caller ID to obtain their number -> PBX sends number (over some protocol) to external database -> Database checks for that number against existing customers -> IF it finds a match, the database sends (presumably via the same protocol as before) an extension back to the PBX to connect them to their specific service representative. If no match is found, connect them to the next available representative.

In short, I want a dynamic dial plan based on input from an external database. With that, I have a few questions:

1). Is this possible with Asterisk?
2). On a scale of “helloWorld” to 150 gB AAA video game title, how difficult would this be to implement?
3). What protocols does asterisk support to achieve this?

Thank you in advance for your comments and time! looking forward to learning a new skill!

EkFudrek · October 25, 2019, 2:29pm

I think it is not a good idea to call an external database when a call comes in, unless you know how to asynchronously update the display. The problem is, if there is a communication problem or something is wrong with the database, no calls get processed and you can’t wait until an internet connection times out.

If you keep a local database or simply use astdb.sqlite3 and regularly update it, you should be fine. It’s relatively straightforward to implement this.

johnkiniston · October 25, 2019, 3:06pm

Yes you can totally do this.

Look at func_odbc, you can define a custom sql command to lookup callers in your external database.

I’d say this is pretty easy, if you look for “Asterisk the definitive guide” on google you should find the web version of the book and it has a chapter on ODBC.

sedwards · October 25, 2019, 3:29pm

Yes.
Pretty simple if you have programming skills.
You can do this using the ODBC functions in the dialplan ‘language’ – but I wouldn’t.

I would do this by writing an ‘external’ program and calling it from the dialplan using the agi() function. This way you are using a ‘real’ programming language (C, Perl, PHP, Python, etc) and can use real programming language features like a debugger and have better access to error handling.

Using the ODBC functions always looks messy and fragile to me. I like having a nice little black box I can call so my dialplan can be nice an simple like:

[customer-service]
        exten = _x.,1,         verbose(1,[${EXTEN}@${CONTEXT}!${ANI}])
        same = n,		       agi(lookup-cs-rep)
        same = n,              goto(representative,${REP-EXTEN},1)

Where lookup-cs-rep would:

Connect to the database.
Lookup the representative’s extension in the database.
Set a channel variable (REP-EXTEN) with the representative’s extension or the extension of the representative queue if the customer does not have an assigned rep.

ambiorixg12 · October 25, 2019, 8:51pm

Extremely simple, I made this quick code you can adapt to your needs, it will serve you as reference

gist.github.com

https://gist.github.com/ambiorixg12/e486d9f036915bb51e04d4e2471ae1ae

MYSQL CID Lookup

[cidlookup]
exten=>_x.,1,Noop( checking the callerid ${CALLERID(num)})
same=>n,Set(__cid=${SHELL(mysql --user=root --password='12340' --skip-column-names  dialer -e 'select dnc_phone  from dnc where dnc_phone="${CALLERID(num)}" limit 0,1 ')})
same=>n,Noop( result ${cid:0:1})
same => n,GotoIf($["${CALLERID(num)}"="${cid:0:-1}"]?allow:disallow)
same=>n(allow),Playback(demo-thanks)
same=>n,Hangup()
same=>n(disallow),Playback(im-sorry)
same=>n,Hangup()

Cwhipple · November 7, 2019, 1:49am

Thank you all for your comments! I am working on implementing this and will report back!

benphone · November 7, 2019, 8:35am

usually u need to get an api to data base

in dial plan you could use the curl function something like this

same => n,Set(curltmp={CURL({serverurl}/RegistrationRest/}) ;CHECK IF THERE ARE EVENTS

etc

dux · November 11, 2019, 3:20pm

I work with Microsoft Sql Server via php and AGI. Also, as has already been noted, it is possible to interact by means of odbc (I did not manage to execute stored procedures with the asterisk odbc interface, just queries).

Cwhipple · November 20, 2019, 1:47am

This is incredibly helpful, thank you! I have asterisk running now and have written a script in python that is accessed by AGI. I can confirm that the python is working and can show the new extension using this line

sys.stdout.write(‘verbose %s 0 \n’ % r.content)

where r.content is the new extension.

But this only writes the variable to the console, how do I write this to a system variable accessible to the dialplan?

Cwhipple · November 22, 2019, 7:41pm

Good afternoon everyone,

Just to follow up, I was able to get this working, had a typo in the dialplan (that took me 3 days to find!) Thank you again for all your help!

best,
Chris

system · December 22, 2019, 7:55pm

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.