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

Thu Aug 31 17:53:38 EDT 2017

Thanks!  I wrote that off the top of my head too.  I know there's like 50
more things to know, but if you master those basics, you can figure out the
rest with very little additional effort.

I've spent a lot of time in the tables on CUCM, a fair but careful amount
in CUC <https://twitter.com/avholloway45633/status/781873225097629698>,
very little in UCCX (mostly callcontactdetail), and none at all for other
products.

On Thu, Aug 31, 2017 at 3:40 PM Stephen Welsh <stephen.welsh at unifiedfx.com>
wrote:

> 👏👏👏
>
> Wow, that’s the best advice I’ve every seen on getting to grips with
> CUCM’s database.
>
> Kind Regards
>
> Stephen Welsh
> CTO
> UnifiedFX
>
> Sent from my iPad
>
> On 31 Aug 2017, at 21:37, Anthony Holloway <
> avholloway+cisco-voip at gmail.com> wrote:
>
> 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.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
>>
> _______________________________________________
> 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/ee43db0d/attachment.html>