SQL help to get the mac address and phone number.

[r.boloor.rao]

I  am trying to write sql command to get the list of MAC addresses and Phone numbers associated with a device pool. I can do this from CCM admin pages but when there are multiple entries I hve to search individually.

Is there any sql query to run using <run sql select >on ssh  cli ?. I am  using following query

:run sql select d.name , e.name from device d, devicepool e where d.fkdevicepool=e.pkid where d.name like "SEP%" I get the list of device name and device pool

This gives me devicename,devicepool . I am need to link Phone numbers to these .

Which table has phone numbers?. devicenumplan ?. how do I link device table and devicenumplan?

[Gordon Ross · Accepted solution]

run sql SELECT device.name, devicepool.name, dnorpattern FROM device, devicepool, devicenumplanmap, numplan WHERE device.fkdevicepool=devicepool.pkid AND numplan.pkid=devicenumplanmap.fknumplan AND device.pkid=devicenumplanmap.fkdevice AND  devicepool.name LIKE "FOO"

(Note: SEP% will only get you VoIP handsets. ATAs, VG224s, etc won't be listed)

GTG