[cisco-voip] Automatically exporting just the DN and Description fields

Thu Aug 31 16:36:29 EDT 2017

For the record, the API part is not necessary, you can just run your SQL
queries on the CLI.  However, using the AXL API method to run the SQL query
for you, allows you to do so programmatically.

As for the database, here are a few key points:

   - Anything that registers is a Device and is in the device table.

   - Anything that is a pattern is in the numplan table

   - End Users are in the enduser table, while Application Users are in the
   applicationuser table

   - The database is a relational database, meaning that data from one
   table will have a relationship to data in another table

   - Some tables are joined by holding a GUID in their PKID column, which
   other tables refer to with a column name in their own table, named "fk" +
   tablename E.g., device.fkenduser = enduser.pkid

   - Some tables are joined by holding an enum (number) in their enum
   column, which other tables refer to with a column name in their own table,
   named "tk" + tablename E.g., device.tkmodel = typemodel.enum (slightly
   different that fk)

   - Some data objects are mapped together to form a new object in a third
   table named: tablename1 + tablename2 + "map"  E.g., devicenumplanmap holds
   line appearances for phones and DNs

   - To find a list of table names: select tabname from systables order by
   tabname

   - To find a list of columns in a table: select colname from syscolumns
   where tabid = (select tabid from systables where tabname = 'numplan') order
   by colname

   - Sometimes when I don't know what the name of something is in the
   database, I just access the resource in the web admin, and then look at logs
   This tip is courtesy of Wes Sisk:
   http://cisco-voip.markmail.org/thread/vtoasdkgieadmou3

   - Other times I just look at the HTML on the web page to find out the
   name of the field, which is usually the name of the column (99% of the time)

   [image: image.png]

On Thu, Aug 31, 2017 at 2:08 PM Ben Amick <bamick at humanarc.com> wrote:

> Do you have a quick reference sheet of the field names? I’d like to do
> something similar to this but also including the RNA timer/destination,
> user associations, external number mask, and the recording setting for each
> line.
>
>
>
> Really, I’d like to do a run against all my translations, phones, CTI
> route points, etc to just automagically generate all my documentation, but
> I have not nearly enough knowledge about hot to use the API or the database
> structure in general. You wouldn’t happen to have a crash course on all
> that, would you?
>
>
>
> *From:* cisco-voip [mailto:cisco-voip-bounces at puck.nether.net] *On Behalf
> Of *Brian Meade
> *Sent:* Wednesday, August 30, 2017 5:28 PM
> *To:* Tim Reimers <treimers at ashevillenc.gov>
> *Cc:* cisco-voip <cisco-voip at puck.nether.net>
> *Subject:* Re: [cisco-voip] Automatically exporting just the DN and
> Description fields
>
>
>
> Use the AXL API and the following SQL query to automate this:
>
>
>
> select dnorpattern,description from numplan where tkpatternusage='2'
>
>
>
> You can put "run sql" before the query to run via SSH to CUCM.
>
>
>
> On Wed, Aug 30, 2017 at 4:45 PM, Tim Reimers <treimers at ashevillenc.gov>
> wrote:
>
> Hi all-
>
>
>
> In CUCM 9.12
>
> I'm trying to figure out how to export just a few of the fields from the
> UCM database.
>
> What we're after is simply the DN number, and the contents of the
> Description field associated with that DN
>
>
>
> I've done a Phone export, and the resulting CSV is so huge it's unusable
> in Google Sheets.
>
> I know I can set up an Export as a repeated schedule, but I have two
> questions.
>
>
>
> - Can I somehow reduce the export query to a limited set of details about
> lines only. I don't need phone info particularly.
>
> - Is there a way to transfer the file off the UCM environment
> automatically, so that it's available to another automated process that
> will  automatically consume the resulting file and use that data? Right
> now, I have to manually download the export job file.
>
>
>
> Thanks, Tim
>
>
>
> --
>
> *Quis custodiet ipsos
> <http://cp.mcafee.com/d/1jWVIg4xEqdELTohjuod79EVdTdEEILIFzxO9EVdEEILIFzDDSjqabbXPUVVZMSyyUqejhPuZQrCO6GOIl1hdJnor6TbCOrqKMSdKndIKE9zJd7b_nVMsUCMYZRXBQSnxT8ELK6zB5dPG8FHnjlKy_OEuvkzaT0QSyrjdTWWa8VNwttYQsCXCOsVHkiPfxNyhmiTEiTw0e695po0XsG2JCWjGxbVyIBGlEaSrFeG7Q7IyVKQGmGncRAIn8lrxrW0FpKNnwqj-f0QSqehOb9I5-Aq83iTqlblbCqOmdbFEw46VRlSsHquumd47xWRoPB0yq815KAPh1cDVEw3woq8dwwq80XWmz9qsGMd43ILfgEr7fCNxQY>**opus
> reticulatum*
>
> Tim Reimers
>
> Network Administrator
>
> I.T Services
>
> City of Asheville
>
> treimers at ashevillenc.gov
>
> (desk) 828-259-5512 <(828)%20259-5512>
>
> (cell)   828-552-1585 <(828)%20552-1585>
>
> Please do NOT use the contact information above for unsolicited sales
> contacts.
>
> All unsolicited sales calls > /dev/null
>
>
> _______________________________________________
> cisco-voip mailing list
> cisco-voip at puck.nether.net
> https://puck.nether.net/mailman/listinfo/cisco-voip
> <http://cp.mcafee.com/d/5fHCMUgdELTohjuod79EVdTdEEILIFzxO9EVdEEILIFzDDSjqabbXPUVVZMSyyUqejhPuZQrCO6GOIl1hdJnor6TbCOrqKMSdKndIKE9zJd7b_nVMsUCMYZRXBQSnxT8ELK6zB5dPG8FHnjlKy_OEuvkzaT0QSCrjdTWWa8VNwttYQsCXCOsVHkiP5CX5u1FfUY3s4RtxxYGjB1SKmBiRiVCIBztFkJkKpH9oKgGT2TQ1iPtyL0QDYu1FIQszAmjobZ8Qg6BKQGmGncRAIqnjh08dPGHIVmQYYIq8f3RGNDa14Qg2bt9Cy2pfPh070MQgr10Qg1TQJ6iQVlwq87puuxgSevdP01KNxc>
>
>
>
> Confidentiality Note: This message is intended for use only by the
> individual or entity to which it is addressed and may contain information
> that is privileged, confidential, and exempt from disclosure under
> applicable law. If the reader of this message is not the intended recipient
> or the employee or agent responsible for delivering the message to the
> intended recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly prohibited. If
> you have received this communication in error, please contact the sender
> immediately and destroy the material in its entirety, whether electronic or
> hard copy. Thank you _______________________________________________
> cisco-voip mailing list
> cisco-voip at puck.nether.net
> https://puck.nether.net/mailman/listinfo/cisco-voip
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://puck.nether.net/pipermail/cisco-voip/attachments/20170831/b8b44cfa/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 11760 bytes
Desc: not available
URL: <https://puck.nether.net/pipermail/cisco-voip/attachments/20170831/b8b44cfa/attachment.png>