List of All Devices and Associated Phone Number

Version 1
    This document was generated from CDN thread

    Created by: Shivinder Singh on 16-10-2012 04:32:43 PM
    Which request should I use in AXL 8.0 to obtain a list of all ip phone devices and the associated primary phone number?
    something like
    device-name1, 4-digit-ext1
    device-name2, 4-digit-ext2

    Subject: RE: List of All Devices and Associated Phone Number
    Replied by: David Staudt on 16-10-2012 05:14:45 PM
    With the regular AXL requests, you can use the listPhone request with a wildcard for the name, then perform getPhone for each device in the list.  This could potentially take many requests (N+1).
    Using executeSQLQuery you can try to get a SQL join with all the data at once, the relevant tables are 'device', 'numplan' and 'devicenumplanmap'.  Something like:
     
    select device.name, numplan.dnorpattern from device, numplan, devicenumplanmap where device.pkid=devicenumplanmap.fkdevice and device.fknumplan=numplan.pkid
    (not tested)
    You may need to add some additional restrictions, e.g. to make sure you are retrieving only phone devices types.
    In addition, the performance impact of executing and/or the size of the response data may be very large, depending on how many devices/lines are present on the system.  AXL will throttle/reject queries which are too large, in which case you may need to break up the request into subsets somehow (e.g. 100 devices at a time.)  Monitoring performance impact on UCM is also recommended.
    Please see the AXL Developer Guide for more details on throttling, and the Data Dictionary for details on UCM tables/fields/relationships

    Subject: RE: New Message from David Staudt in Administration XML (AXL) - Administrat
    Replied by: Shivinder Singh on 17-10-2012 10:15:03 AM
    David, thanks for detailed response. executeSQLQuery approach seems much better. I tried and I got SoapException (using C#):
    Unhandled Exception: System.Web.Services.Protocols.SoapException: Column (fknumplan) not found in any table in the query (or SLV is undefined).

    We’re on 8.0; I checked the data dictionary and there is no column fknumplan in device table. Any suggestions?

    Subject: RE: New Message from Shivinder Singh in Administration XML (AXL) - Administ
    Replied by: Dennis Heim on 17-10-2012 10:35:03 AM
    Just taking a guess at what you are trying to do:

    The device and numplan tables are related via  the devicenumplanmap table. The relationship is usually something like this:

    Device.pkid = devicenumplanmap.fkdevice
    Numplan.pkid = devicenumplanmap.fknumplan

    Dennis Heim | Sr. UC Engineer
    World Wide Technology | 314.212.1814 | dennis.heim@wwt.com<mailto:dennis.heim@wwt.com>
    “Creating Impact, Ignition & Scalability”

    From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
    Sent: Wednesday, October 17, 2012 11:15 AM
    To: cdicuser@developer.cisco.com
    Subject: New Message from Shivinder Singh in Administration XML (AXL) - Administration XML Questions: RE: New Message from David Staudt in Administration XML (AXL) - Administrat

    Shivinder Singh has created a new message in the forum "Administration XML Questions": -------------------------------------------------------------- David, thanks for detailed response. executeSQLQuery approach seems much better. I tried and I got SoapException (using C#):
    Unhandled Exception: System.Web.Services.Protocols.SoapException: Column (fknumplan) not found in any table in the query (or SLV is undefined).

    We’re on 8.0; I checked the data dictionary and there is no column fknumplan in device table. Any suggestions?
    --
    To respond to this post, please click the following link: http://developer.cisco.com/web/axl/forums/-/message_boards/view_message/7746348 or simply reply to this email.

    Subject: RE: New Message from Dennis Heim in Administration XML (AXL) - Administrati
    Replied by: Shivinder Singh on 17-10-2012 01:34:03 PM
    This is awesome.
    I have one more related question. From the above query, I am getting CCX (Contact Center Xpress) numbers as well. Is there any join/relation I can use to identify the primary line of a CCX agent number? Say for instance John Doe’s primary line is 8888, but he is also assigned an agent number of 9999. How do I figure out both numbers of John Doe in one query?

    Subject: RE: New Message from Sascha Monteiro in Administration XML (AXL) - Administ
    Replied by: Shivinder Singh on 17-10-2012 02:33:03 PM
    Sascha I apologize I’m very new to CUCM. Do you mean there will be one row per number (one row for primary line and one row for CCX number) in the enduser table?

    Subject: RE: New Message from Shivinder Singh in Administration XML (AXL) - Administ
    Replied by: Shivinder Singh on 17-10-2012 05:03:03 PM
    Roadblock! It seems, like David mentioned earlier, the system is somehow limiting the number of rows returned. I’ve tried issuing multiple queries to get ranges of phone numbers in a for loop, for e.g.,
    SQL…”where enduser.phonenumber between 2000 and 3000”

    But this does not give expected results. I only get like 60 rows for this range. I’m expecting around 150 rows.

    I then tried getting ranges in even smaller increments of 50 like this ”where enduser.phonenumber between 2150 and 2200” and then I get even lesser number, like 8 rows

    We have a total of around 550 devices, and the data I’m querying should be extremely small and should ideally be returned in a single query. What else can I try?

    Subject: Automatic reply: New Message from Shivinder Singh in Administration XML (AX
    Replied by: WILL DELAUGHTER on 17-10-2012 10:16:34 AM
    I am out of the office until 9/10/2012.  In my absence, please contact James Hopkins at (908)234-3646.

    Subject: RE: New Message from Dennis Heim in Administration XML (AXL) - Administrati
    Replied by: Martin Sloan on 17-10-2012 02:02:51 PM
    If you know where to expect the lines to appear on the device you could use the numplanindex field in devicenumplanmap 'WHERE devicenumplanmap.numplanindex = '1''.  But you might want to include the typepatternusage table as well to make sure you're returning results for numbers assigned to devices and not translation patterns or such.  You could add 'numplan.tkpatternusage = '2'' for that. 
    HTH

    Subject: RE: New Message from Dennis Heim in Administration XML (AXL) - Administrati
    Replied by: David Staudt on 17-10-2012 02:12:41 PM
    UCM is not aware of UCCX internal configuration, so you would have to somehow query UCCX to find out that information.  I'm not aware of any facility/API/interface for doing that, however.  You might try inquirying in the UCCX API forums.

    Subject: RE: New Message from Dennis Heim in Administration XML (AXL) - Administrati
    Replied by: Sascha Monteiro on 17-10-2012 02:23:02 PM
    You could query the user for it's IPCC extension, this is stored in endusernumplanmap (tkdnusage = '2')

    Subject: RE: New Message from Dennis Heim in Administration XML (AXL) - Administrati
    Replied by: Sascha Monteiro on 17-10-2012 02:40:07 PM
    yeah, these are the primary extension and iopcc extension that you see on the enduser page ;
     


    run sql select E.userid,N.dnorpattern,NM.tkdnusage from numplan N join endusernumplanmap NM on (NM.fknumplan = N.pkid) join enduser E on (E.pkid = NM.fkenduser) order by E.userid,NM.tkdnusage
    userid     dnorpattern tkdnusage 
    ========== =========== ========= 
    agent21    8021        1         
    agent21    8021        2         
    agent22    8022        1         
    agent22    8022        2   

     
    I only have 1 line on the phone, but if you had a 2nd line for UCCX, you would see that extn in the row with tkdnusage = 2

    Subject: RE: New Message from Shivinder Singh in Administration XML (AXL) - Administ
    Replied by: Shivinder Singh on 17-10-2012 02:40:33 PM
    Got it! This query does the job:

    select device.name, numplan.dnorpattern,numplan.alertingname, enduser.telephonenumber
                        from device, numplan, devicenumplanmap, enduser, endusernumplanmap where
                        Device.pkid = devicenumplanmap.fkdevice and
                        Numplan.pkid = devicenumplanmap.fknumplan and
                        endusernumplanmap.fkenduser = enduser.pkid and
                        endusernumplanmap.fknumplan = devicenumplanmap.fknumplan and
                        tkdnusage='2' and
                        device.name='SEPACA016FC9125'

    And of course you could remove the last line to query ALL devices. Thanks !

    Subject: RE: New Message from Shivinder Singh in Administration XML (AXL) - Administ
    Replied by: Sascha Monteiro on 17-10-2012 05:16:38 PM
    do all users have the telephonenumber set? this is just a field and has no link to the phone or primary extension..

    Subject: RE: New Message from Shivinder Singh in Administration XML (AXL) - Administ
    Replied by: David Staudt on 17-10-2012 05:24:54 PM
    If you're referring to the User 'Telephone Number' field, then that's true: it's basically an arbitrary field that may or not be present, or may or not accurately reflect actual user->line relationships in UCM.  The field is typically entered manually by the admin or auto-synced from an LDAP system.

    Subject: RE: New Message from David Staudt in Administration XML (AXL) - Administrat
    Replied by: Shivinder Singh on 18-10-2012 04:54:56 PM
    Looks like Informix does not like this syntax:

    with tsource as (select count(*) from enduser) select * from tsource

    CLI says syntax error.

    But this syntax works fine on Oracle. I need this syntax to get the desired output in original post. Is there any equivalent in Informix?

    Subject: RE: New Message from Shivinder Singh in Administration XML (AXL) - Administ
    Replied by: Shivinder Singh on 18-10-2012 05:30:04 PM
    I found this equivalent syntax in Informix, but still get syntax error when run via CLI or AXL’s executeSQLQuery:

    select count(*) from enduser into temp temp_endusercount select * from temp_endusercount

    http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.admin.doc/admin365.htm


    Regards,

    Shivinder Singh * Programmer III, Computer Services * Lamar Institute of Technology * Beaumont, Texas * Phone: 409 839 2097 * ssingh@lit.edu<mailto:ssingh@lit.edu> * http://www.lit.edu<http://www.lit.edu/>


    From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
    Sent: Thursday, October 18, 2012 4:55 PM
    To: cdicuser@developer.cisco.com
    Subject: New Message from Shivinder Singh in Administration XML (AXL) - Administration XML Questions: RE: New Message from David Staudt in Administration XML (AXL) - Administrat

    Shivinder Singh has created a new message in the forum "Administration XML Questions": -------------------------------------------------------------- Looks like Informix does not like this syntax:

    with tsource as (select count(*) from enduser) select * from tsource

    CLI says syntax error.

    But this syntax works fine on Oracle. I need this syntax to get the desired output in original post. Is there any equivalent in Informix?
    --
    To respond to this post, please click the following link: http://developer.cisco.com/web/axl/forums/-/message_boards/view_message/7823743 or simply reply to this email.

    Subject: RE: New Message from David Staudt in Administration XML (AXL) - Administrat
    Replied by: Shivinder Singh on 21-10-2012 07:49:08 PM
    Just in case anyone comes across this post - Found a better way to get all primary numbers and agent numbers. Device, numplan, and devicenumplanmap are the 3 tables I found from data dictionary. Here is the query:

    select distinct
                    dev.name,
                    n.alertingname,
                    n.dnorpattern,
                    from numplan n,
                    devicenumplanmap d,
                    device dev
                    where n.pkid=d.fknumplan
                    and d.fkdevice=dev.pkid
                    and dev.name like 'SEP%'
                    order by n.alertingname

    Not sure why some of our devices had userid of the person in the name column of device table. So I had to add
    where dev.name like 'SEP%'