<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">
If you really want to dive into the UCM database you can find the data dictionaries in the AXL section of <a href="http://developer.cisco.com" class="">developer.cisco.com</a>.
<div class=""><br class="">
<div class="">-Ryan </div>
<br class="">
<div>
<div class="">On Aug 31, 2017, at 5:53 PM, Anthony Holloway <<a href="mailto:avholloway+cisco-voip@gmail.com" class="">avholloway+cisco-voip@gmail.com</a>> wrote:</div>
<br class="Apple-interchange-newline">
<div class="">
<div dir="ltr" class="">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.
<div class=""><br class="">
</div>
<div class="">I've spent a lot of time in the tables on CUCM, a fair but <a href="https://twitter.com/avholloway45633/status/781873225097629698" class="">
careful amount in CUC</a>, very little in UCCX (mostly callcontactdetail), and none at all for other products.</div>
<div class=""><br class="">
<div class="gmail_quote">
<div dir="ltr" class="">On Thu, Aug 31, 2017 at 3:40 PM Stephen Welsh <<a href="mailto:stephen.welsh@unifiedfx.com" class="">stephen.welsh@unifiedfx.com</a>> wrote:<br class="">
</div>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="auto" class="">
<div class="">đź‘Źđź‘Źđź‘Ź</div>
<div class=""><br class="">
</div>
<div class="">Wow, that’s the best advice I’ve every seen on getting to grips with CUCM’s database.</div>
<div class=""><br class="">
</div>
<div class="">Kind Regards</div>
<div class=""><br class="">
</div>
<div class="">Stephen Welsh</div>
<div class="">CTO</div>
<div class="">UnifiedFX</div>
<div class=""><br class="">
<div id="m_7136249157402021324AppleMailSignature" class="">Sent from my iPad</div>
</div>
</div>
<div dir="auto" class="">
<div class="">
<div class=""><br class="">
On 31 Aug 2017, at 21:37, Anthony Holloway <<a href="mailto:avholloway+cisco-voip@gmail.com" target="_blank" class="">avholloway+cisco-voip@gmail.com</a>> wrote:<br class="">
<br class="">
</div>
</div>
</div>
<div dir="auto" class="">
<div class="">
<blockquote type="cite" class="">
<div class="">
<div dir="ltr" class="">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.
<div class=""><br class="">
</div>
<div class="">As for the database, here are a few key points:</div>
</div>
</div>
</blockquote>
</div>
</div>
<div dir="auto" class="">
<div class="">
<blockquote type="cite" class="">
<div class="">
<div dir="ltr" class="">
<div class="">
<ul class="">
<li class="">Anything that registers is a Device and is in the device table.<br class="">
<br class="">
</li><li class="">Anything that is a pattern is in the numplan table<br class="">
<br class="">
</li><li class="">End Users are in the enduser table, while Application Users are in the applicationuser table<br class="">
<br class="">
</li><li class="">The database is a relational database, meaning that data from one table will have a relationship to data in another table<br class="">
<br class="">
</li><li class="">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<br class="">
<br class="">
</li><li class="">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)<br class="">
<br class="">
</li><li class="">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<br class="">
<br class="">
</li><li class="">To find a list of table names: select tabname from systables order by tabname<br class="">
<br class="">
</li><li class="">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<br class="">
<br class="">
</li><li class="">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<br class="">
This tip is courtesy of Wes Sisk: <a href="http://cisco-voip.markmail.org/thread/vtoasdkgieadmou3" target="_blank" class="">
http://cisco-voip.markmail.org/thread/vtoasdkgieadmou3</a><br class="">
<br class="">
</li></ul>
</div>
</div>
</div>
</blockquote>
</div>
</div>
<div dir="auto" class="">
<div class="">
<blockquote type="cite" class="">
<div class="">
<div dir="ltr" class="">
<div class="">
<ul class="">
<li class="">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)<br class="">
<br class="">
</li></ul>
</div>
</div>
</div>
</blockquote>
</div>
</div>
<div dir="auto" class="">
<div class="">
<blockquote type="cite" class="">
<div class="">
<div dir="ltr" class="">
<div class="">
<ul class="">
<li class=""><image.png><br class="">
</li></ul>
</div>
</div>
</div>
</blockquote>
</div>
</div>
<div dir="auto" class="">
<div class="">
<blockquote type="cite" class="">
<div class="">
<div dir="ltr" class="">
<div class=""><br class="">
<div class="gmail_quote">
<div dir="ltr" class="">On Thu, Aug 31, 2017 at 2:08 PM Ben Amick <<a href="mailto:bamick@humanarc.com" target="_blank" class="">bamick@humanarc.com</a>> wrote:<br class="">
</div>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-US" link="blue" vlink="purple" class="">
<div class="m_7136249157402021324m_8534312097838823906WordSection1">
<p class="MsoNormal">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.<u class=""></u><u class=""></u></p>
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
<p class="MsoNormal">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?<u class=""></u><u class=""></u></p>
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
<p class="MsoNormal"><b class="">From:</b> cisco-voip [mailto:<a href="mailto:cisco-voip-bounces@puck.nether.net" target="_blank" class="">cisco-voip-bounces@puck.nether.net</a>]
<b class="">On Behalf Of </b>Brian Meade<br class="">
<b class="">Sent:</b> Wednesday, August 30, 2017 5:28 PM<br class="">
<b class="">To:</b> Tim Reimers <<a href="mailto:treimers@ashevillenc.gov" target="_blank" class="">treimers@ashevillenc.gov</a>><br class="">
<b class="">Cc:</b> cisco-voip <<a href="mailto:cisco-voip@puck.nether.net" target="_blank" class="">cisco-voip@puck.nether.net</a>><br class="">
<b class="">Subject:</b> Re: [cisco-voip] Automatically exporting just the DN and Description fields<u class=""></u><u class=""></u></p>
</div>
</div>
<div lang="EN-US" link="blue" vlink="purple" class="">
<div class="m_7136249157402021324m_8534312097838823906WordSection1">
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
<div class="">
<p class="MsoNormal">Use the AXL API and the following SQL query to automate this:<u class=""></u><u class=""></u></p>
<div class="">
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
<div class="">
<p class="MsoNormal">select dnorpattern,description from numplan where tkpatternusage='2'<u class=""></u><u class=""></u></p>
</div>
</div>
<div class="">
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal">You can put "run sql" before the query to run via SSH to CUCM.<u class=""></u><u class=""></u></p>
</div>
</div>
<div class="">
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
<div class="">
<p class="MsoNormal">On Wed, Aug 30, 2017 at 4:45 PM, Tim Reimers <<a href="mailto:treimers@ashevillenc.gov" target="_blank" class="">treimers@ashevillenc.gov</a>> wrote:<u class=""></u><u class=""></u></p>
<blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in" class="">
<div class="">
<p class="MsoNormal">Hi all-<u class=""></u><u class=""></u></p>
<div class="">
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal">In CUCM 9.12<u class=""></u><u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal">I'm trying to figure out how to export just a few of the fields from the UCM database.<u class=""></u><u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal">What we're after is simply the DN number, and the contents of the Description field associated with that DN<u class=""></u><u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal">I've done a Phone export, and the resulting CSV is so huge it's unusable in Google Sheets.<u class=""></u><u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal">I know I can set up an Export as a repeated schedule, but I have two questions.<u class=""></u><u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal">- Can I somehow reduce the export query to a limited set of details about lines only. I don't need phone info particularly.<u class=""></u><u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal">- 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.<u class=""></u><u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
</div>
<div class="">
<p class="MsoNormal">Thanks, Tim<br clear="all" class="">
<u class=""></u><u class=""></u></p>
<div class="">
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
</div>
<p class="MsoNormal">-- <u class=""></u><u class=""></u></p>
<div class="">
<div class="">
<div class="">
<div class="">
<p class=""><b class=""><i class=""><span lang="EN" style="font-size:10.0pt;font-family:"Arial",sans-serif;color:blue" class=""><a href="http://cp.mcafee.com/d/1jWVIg4xEqdELTohjuod79EVdTdEEILIFzxO9EVdEEILIFzDDSjqabbXPUVVZMSyyUqejhPuZQrCO6GOIl1hdJnor6TbCOrqKMSdKndIKE9zJd7b_nVMsUCMYZRXBQSnxT8ELK6zB5dPG8FHnjlKy_OEuvkzaT0QSyrjdTWWa8VNwttYQsCXCOsVHkiPfxNyhmiTEiTw0e695po0XsG2JCWjGxbVyIBGlEaSrFeG7Q7IyVKQGmGncRAIn8lrxrW0FpKNnwqj-f0QSqehOb9I5-Aq83iTqlblbCqOmdbFEw46VRlSsHquumd47xWRoPB0yq815KAPh1cDVEw3woq8dwwq80XWmz9qsGMd43ILfgEr7fCNxQY" target="_blank" class="">Quis
custodiet ipsos </a></span></i></b><b class=""><i class=""><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:blue" class="">opus reticulatum</span></i></b><u class=""></u><u class=""></u></p>
<p class="">Tim Reimers<u class=""></u><u class=""></u></p>
<p class="">Network Administrator<u class=""></u><u class=""></u></p>
<p class="">I.T Services<u class=""></u><u class=""></u></p>
<p class="">City of Asheville <u class=""></u><u class=""></u></p>
<p class=""><a href="mailto:treimers@ashevillenc.gov" target="_blank" class="">treimers@ashevillenc.gov</a><u class=""></u><u class=""></u></p>
<p class="">(desk) <a href="tel:(828)%20259-5512" target="_blank" class="">828-259-5512</a><u class=""></u><u class=""></u></p>
<p class="">(cell) <a href="tel:(828)%20552-1585" target="_blank" class="">828-552-1585</a><u class=""></u><u class=""></u></p>
<p class="">Please do NOT use the contact information above for unsolicited sales contacts.
<u class=""></u><u class=""></u></p>
<p class="">All unsolicited sales calls > /dev/null<u class=""></u><u class=""></u></p>
</div>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><br class="">
_______________________________________________<br class="">
cisco-voip mailing list<br class="">
<a href="mailto:cisco-voip@puck.nether.net" target="_blank" class="">cisco-voip@puck.nether.net</a><br class="">
<a href="http://cp.mcafee.com/d/5fHCMUgdELTohjuod79EVdTdEEILIFzxO9EVdEEILIFzDDSjqabbXPUVVZMSyyUqejhPuZQrCO6GOIl1hdJnor6TbCOrqKMSdKndIKE9zJd7b_nVMsUCMYZRXBQSnxT8ELK6zB5dPG8FHnjlKy_OEuvkzaT0QSCrjdTWWa8VNwttYQsCXCOsVHkiP5CX5u1FfUY3s4RtxxYGjB1SKmBiRiVCIBztFkJkKpH9oKgGT2TQ1iPtyL0QDYu1FIQszAmjobZ8Qg6BKQGmGncRAIqnjh08dPGHIVmQYYIq8f3RGNDa14Qg2bt9Cy2pfPh070MQgr10Qg1TQJ6iQVlwq87puuxgSevdP01KNxc" target="_blank" class="">https://puck.nether.net/mailman/listinfo/cisco-voip</a><u class=""></u><u class=""></u></p>
</blockquote>
</div>
<p class="MsoNormal"><u class=""></u> <u class=""></u></p>
</div>
</div>
</div>
<div lang="EN-US" link="blue" vlink="purple" class="">
<div class="m_7136249157402021324m_8534312097838823906WordSection1"></div>
</div>
<br class="">
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 _______________________________________________<br class="">
cisco-voip mailing list<br class="">
<a href="mailto:cisco-voip@puck.nether.net" target="_blank" class="">cisco-voip@puck.nether.net</a><br class="">
<a href="https://puck.nether.net/mailman/listinfo/cisco-voip" rel="noreferrer" target="_blank" class="">https://puck.nether.net/mailman/listinfo/cisco-voip</a><br class="">
</blockquote>
</div>
</div>
</div>
</div>
</blockquote>
</div>
</div>
<div dir="auto" class="">
<div class="">
<blockquote type="cite" class="">
<div class=""><span class="">_______________________________________________</span><br class="">
<span class="">cisco-voip mailing list</span><br class="">
<span class=""><a href="mailto:cisco-voip@puck.nether.net" target="_blank" class="">cisco-voip@puck.nether.net</a></span><br class="">
<span class=""><a href="https://puck.nether.net/mailman/listinfo/cisco-voip" target="_blank" class="">https://puck.nether.net/mailman/listinfo/cisco-voip</a></span><br class="">
</div>
</blockquote>
</div>
</div>
</blockquote>
</div>
</div>
</div>
_______________________________________________<br class="">
cisco-voip mailing list<br class="">
<a href="mailto:cisco-voip@puck.nether.net" class="">cisco-voip@puck.nether.net</a><br class="">
https://puck.nether.net/mailman/listinfo/cisco-voip<br class="">
</div>
</div>
<br class="">
</div>
</body>
</html>