Device (phone) list from SQL

Version 1
    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
    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?