This document was generated from CDN thread
Created by: Wes Schochet on 15-02-2010 06:24:15 PM
Hey All-
I just wrote this SQL query and thought I'd share it. It's a nice simple list of phones for my CUCM along with their primary DNs. Give it a whirl!
select n.dnorpattern dn, rp.name partition, d.name, d.description
from ((((device d inner join devicenumplanmap m on d.pkid = m.fkdevice)
inner join numplan n on n.pkid = m.fknumplan)
inner join NumPlan p on m.fknumplan = p.pkid)
inner join routePartition rp on p.fkRoutePartition = rp.pkid)
where d.name like 'SEP%' and
numplanindex = 1
order by n.dnorpattern
And, no, you cannot determine from a DB query the IP address of a phone (just thought I'd head that one off)!
It'd be nice to start a query archive somewhere in the Wiki for these types of things!
Subject: RE: Device (phone) list from SQL
Replied by: David Staudt on 15-02-2010 07:36:46 PM
Two suggestions:
- I understand the UCM database (Informix) works better using syntax like '... where devicenumplanmap.fkdevice=device.pkid ...' rather than the ANSI 'inner join' syntax. You might rework your query in this form to minimize performance impact.
- Generally you'll want to avoid asking the UCM database to perform format/fixing-up operations on the data - like sorting on large tables - to avoid the CPU cycles/RAM use and potential impact on call processing.
If you want to update your query and validate it (let me know the UCM versions tested), I'll see if I can't get a section in the Wiki added...appreciate that
Subject: RE: Device (phone) list from SQL
Replied by: Marc Van-De-Cappelle on 02-08-2010 10:37:49 AM
Wes, awesome script mate, many thanks. I would like to add the IP and model of all phones, but i tried a few stabs at d.ip, d.model and then d.ipaddress but with no luck. Any ideas how i can find out how to do this?
Created by: Wes Schochet on 15-02-2010 06:24:15 PM
Hey All-
I just wrote this SQL query and thought I'd share it. It's a nice simple list of phones for my CUCM along with their primary DNs. Give it a whirl!
select n.dnorpattern dn, rp.name partition, d.name, d.description
from ((((device d inner join devicenumplanmap m on d.pkid = m.fkdevice)
inner join numplan n on n.pkid = m.fknumplan)
inner join NumPlan p on m.fknumplan = p.pkid)
inner join routePartition rp on p.fkRoutePartition = rp.pkid)
where d.name like 'SEP%' and
numplanindex = 1
order by n.dnorpattern
And, no, you cannot determine from a DB query the IP address of a phone (just thought I'd head that one off)!
It'd be nice to start a query archive somewhere in the Wiki for these types of things!
Subject: RE: Device (phone) list from SQL
Replied by: David Staudt on 15-02-2010 07:36:46 PM
Two suggestions:
- I understand the UCM database (Informix) works better using syntax like '... where devicenumplanmap.fkdevice=device.pkid ...' rather than the ANSI 'inner join' syntax. You might rework your query in this form to minimize performance impact.
- Generally you'll want to avoid asking the UCM database to perform format/fixing-up operations on the data - like sorting on large tables - to avoid the CPU cycles/RAM use and potential impact on call processing.
If you want to update your query and validate it (let me know the UCM versions tested), I'll see if I can't get a section in the Wiki added...appreciate that
Subject: RE: Device (phone) list from SQL
Replied by: Marc Van-De-Cappelle on 02-08-2010 10:37:49 AM
Hey All-
I just wrote this SQL query and thought I'd share it. It's a nice simple list of phones for my CUCM along with their primary DNs. Give it a whirl!
select n.dnorpattern dn, rp.name partition, d.name, d.description
from ((((device d inner join devicenumplanmap m on d.pkid = m.fkdevice)
inner join numplan n on n.pkid = m.fknumplan)
inner join NumPlan p on m.fknumplan = p.pkid)
inner join routePartition rp on p.fkRoutePartition = rp.pkid)
where d.name like 'SEP%' and
numplanindex = 1
order by n.dnorpattern
And, no, you cannot determine from a DB query the IP address of a phone (just thought I'd head that one off)!
It'd be nice to start a query archive somewhere in the Wiki for these types of things!
Wes, awesome script mate, many thanks. I would like to add the IP and model of all phones, but i tried a few stabs at d.ip, d.model and then d.ipaddress but with no luck. Any ideas how i can find out how to do this?