[cisco-voip] Updating e164Mask (was: Lines in UpdatePhone using AXL)

[Robert Singleton]

On Thu, 2006-10-26 at 08:25 +0200, Dennis Ploeger wrote:
> Hi!
> 
> As no one seems to know more about altering the e164Mask using axl, I'm
> asking for another solution.
> 
> Again, my task is to change all e164Masks of our device pool. This
> involves reading the e164mask, removing the first zero from it and
> writing it back.

I wrote a small script to change the E164Mask. While I wrote it as for
use on a Linux command line, the SQL query is pretty much universal and
could be adapted as needed and called from another script. You can
probably do it from Enterprise Manager, though scripting all those mouse
clicks can be problematic.

In my CallManager, 4.0(2a)sr2, these change takes place on the DN
immediately. Your results may vary.

I use isql to access the server. Setting up isql on a Linux workstation
is beyond the scope of this note, but I'd be glad to help you with that
off the list. For now, know that for scripting purposes, I am echoing
commands to isql's command line interface. The queries contained within
" " along with passed variables are the meat of script.

The script, which I call 'chid', is called on the command line in the
form of:

chid [DN to change] [new E164Mask]

This particular tool changes only the mask on a DN that is the prime
line on an IP phone, which is what I needed.

To change the mask on a given DN, we need to look up the DN's associated
entry in the NumPlan table and save the associated reference to the
DeviceNumPlanMap table, then change *that* table entry.

The first query takes the first parameter (the DN, $1 below), determines
the specific DeviceNumPlanMap table entry that we want to change and
assigns it to an environment variable 'numpkid'. The query pulls the
pkid field from the record that matches both the DN and NumPlanIndex =
'1', which specifies the first line on the device. Other lines on the
device can be specified by changing to NumPlanIndex ='2', etc.

The second line changes the E164Mask field of the record specified by
'numpkid' in the DeviceNumPlanMap table to the value we provide ($2
below) on the command line.

Please be warned. This script does no value checking, no validation of
input whatsoever. You must be completely aware and responsible for
whatever values to provide to it, especially wildcards. If you put '%'
as your DN, it WILL change *all* DNs in the database to the given mask,
especially if you remove the "NumPlanIndex" clause. Short of restoring a
back up of the database made beforehand, there is no 'undo' for such
changes.

For your needs, you could insert another query before the UPDATE to read
the current value of E164Mask, modify it and store it, then the UPDATE
query could write that value instead of a command line parameter value.
Chances are that this function could be done with one UPDATE query, but
I'd need to read up on that.

---------------
#!/bin/bash

numpkid="$(echo "select DeviceNumPlanMap.pkid from DeviceNumPlanMap
inner join NumPlan on DeviceNumPlanMap.fkNumPlan=NumPlan.pkid where
DNOrPattern like '$1' and NumPlanIndex = '1'" | isql -b -d'|' CCMserver
sa cisco)"

echo "UPDATE DeviceNumPlanMap SET E164Mask = '$2' WHERE
DeviceNumPlanMap.pkid='$numpkid'" | isql -b CCMserver sa cisco
----------------

This script grew out of a series of command line queries I used to
correct typos and omissions that were committed during the rollout of
our systems. My (often temporary) assistants would add telephones and
frequently omit the mask, especially if the line was shared. Even I,
perfect though I might otherwise be, made the occasional error in
typing. By the time I was getting a little comfortable with directly
modifying the database, I had several hundred DNs with a missing or
erroneous E164Mask.

Now the most common use for this command is to alter the mask on my test
telephone for test calling purposes.

Robert