asterisk users - Nov 2006 - Rewriting caller ID from database?

Hi

Most of our customers have generic names like "Hospital", so I need to
rewrite their caller ID name by looking up the number in a database on the 
Asterisk server, and rewriting the name such as "Reading Hospital" so
that
we know who's calling.

Any idea if this can be done with Asterisk, and how to do it?

Thank you.

There are two I can think of.

Hoodahek and asterdex (or asteridex)

We used hoodahek at first, but now use asterdex(sp?)
It has a web interface to enter the new names into.

We use it to fixup, corp. cell phones and used to use it for our leagcy PBX
extensions.

-- 
-- 
Steven

http://www.glimasoutheast.org



"Vincent Delporte" <udc91pk02@bigfoot.com> wrote in message
news:5.1.1.6.2.20061122140023.00bdd358@127.0.0.1...
> Hi
>
> Most of our customers have generic names like "Hospital", so I
need to rewrite their caller ID name by looking up the number in a
> database on the Asterisk server, and rewriting the name such as
"Reading Hospital" so that we know who's calling.
>
> Any idea if this can be done with Asterisk, and how to do it?
>
> Thank you.
>
> _______________________________________________
> --Bandwidth and Colocation provided by Easynews.com --
>
> asterisk-users mailing list
> To UNSUBSCRIBE or update options visit:
>   http://lists.digium.com/mailman/listinfo/asterisk-users
>

Vincent Delporte wrote:
> Hi
>
> Most of our customers have generic names like "Hospital", so I
need to
> rewrite their caller ID name by looking up the number in a database on 
> the Asterisk server, and rewriting the name such as "Reading
Hospital"
> so that we know who's calling.
>
> Any idea if this can be done with Asterisk, and how to do it?
>
Entries are stored in the internal database.  Then at the incoming 
context I have:

exten => _XXXXXXXXXX,n,Set(CALLERID(name)=${DB(CIDNAME/${CALLERIDNUM})})
exten => _XXXXXXXXXX,n,Set(CALLERID(number)=91${CALLERIDNUM})

Doug

-- Ben Franklin quote: "Those who would give up Essential Liberty to 
purchase a little Temporary Safety, deserve neither Liberty nor Safety."

> Most of our customers have generic names like "Hospital", so I
need to
> rewrite their caller ID name by looking up the number in a database on the
> Asterisk server, and rewriting the name such as "Reading
Hospital" so that
> we know who's calling.
>
> Any idea if this can be done with Asterisk, and how to do it?
I made a simple PHP AGI that takes the phone number and query a MySQL
table to find the name assigned to this number.

I still need to make a web interface to enter/modify the list but
phpMyAdmin do the job for now.

If you want it, just let me know.

I'm using my own lookup script, published on http://muware.com/asterisk

It does use various web-services in attempting to find a name -- this 
obviously only works in the US environment.  I maintain the incoming 
numbers using a very crude php-script, but phpMyAdmin works wonders.

Vincent Delporte wrote:
> Hi
>
> Most of our customers have generic names like "Hospital", so I
need to
> rewrite their caller ID name by looking up the number in a database on 
> the Asterisk server, and rewriting the name such as "Reading
Hospital"
> so that we know who's calling.
>
> Any idea if this can be done with Asterisk, and how to do it?
>
> Thank you.

Vincent Delporte wrote:
> Hi
>
> Most of our customers have generic names like "Hospital", so I
need
to
> rewrite their caller ID name by looking up the number in a database
on
> the Asterisk server, and rewriting the name such as "Reading
Hospital"
> so that we know who's calling.
>
> Any idea if this can be done with Asterisk, and how to do it?
Little C and AGI to the rescue (uses MySQL too). DB schema in the code
comments at the top.

dbc.

extensions.conf:
;; Advantech primary context (Sangoma A200D ports 1&2);;;;;;;;;;;;;;;
;; Primary telco number (905-xxx-xxxx)
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
[advan-primary]
exten => s,1,NoOp(Primary line - ${CALLERID})   ; write log entry
exten => s,n,agi,clid_override|${CALLERID(NUM)} ; CLID agi override
exten => s,n,Goto(cook-main-menu,s,1)           ; Jump to main menu
exten => s,n,Hangup                             ; end/fallthrough

clid_override.c:
/* clid_override.c
 * (c) Advantech Systems Integration, 2006
 * Author: David B. Cook, <davidc@advan.ca>, 905/xxx-xxxx
 * Initial Delivery: Version 1.0, March 1, 2006
 *
 * Application to set the CLID NAME field from a local database
 * when the field comes in empty from the carrier.
 *
 * Meant to be called from Asterisk as an AGI lookup
 * Connects to MySQL database : CLID_NAME
 *
 * Database definition
 * # Host: localhost
 * # Database: asterisk
 * # Table: 'CLID_NAME'
 * #
 * CREATE TABLE `CLID_NAME` (
 *  `CLID_NUM` varchar,
 *  `CLID_NAME` varchar,
 *  PRIMARY KEY  (`CLID_NUM`)
 * ) TYPE=InnoDB; * CLID_NAME
 *
 * Modification History:
 * XXX 00,00  dbc       - Example modification history format
 */

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#include <string.h>

#if !defined(MYSQL_VERSION_ID)||MYSQL_VERSION_ID<32224
#define mysql_field_count mysql_num_fields
#endif

