SQL Cisco Unity Connection?

Version 1
    This document was generated from CDN thread

    Created by: Michael Schmidt on 22-05-2013 03:25:11 AM
    Hi,

    we have a Stand Alone Cisco Unity Connection Cluster in Verion 8.6.2.

    Can I use in Cisco Unity Connection SQL commands similar I use it in Cisco CUCM?
     
    How a SQL command looks like for CUUC for:
     
    - deleting a mailbox
    - creating a mailbox
     
    Do I have to create a AXL User in CUUC like I did it at CUCM?
     
    BR
    Michael

    Subject: Re: New Message from Michael Schmidt in AXL Developer - Administration XML
    Replied by: Sascha Monteiro on 22-05-2013 03:29:51 AM
    for CUC you need to use the CUPI interface, accessed through REST

    http://docwiki.cisco.com/wiki/Cisco_Unity_Connection_Provisioning_Interface_(CUPI)_API


    On Wed, May 22, 2013 at 8:25 PM, Cisco Developer Community Forums <
    cdicuser@developer.cisco.com> wrote:

    > Michael Schmidt has created a new message in the forum "Administration XML
    > Questions": --------------------------------------------------------------
    > Hi,
    >
    > we have a Stand Alone Cisco Unity Connection Cluster in Verion 8.6.2.
    >
    > Can I use in Cisco Unity Connection SQL commands similar I use it in Cisco
    > CUCM?
    >
    > How a SQL command looks like for CUUC for:
    >
    > - deleting a mailbox
    > - creating a mailbox
    >
    > Do I have to create a AXL User in CUUC like I did it at CUCM?
    >
    > BR
    > Michael
    > --
    > To respond to this post, please click the following link:
    > http://developer.cisco.com/web/axl-developer/forums/-/message_boards/view_message/15478324or simply reply to this email.




    --


    Regards,


    *Sascha Monteiro* BSc, CCIE(Voice)#14395: Software Development Manager
    Atea Systems Limited
    PO Box 22042, Wellington, New Zealand
    Phone: +64 4 464 0046 | Cell: +64 21 460 030
    sascha@ateasystems.com | http://www.ateasystems.com

    *making telephony **better*

    Subject: RE: SQL Cisco Unity Connection?
    Replied by: Michael Schmidt on 22-05-2013 07:05:59 AM
    Hi Sascha,
    thank you for your answer.

    But is there also a way to edit users / mailboxes directly via SQL commands to edit the database?
    The same way like a Perl Script with SQL commands for CUCM for editing directly the CUCM database.
     
    Actually most of the mailboxes / users at CUUC are imported via LDAP.
     
    BR
    Michael

    Subject: RE: New Message from Michael Schmidt in AXL Developer - Administration XML
    Replied by: Dennis Heim on 22-05-2013 07:40:27 AM
    I am sure you could find a way to fit the square peg in the round hole.  The issue is that AXL (CUCM) and REST (CUC) are the support provisioning methods, and also the best way to avoid a resume generating event.

    Dennis Heim | Sr. Unified Collaboration Team Lead & Solution Architect
    World Wide Technology | 314.212.1814 | dennis.heim@wwt.com<mailto:dennis.heim@wwt.com>
    “Creating Impact, Ignition & Scalability”

    From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
    Sent: Wednesday, May 22, 2013 8:06 AM
    To: cdicuser@developer.cisco.com
    Subject: New Message from Michael Schmidt in AXL Developer - Administration XML Questions: RE: SQL Cisco Unity Connection?

    Michael Schmidt has created a new message in the forum "Administration XML Questions": -------------------------------------------------------------- Hi Sascha,
    thank you for your answer.

    But is there also a way to edit users / mailboxes directly via SQL commands to edit the database?
    The same way like a Perl Script with SQL commands for CUCM for editing directly the CUCM database.

    Actually most of the mailboxes / users at CUUC are imported via LDAP.

    BR
    Michael
    --
    To respond to this post, please click the following link: http://developer.cisco.com/web/axl-developer/forums/-/message_boards/view_message/15482806 or simply reply to this email.

    Subject: RE: SQL Cisco Unity Connection?
    Replied by: Martin Sloan on 22-05-2013 07:46:03 AM
    Michael,

    Unity provides the REST interface which doesn't include the SQL functionality like CUCM.  However, you can connect to Unity via ODBC, although I haven't had any luck bringing that connection up myself using Perl and the DB connection modules.  After many try and fail attempts, I conceded to using the REST interface which requires a few more messages but provides the functionality I needed.  If you're looking for some tools to help explore the Unity DB, check out http://ciscounitytools.com/SiteList/AllDownloads/alldownloads.html and look for CUDLI.  You need to install some Informix drivers for this.  Also, check out the CUPI forums like was mentioned above.  Folks like Sascha, Jeff Lindborg and others have really helped me out there.  My personal favorite page for Unity REST info is
    http://docwiki.cisco.com/wiki/Cisco_Unity_Connection_Provisioning_Interface_%28CUPI%29_API

    There are some really nice how-to's within that site.

    I hpoe this helps
    Marty

    Subject: RE: SQL Cisco Unity Connection?
    Replied by: Sascha Monteiro on 22-05-2013 02:26:57 PM
    Michael,
    You might want to look into using the CUPI REST API through perl, manipulating the database through ODBC is not supported by TAC

    Subject: RE: SQL Cisco Unity Connection?
    Replied by: Martin Sloan on 22-05-2013 03:09:47 PM
    Michael,
    Here's an example framework using the PEST client to add a user via REST LDAP import.  You'll have to fill in the appropriate values for the userid, dtmfaccessid (extension) and others but you can at least see the structure of the messaging.  The 'base_url' for Unity can be defined in the Pest.php file, just put in 'https://your.cuc.ip.address' and it will call that for each pest function.
    The link I posted above to the CUPI wiki really helped me work through issues with the syntax of the requests.  The PEST client is easy enough to use so just take a look at what the server is expecting in the messaging on the wikit.  One of the benefits of REST is that you can test some of the calls by just typing the URL into a browser.
     
    <?php
    require_once('includes/pest-master/Pest.php');
    require_once('includes/pest-master/PestXML.php');
     
    createVmAccount();
     
    function importFromLdap($pest,$alias,$pkid,$dtmfAccessId){
      global $userData;
      try
        {
          $postUrl = "/vmrest/import/users/ldap?templateAlias=HQ-DEFAULT-UT";
          $user = $pest->post($postUrl,"<ImportUser><alias>$alias</alias><pkid>$pkid</pkid><dtmfAccessId>$dtmfAccessId</dtmfAccessId></ImportUser>");
        } catch (Exception $e) {
        systemErrorMessage("VM import from LDAP",$e->getMessage());
      }
      return;
    }
    function getVmVars($pest){
      global $userData;
      try
        {
          $ldapUrl = urlencode('(alias is ' . $userId . ')');
          $contactUnity = $pest->get("/vmrest/import/users/ldap?query=" . $ldapUrl);
          return $contactUnity;
        } catch (Exception $e) {
        systemErrorMessage("VM get user Vars from LDAP",$e->getMessage());
      }
    }
    function doCreateAccount($pest) {
      global $userData;
      $contactUnity = getVmVars($pest);
      if ($contactUnity->ImportUser->alias) {
        $alias = $contactUnity->ImportUser->alias;
        $pkid = $contactUnity->ImportUser->pkid;
        importFromLdap($pest,$alias,$pkid,$extension);
      }
      else {
        systemErrorMessage("No user found to import from LDAP for VM!\n",$e->getMessage());
      }
    }
    function checkVmAccount($pest) {
      global $userData;
      try
        {
          $unityUrl = urlencode('(alias is ' . $userId . ')');
          $contactUnity = $pest->get("/vmrest/users?query=" . $unityUrl);
          return $contactUnity;
        } catch (Exception $e) {
        systemErrorMessage("Check User Account",$e->getMessage());
      }
    }
    function createVmAccount() {
        global $userData;
        $pest = new PestXML();
      try
        {
          $pest->setupAuth();
        } catch (Exception $e) {
        systemErrorMessage("VM authentication error",$e->getMessage());
      }
      $vmCheck = checkVmAccount($pest);
      if ($vmCheck->User->URI) {
        return;
      }
      else {
        doCreateAccount($pest);
      }
    ?>

    Subject: RE: SQL Cisco Unity Connection?
    Replied by: Martin Sloan on 22-05-2013 02:42:00 PM
    I agree and should have mentioned that my own project to bring up the ODBC was for a read-only usage.  Provisioning is best done with REST but it is nice to what the options are.  Gathering large data sets via REST hasn't been the most efficient way to extract information from the Unity DB.  It takes hours to complete on Unity what takes minutes on CUCM using SQL.

    If you're using any PHP, I've found the PHP Rest Client, PEST, is easier to use than the Perl modules.  Just need to download the library and 'include' in your script.

    Subject: RE: SQL Cisco Unity Connection?
    Replied by: Michael Schmidt on 22-05-2013 02:42:56 PM
    Hi Marty,
    Hi Sascha,
     
    thank for your answers. Does anybody provide me some example REST scripts for deleting / creating a mailbox in CUC? At the moment I don`t know how to use this REST interface in CUC :-(
     
    BR
    Michael

    Subject: RE: SQL Cisco Unity Connection?
    Replied by: Michael Schmidt on 22-05-2013 03:27:01 PM
    Hi Marty,
     
    thank you. I will try this tomorrow in the office.
     

    BR
    Michael