Associated Devices via getLine

Version 1
    This document was generated from CDN thread

    Created by: Michael Schmidt on 18-04-2012 09:58:12 AM
    Hi,
     
    is it possible to read out the "Associated Devices" for a directory number via "getLine"?
     
    With this "//getLineResponse/return/directoryNumber/usage" it`s not working.
     
    BR
    Michael

    Subject: Re: New Message from Michael Schmidt in Administration XML (AXL) - Administ
    Replied by: Wes Schochet on 18-04-2012 10:49:56 AM
    It's a good SQL query:

    select name, d.description, numplanindex, n.dnorpattern from device d, devicenumplanmap m, numplan n where d.pkid = m.fkdevice and n.pkid = m.fknumplan and n.dnorpattern='32081'

    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
       <SOAP-ENV:Body>
          <executeSQLQuery sequence="1224262625701">
            <sql>select name, d.description, numplanindex, n.dnorpattern from device d, devicenumplanmap m, numplan n where d.pkid = m.fkdevice and n.pkid = m.fknumplan and n.dnorpattern='32081'
    </sql>
          </executeSQLQuery>
       </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

    <SOAP-ENV:Envelope SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
       <SOAP-ENV:Header />

       <SOAP-ENV:Body>
          <axl:executeSQLQueryResponse sequence="1224262625701" xmlns:axl="http://www.cisco.com/AXL/API/6.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
             <return>
                <row>
                   <name>SEP000000032081</name>
                   <description>Wes IP Comm</description>
                   <numplanindex>4</numplanindex>
                   <dnorpattern>32081</dnorpattern>
                </row>
                <row>
                   <name>SEP000F34117ECF</name>
                   <description>Office Test Phone (was 000F34117ECF or 0015F9ED4373)</description>
                   <numplanindex>1</numplanindex>
                   <dnorpattern>32081</dnorpattern>
                </row>
                <row>
                   <name>SEP1CDF0F2006AE</name>
                  <description>Wes Schochet Yellow</description>
                   <numplanindex>1</numplanindex>
                   <dnorpattern>32081</dnorpattern>
                </row>
               <row>
                   <name>Wes_Schochet</name>
                   <description />
                   <numplanindex>1</numplanindex>
                   <dnorpattern>32081</dnorpattern>
                </row>
                <row>
                   <name>SEP001EF7C42882</name>
                   <description>Home 7941</description>
                   <numplanindex>2</numplanindex>
                   <dnorpattern>32081</dnorpattern>
                </row>
             </return>
          </axl:executeSQLQueryResponse>
       </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>





    >>> On 4/18/2012 at 8:58 AM, Cisco Developer Community Forums <cdicuser@developer.cisco.com> wrote:

    Michael Schmidt has created a new message in the forum "Administration XML Questions":

    --------------------------------------------------------------
    Hi,

    is it possible to read out the "Associated Devices" for a directory number via "getLine"?

    With this "//getLineResponse/return/directoryNumber/usage" it`s not working.

    BR
    Michael
    --
    To respond to this post, please click the following link:

    <http://developer.cisco.com/web/axl/forums/-/message_boards/view_message/5481916>

    or simply reply to this email.

    Confidentiality Statement:
    This email/fax, including attachments, may include confidential
    and/or proprietary information and may be used only by the
    person or entity to which it is addressed. If the reader of
    this email/fax is not the intended recipient or his or her
    agent, the reader is hereby notified that any dissemination,
    distribution or copying of this email/fax is prohibited. If you
    have received this email/fax in error, please notify the sender
    by replying to this message and deleting this email or
    destroying this facsimile immediately.

    Subject: RE: Associated Devices via getLine
    Replied by: Michael Schmidt on 18-04-2012 12:09:31 PM
    Hi Wes,

    many thanks for your quick answer. We are using CUCM in Version 8.6.2

    Is in version 8.6.2 also a SQL database at CUCM where I can use such SQL statements?

    BR
    Michael

    Subject: RE: Associated Devices via getLine
    Replied by: Michael Schmidt on 18-04-2012 03:56:57 PM
    Hi Wes,

    thank you very much I just tried this SQL query and it`s working fine.

    Some questions about this:

    1. Can I run scheduled SQL querys at CUCM?
    2. Where can I find some other SQL examples or the necessary commands for the SQL?
    3. Can I write the SQL output automatically into a file at the file-share?

    BR
    Michael

    Subject: RE: Associated Devices via getLine
    Replied by: David Staudt on 20-04-2012 04:03:27 PM
    You can submit SQL queries to UCM via the AXL <executeSQLQuery> request, which means you can script this on a schedule, and store the output however you desire.

    Subject: RE: Associated Devices via getLine
    Replied by: David Staudt on 20-04-2012 04:16:26 PM
    There is not an onboard UC Manager/Cisco provided service - this would be a custom script - using the AXL API - that you write and host on an app server.  It could be as simple as a PERL or Python script running as a cron job on a linux VM...

    Subject: RE: Associated Devices via getLine
    Replied by: Michael Schmidt on 20-04-2012 04:25:59 PM
    OK thanks I will try this next week when I`m back in the office

    Subject: RE: Associated Devices via getLine
    Replied by: Michael Schmidt on 20-04-2012 04:33:37 PM
    Hi David,

    sorry once again. Do you maybe have such Perl script to execute this SQL query scheduled?

    BR
    Michael

    Subject: RE: Associated Devices via getLine
    Replied by: David Staudt on 20-04-2012 04:39:45 PM
    I don't, but there is a java-bases sample in the AXL SQL Toolkit that does this.  The Toolkit is downloaded from the UCM server, via the admin UI under Application/Plugins.

    Subject: RE: Associated Devices via getLine
    Replied by: Michael Schmidt on 20-04-2012 04:13:07 PM
    Hi David,

    many thanks.

    Where can I schedule the "AXL <executeSQLQuery> request"? At CUCM webpage at buld administrator scheduler?

    BR
    Michael