#define SELECT1_QUERY "select CLID_NAME from CLID_NAME where
CLID_NUM='%s'"

int main(int argc, char **argv)
{
  MYSQL mysql,*sock;
  MYSQL_RES *res;
  MYSQL_ROW row;
  char *DBhost="put hostname here";
  char *DBuser="put MySQL username here";
  char *DBpw="put MySQL password here";
  char *DBdb="put MySQL database name here";
  char  qbuf[512];
  int   i=0;
  char  line[80];

  /* use line buffering */
  setlinebuf(stdout);
  setlinebuf(stderr);

  /* read and ignore AGI environment */
  while (1) {
    fgets(line,80,stdin);
    if (strlen(line) <= 1) break;
  }

  sprintf(qbuf,SELECT1_QUERY, argv[1]);
  /* debug: show query formulation */
  /* printf("SQL: %s\n", qbuf); */

  /* Initialize and connect to the server */
  mysql_init(&mysql);
  if (!(sock mysql_real_connect(&mysql,DBhost,DBuser,DBpw,DBdb,0,NULL,0)))
  {
    fprintf(stderr,"Couldn't connect to
engine!\n%s\n\n",mysql_error(&mysql));
    perror("");
    exit(1);
  }

  /* Perform query to determine if a row exists in the database for the
   * CLID in question.
   */
  if(mysql_query(sock,qbuf))
  {
    fprintf(stderr,"Query 1 failed (%s)\n",mysql_error(sock));
    exit(1);
  }

  /* No results - fatal error */
  if (!(res=mysql_store_result(sock)))
  {
    fprintf(stderr,"Couldn't get result from query failed\n",
    mysql_error(sock));
    exit(1);
  }

  if(mysql_num_rows(res)>=1) {
    /* CLID is PK so should only be 1 row, but I'm going to        */
    /* say >= just so it won't break if no PK and multiple hits.   */
    /* If so, will just re-set CLID again but won't break Asterisk */
    while(row=mysql_fetch_row(res)) {

      printf( "Set VARIABLE CALLERID(name) \"%s\" \n",
row[0]);

      /* send the output back to Asterisk */
      fgets(line,80,stdin);
      fputs(line,stderr);
    }
   }
  /* Clean up memory tables/free resources */
  mysql_free_result(res);

  /* Terminate the database connection */
  mysql_close(sock);
  exit(0);
  return 0;                           /* Keep some compilers happy */
}

At 22:07 22/11/2006 -0700, Marco Mouta wrote:
>You can do it using AstDB, just load the database with callerid names and 
>numbers and then include a lookup on database in all incoming calls, so 
>you can override whatever you wanted:)
Thanks everyone. Indeed, it seems like using the embedded BerkeleyDB-based 
AstDB is good enough for what I'm trying to do. However, asterisk barfs on 
the following script that I used to import data:

#/bin/bash
asterisk -rx "database put cidname 1234567 'Me - cellular'"
asterisk -rx "database put cidname 1234567 'Me - home'"
etc.

Any idea why?

Thanks.

At 12:00 25/11/2006 -0700, "Tom Lynn" <tom@tomlynn.com>
wrote:
>By inverting the relationship, I found it easier to focus on the source of 
>the call and the treatments I want to apply.  I can also wipe out entries 
>by family name and remove all attributes in one operation using database 
>deltree.
Interesting. I'll give it a shot tomorrow at the office.

Anyhow, at this point, I could successfully import all the name + number 
records, and must find solutions for the following problems:
- web interface to add/modify/remove records
- find out if LookupCID is able to match prefixes with a record (some of 
customers have DID, so I'd like to just use 123-45?? to match those 
incoming calls to "Such and such customer" instead of adding
individual
records 123-4501, 123-4502, etc.)

Thanks.

At 12:00 25/11/2006 -0700, "Tom Lynn" <tom@tomlynn.com> wrote:
 > I like a challenge.  I'll let you know if I come up with anything.

My eternal gratitude if you find something :-) And don't forget to update 
the VoIP wiki so others can benefit too.

At 12:00 25/11/2006 -0700, Anselm Martin Hoffmeister 
<anselm@hoffmeister-online.de> wrote:
>Do try
>asterisk -rx "database put cidname 12345676 \"Me -
cellular\""
>or
>asterisk -rx 'database put cidname 3871263 "Me - home"'
>These quotations seem to work.
Yup, I should have tried before posting. Thanks.

At 12:00 25/11/2006 -0700,  "Michelle Dupuis" <support@ocg.ca>
wrote:
>Try using smartCID (www.generationd.com).  You'll get the benefit of 
>ranges of numbers mapping to single ID's (good for corporate blocks), 
>action field for blocking/accepting calls, etc).
Neat, although 411.com won't do as we're not located in the US:
"smartCID -
A php script to replace callerid information with lookup from a local mysql 
table, and if that fails t"hen reverse phone number lookup from 411.com. 
This script links to Asterisk PBX.
http://www.ocg.ca/clientfiles/gss/downloads.htm

Thanks.

At 10:34 28/11/2006 -0700, Pavel Jezek <pavel.jezek@i.cz>
wrote:
>I have done simple ael2 script, tak doing lookup in asterisk database 
>like: find full numer, if cidname isn't found, substract one digit from 
>right  and try again, and so on....
Thanks. If LookupCIDname doesn't come with its own feature, I'll add it
to
the script.