UnityDirDB SQL Query - Caller Input Options

[Martin Sloan]

Hello,

I've been gathering some Unity Connection subscriber data via CUPI and have recently begun a conversion to using the database proxy service, hoping to see some performance gains over the REST API.  I have CUDLI installed and I'm browsing the database to get more familiar with the structure, but was wondering if someone could give me a boost with an SQL query to gather some data distributed across a couple different tables.  It would also help me to get familiar with where some of this data lives.

What I need is to gather subscribers/users that have their caller input option '0' set to 'Transfer to Alternate Contact Number' and what that transfer number is.  So the results would be something like 'alias(or user objectid),zerotransfernumber(null is ok)'.

Many Thanks

[lindborg · Accepted solution]

A brown belt query because this info is spread across a few tables…

A subscriber has a call handler tied to them which is what all menu entries (caller input keys) are tied to (as well as greetings, transfer rules etc…).  If you look at the vw_menuentry view in CUDLI it’ll show you what all the columns means – notably an action of “7” is a transfer to an alternate contact number – the menu entry itself gets tied to an alternate contact number in the vw_alternatecontactnumber view (i.e. you can have more than one alternate contact number – one for each menu entry key if you like).

So the query to get the list of users that have their 0 key mapped to an alternate contact number would look something like this:

SELECT vw_subscriber.alias, vw_alternatecontactnumber.TransferNumber

FROM vw_menuentry, vw_subscriber, vw_callhandler, vw_alternatecontactnumber

WHERE vw_subscriber.CallHandlerObjectId= vw_callhandler.ObjectId

AND vw_callhandler.ObjectId = vw_menuentry.CallHandlerObjectId

AND vw_alternatecontactnumber.MenuEntryObjectId = vw_menuentry.ObjectId

AND vw_menuentry.Action=7

AND vw_menuentry.TouchtoneKey='0'

[Martin Sloan]

Ah, it works perfectly sensei!  Thank you for the help.  I will certainly continue to familiarize myself with the tables and views, but this was a great push to get started.

I'm really stoked about this integration.  I tried a few years back and couldn't get it to work, but recently have run into really long REST API sync processes and dealing with HTTP encoding issues so I decided to give it another shot.  It actually was not too bad to get the Informix CSDK and PHP PDO_INFORMIX working on Ubuntu.  My AXL data syncs are way faster over the executeSqlQuery method and I'm expecting Unity Connection will come up to speed now as well.

Thanks again for the assistance.

[dolphine55]

Hi, Mr . Lindborg,

My client is requesting a report  which need customized fields ,  this zero input destination is one of the fields.  Thanks for your answer , this gives me a start.    My customer need more data in the reports , such as message count. 

I have read that  unity data dump may help.  But that require local windows PC and install SW customer location.

I am trying to search solution from again Query.   I was able to add more fields such as time of last call , etc.    But to add message count, require to access different DB from unity.

I wonder if you could give a guidance on how to connect 2 DBs into this same report ?

Many Many Thanks in advance !