Get a list of lines and descriptions

Version 1
    This document was generated from CDN thread

    Created by: Trevor Petersen on 17-08-2009 08:42:22 PM
    I'm trying to find a list of lines and their descriptions.  Has anyone found a way to do this?
     
     

    Subject: RE: Get a list of lines and descriptions
    Replied by: David Staudt on 17-08-2009 10:01:17 PM
    If you are looking for lines on a particular device, then normally one would use <getPhone> - which returns a <lines> list with dirN UUIDs - followed by a separate <getLine> for each UUID.
     
    If you are looking for just a bulk listing of all lines (or more efficiently pulling info for individual devices,) you may want to look into executing a SQL statement against the UCM database via <executeSQLQuery>.  The Data Dictionary document lists all tables and relationships, though the descriptions are a bit light and/or arcanse.  The base table is 'numplan'.
     
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/7.0">
       <soapenv:Header/>
       <soapenv:Body>
          <ns:executeSQLQuery sequence="1">
             <sql>select numplan.dnorpattern, numplan.description, typepatternusage.name as type from numplan, typepatternusage where numplan.tkpatternusage=typepatternusage.enum</sql>
          </ns:executeSQLQuery>
       </soapenv:Body>
    </soapenv:Envelope>

    Subject: RE: Get a list of lines and descriptions
    Replied by: Trevor Petersen on 18-08-2009 03:38:41 PM
    Thanks, that works for getting the line numbers, but I'm not getting the descriptions - like the names associated with those lines.

    Subject: RE: Get a list of lines and descriptions
    Replied by: David Staudt on 18-08-2009 04:08:04 PM
    Can you elaborate on what info you're looking for?  By 'names' is this names of users?  Note with the shared line feature, a line can be associated to multiple phones, which in turn are associated to multiple users.

    Subject: RE: Get a list of lines and descriptions
    Replied by: Trevor Petersen on 18-08-2009 04:44:53 PM
    Thanks for pointing out the shared line feature.  I guess I'm looking for a list of DN's with their alerting names.  Using your example, I think I've found what I'm looking for  -
     

    select dev.display, n.dnorpattern from DeviceNumplanMap dev, numplan n where dev.fknumplan = n.pkid
     
    Wouldn't this return a list of DN's with the names associated with them?

    Subject: RE: Get a list of lines and descriptions
    Replied by: David Staudt on 18-08-2009 05:36:29 PM
    The Alerting Name/ASCII Alerting Name fields are in the numplan table:
     
             <sql>select numplan.dnorpattern, numplan.alertingname, numplan.alertingnameascii, typepatternusage.name as type from numplan, typepatternusage where numplan.tkpatternusage=typepatternusage.enum</sql>

    Subject: RE: Get a list of lines and descriptions
    Replied by: Trevor Petersen on 18-08-2009 07:00:54 PM
    That's exactly what I'm looking for, thank you!!!  Here's what I ended up with in c#
     
     
     
     
     
     
     
    <font size="2">
     
    </font>
     
    ExecuteSQLQueryReq myExecuteSQL = new ExecuteSQLQueryReq();<font size="2">
     
    </font>
     
    <font size="2">
    myExecuteSQL.sql =
    </font>
     
    "select numplan.dnorpattern, numplan.alertingname, numplan.alertingnameascii, typepatternusage.name as type from numplan, typepatternusage where numplan.tkpatternusage=typepatternusage.enum";
    <font size="2">
     
    </font>
     
    ExecuteSQLQueryRes myExecSqlResults = new ExecuteSQLQueryRes();
    myExecSqlResults = myAxlTest.executeSQLQuery(myExecuteSQL);
    <font size="2">
     
    </font>
     
    foreach (System.Xml.XmlNode[] o in mailto:myExecSqlResults.@return)
    { lblList.Text += o[0].InnerText + " " +o[1].InnerText + " \n"; }