[cisco-voip] SQL Query for device and Lines

[Robert Singleton]

On Mon, 2007-09-03 at 20:41 +0200, Reto Gassmann wrote:
> Hello all
> 
> I try to build a query in access to get the entire device and line information
> in one list. I get the Device table and the NumPlan table from the publisher
> database. Now I have the entire device and the lines information. But I can't
> find a way to link them together.
> Is there a third table involve??

DeviceNumPlanMap might be the link you are looking for...

I have not tried to get *all* info for a given device yet, but I have a
utility that gets some info from DeviceNumPlanMap, NumPlan and Device
using a query with inner joins. As I am not an SQL expert, I had to
experiment with the structure and order of the joins until they worked.

---------------
SELECT DNOrPattern,Name,Display,NumPlanIndex,Label,tkModel from
DeviceNumPlanMap

INNER JOIN NumPlan on DeviceNumPlanMap.fkNumPlan=NumPlan.pkid
INNER JOIN Device on DeviceNumPlanMap.fkDevice=Device.pkid 

WHERE (DNOrPattern like '20$1[57]%' or DNOrPattern like '2___$1%' or
DNOrPattern like '$1%') or Display like '%$1%' or Name like '%$devname%'
order by NumPlanIndex,DNOrPattern"
----------------
Notice that each of the joins are in reference to a field in
DeviceNumPlanMap. In an inner join (and perhaps with all joins), it
seems that some table has to be the "hub" of the data retrieved. Again,
this is observation at work, not training :)

As for the utility, I use this to find the details for a phone using any
part of the name, DN or device name. Typically, I do 'ext Joe' to get
all the phones with a 'joe' in the name. I may also do 'ext 2065' to
display all the DNs at my branch 65.

Sometimes, I'll be researching CDRs and will see a shared DN was
involved in a call on a particular device and I use 'ext devicename' to
determine who's telephone was used. It's distressing how often this is
used to see who picked up and hung up a phone to merely stop it from
ringing.

Further processing on this output dips into TypeModel to give a useful
name to tkModel. I gave up on trying to do this in one query and simply
output the model name with a separate query for each output line. This
is not ideal by any stretch of the term, but it is workable.

Typical output is something like this:

   2000785 SEP000E833CB287:1          Robert Singleton  lbl=[] Cisco 7960
   2000785 SEP001B0C18EE0E:1          Robert Singleton  lbl=[] Cisco 7961
   2000785 SEP000E841AED98:10         Robert Singleton  lbl=[Bobert] Cisco 7960

I digress...

Robert