10 digit phone number using AXL

Version 1
    This document was generated from CDN thread

    Created by: Shivinder Singh on 24-01-2013 03:00:40 PM
    What SQL query should I use to get the full 10 digit phone number (3 digit area code, 3 digits, last 4 digits) of all people in the ENDUSERS backend table? I will use this SQL in executeSQL method in the AXL API.

    Subject: RE: 10 digit phone number using AXL
    Replied by: Shivinder Singh on 04-02-2013 04:56:45 PM
    Hello? Can anyone please help me on this?

    Subject: RE: 10 digit phone number using AXL
    Replied by: David Staudt on 04-02-2013 05:17:24 PM
    Many factors can influence the system's dial-plan, and various features like configured directory numbers, direct inward dial numbers, internal/external phone number masks, etc. can have complex/flexible effects to how a user's 'phone number' might be calculated/presented in various contexts.  For an example an 'area code' (along with 10 digit dialing, a concept significant only within the North American numbering plan - among multiple supported by UCM), could be assigned to a dialed number at the line appearance, translation pattern, route pattern, gateway, dynamically via an application, etc.  To definitively parse all of the relevant UCM config options to calculate a presented phone number (for all contexts, e.g. internal, external, varied by service provider, etc.) might be pretty involved.
    That being said, if you just want to get at the number that is populated in the user directory, you can do something like:
    select userid,firstname,lastname,telephonenumber from enduser
    But note that this value is optional, is populated either manually by the admin or by some kind of external tool or sync operation (e.g. LDAP sync) and may not exist or may not reflect the 'actual' dialable number configured in the UCM dial plan.  Also you will almost certainly want to take care in executing this request on a live system with a large number of users:  be aware of AXL data throttling limits, consider using SQL skip/first commands to retrieve limited chunks of data, and test the performance impact of your query against UCM databases of various size, on systems under load.

    Subject: RE: 10 digit phone number using AXL
    Replied by: Michael Piskun on 04-02-2013 05:18:07 PM
    Hi Shavinder,
    The string below will retreive userid, firstname, lastname, mailid and telephone number.

    select userid,firstname,lastname,mailid,telephonenumber from enduser
    Unfortunately, this does not nessesarily reflect the number that is configured for the user in CallManager.


    Subject: RE: New Message from Michael Piskun in AXL Developer - Administration XML Q
    Replied by: Shivinder Singh on 05-02-2013 03:09:30 PM
    Thanks  David and Michael. Our AD sync was set to use ipPhone property in AD, which stores 4 digits. We create new sync configuration and set it to use telephoneNumber property – which has the full 10 digits – manually entered.
    I think this would be the best option after reading David’s response.

    But just in case someone comes across this and still wants to use enduser table: this is what I could come up with - that works in our case – may not necessarily work on your case!

    select distinct dmap.e164mask[1,6]||n.dnorpattern
                  from enduser euser, endusernumplanmap emap, devicenumplanmap dmap, numplan n
                  where 1=1
                  and n.pkid=dmap.fknumplan
                  and n.pkid=emap.fknumplan
                  and euser.pkid=emap.fkenduser
                  and emap.tkdnusage ='1'
                  and dmap.e164mask is not null
                  and length(dmap.e164mask)>=6