Sample Code : SQL Query Examples

Queries. 2

All subscribers with active transfer rule. 2

All call handlers with active transfer rules. 2

All subscriber extensions, including alternates. 2

All call handler owners. 2

All active greetings for subscribers. 2

Show zero key input for subscribers. 2

All subscribers COS membership. 2

Total subscribers assigned to COS. 2

All subscribers in directory. 2

All subscribers in dialing domain. 2

Obtaining Updates. 2

Revision History. 2

Overview

The SQL Query Examples applications is a simple vehicle to demonstrate some of the more common and useful SQL queries for pulling information about subscribers and call handlers out of the UnityDB database on Unity 4.0(1) and later. The source code for the application isn’t very interesting but is provided anyway – the meat of what you need to know is demonstrated in the queries themselves and the explanations behind them found here in the help file.

DISCLAIMER: The built in grid control that comes with VB 6.0 is the biggest hunk of junk ever, however I use it here so that folks can pop open the application in VB and walk the code if they wish without having to install a 3^rd party grid control. The built in grid is riddled with bugs including the fact that the slider bar is totally busted. Fortunately the grid is just there to show you the results of the query and isn’t really a critical part of the application.

Requirements/Special Notes

This tool needs to be run locally on a Unity 4.0(1) or later system.

You will need to be logged in as a user that has read access to the UnityDB SQL database on the local Unity server. If you are a member of the local administrators group (or the builtins group in the case of a domain controller) you have read/write access to the database by default. If not you will need to add yourself to the group or grant your account rights using the SQL Enterprise Administration application or via an SQL command in the Query Analyzer or via the OSQL command line interface if you don’t have the Query Analyzer installed. For details on how to do this, refer to the SQL help.

NOTE: This, as with all the code samples on CiscoUnityTools.com, is written against and tested with Unity 4.0(1) and later. Many will work fine with 3.x versions of Unity, however we will not help you with problems you may encounter due to subtle (and not so subtle) differences between the versions.

IMPORTANT: This source is provided "as is" and includes no warranty or guarantees of any kind. Short story - you use it, you're responsible for what happens. End of story. Test heavily before deploying in any kind of production environment and make sure you have a good backup. Cisco TAC (Technical Assistance Center) does NOT support the use of code to administer Unity by anyone in the field so they will not assist you with problems you may encounter.

You may use and modify this and all code posted on CiscoUnityTools.com (AnswerMonkey.net) any way you wish without crediting me or Cisco Systems. Just think nice thoughts about us when you do it.

For assistance with this and all sample code posted on CiscoUnityTools.com or other programmatic administration issues you have with Unity 4.0(1) or later, please visit the Cisco Unity Forum. We will do our best to help you there. We will not assist with programmatic administration of systems earlier than 4.0(1).

Queries

A quick general comment here about the SQL queries you’ll find here:

All queries utilize the concept of “views” in SQL which is why the table names are all prefaced with “vw_”. You should NEVER write queries against the raw tables even though at the moment you can see the views are identical to the tables they “wrap”. We will be changing the database structure and collapsing some tables into one another and the views will be modified to make sure they maintain backwards compatibility as we do this. If you write queries to the raw tables your applications may (and probably will) break in later versions of Unity. Save yourself the trouble and make sure you carefully use only views.

Also notice that these are all read only queries. We’ll be doing all our adds and changes to existing data using stored procedures which are demonstrated in other example applications on CiscoUnityTools.com. Updating information in SQL using direct ad hoc SQL statements to do it is a very bad idea and you’ll get yourself into trouble in a hurry.

To execute these queries you can simply paste the text into the Query Analyzer itself (remember to select the “UnityDB” database in the drop down list at the top) or you can use the command line OSQL interface or, of course, any other mechanism you like such as ADO which is my weapon of choice in VB for such things.

NOTE: It’s a very good idea to make sure you’re familiarized with the Unity object model before digging into these queries. The Unity Object Model chapter is available on the CiscoUnityTools.com on the Documents page. It’s important that you understand what contact rules, messaging rules, menu entries, call handlers, mail users, class of service objects and the like are or these queries are going to be pretty confusing to you. It’s also a good idea to get the latest version of CUDLE (Cisco Unity Data Link Explorer) off the CiscoUnityTools.com site as well. It has much of the UnityDB data dictionary information built into it and you can lookup tables and columns and get brief explanations of what their values are used for and the like. I designed the tool specifically for folks wanting to do their own SQL work in Unity so it’s a handy thing to have on your desktop as you try to wrap your brain around these queries.

ANOTHER NOTE: This isn’t intended as an SQL tutorial for folks. I highly recommend going out and getting yourself a good book on the basics of SQL syntax or cozy up to the online help that installs with the SQL Analyzer application. You don’t need to be an expert but understanding the basic SELECT, FROM, ON, WHERE, etc. commands is a must. Grasping the subtleties of the INNER JOIN and it’s tricky friend the OUTER JOIN functions is a real plus as well since they get used heavily given how information in Unity is spread across multiple tables.

OK, now on to the query examples themselves.

All subscribers with active transfer rule

This query creates a table that shows the subscriber’s display name, their primary extension number and the transfer string for every subscriber in the database that has their transfer rule enabled.

SELECT vw_Subscriber.DisplayName, vw_CallHandler.DTMFAccessId AS Extension, vw_ContactRule.Extension AS 'Transfer String'

FROM vw_Subscriber INNER JOIN vw_CallHandler

ON vw_CallHandler.CallHandlerObjectId=vw_Subscriber.CallHandlerObjectId

INNER JOIN vw_ContactRule

ON vw_ContactRule.ParentObjectId=vw_CallHandler.CallHandlerObjectId

WHERE vw_CallHandler.IsPrimary='1' AND vw_Subscriber.SubscriberType IN ('1','3') AND vw_ContactRule.Alias='alternate' AND vw_ContactRule.Action='1'

The first INNER JOIN on the 2^nd line is used to pair up the subscriber records with their corresponding primary call handlers. This is a very common device since much of the information about a subscriber is actually stored in their primary call handler including, in this case, their references to the contact rules (transfer rules).

The second INNER JOIN on the 4^th line is used to get all the contact rules associated with the primary call handler. Even though subscribers only use the Alternate transfer rule which is always active, all three rules (alternate, standard and off hours) are in the database. As such we need to remember to filter for only the contact rule with an alias of “alternate” here on the WHERE line.

Since the alternate contact rule is always active for subscribers the only thing we need to check for is if the “action” field indicates that we will be dialing the transfer string that’s stored in the contact rules’ “extension” column. An action of “1” indicates we will be transferring a value of “0” means we will skip the transfer rule and go right to the subscriber’s greeting. The keen observer will wonder if there’s a separate “action” value for dialing a random number vs. the “dial subscribers extension” radio button option in the SA. The answer is no – they both use an action value of 1, the appropriate dial string just gets copied into the extension column.

The subscriber’s primary extension is stored on their corresponding primary call handler in the DTMFAccessId field. Remember that a subscriber can have up to ten alternate extension which are stored in the DTMFAccessID table. A sample showing how to use that table is included below.

The filter for the SubscriberType column being either 1 or 3 indicates we’re only interested in full Exchange or Lotus subscriber respectively and not internet subscribers (SMTP, AMIS, VPIM or Bridge subscribers) in this query. Check the data dictionary information in the CUDLE database explorer noted above for more information on the other subscriber type values. Be careful not to just remove this filter entirely since you’ll get some undesirable subscriber matches on type “0” which is the special hidden Unity Messaging System Account and type “6” which is the special hidden Installation account used internally. You always want to make sure you exclude these two types at a minimum since they don’t have all the same properties as regular subscribers do and you’ll get some odd behavior.

All call handlers with active transfer rules

This query is very similar to the subscriber transfer rules above however we need to take into account that for regular call handlers (i.e. those not associated with subscribers – also called “application call handlers”) all three contact rules are available for use and the alternate and off hours rules can be disabled (the standard rule can never be disabled).

SELECT vw_CallHandler.TextName as 'Display Name', vw_CallHandler.DTMFAccessId AS 'Extension', vw_ContactRule.Alias, vw_ContactRule.Extension AS 'Transfer String'

FROM vw_CallHandler INNER JOIN vw_ContactRule

ON vw_ContactRule.ParentObjectId=vw_CallHandler.CallHandlerObjectId

WHERE vw_CallHandler.IsPrimary='0' AND vw_ContactRule.Action='1' AND ((vw_ContactRule.TimeExpires IS NULL) OR (vw_ContactRule.TimeExpires > GETDATE()))

In this case we want to get only non primary call handlers so the WHERE filter includes a check to be sure the call handler’s IsPrimary column is set to ‘0’.

Again we use the INNER JOIN to pull all three contact rules for each call handler out to include in the resulting table. It will show up to three contact rules for each call handler if the rule is both enabled AND the “Action” column is set to “1” which indicates we will be dialing the transfer string found in the contact rule’s “Extension” column.

A contact rule is enabled and disabled based on the “TimeExpires” column. If the column is null or a time in the future then the rule is considered enabled, otherwise it’s considered disabled. We use the “IS NULL” and the comparison with the GETDATE() function to check for these conditions respectively. You’ll see this same convention used for messaging rules (greetings) later.

All subscriber extensions, including alternates

This shows how to get all the extensions for a subscriber, including the alternates. All extensions for all objects in the database are stored in the DTMFAccessID table. Even though you’ll ALSO find them on other tables such as the call handler’s and DistributionList’s DTMFAccessID columns, they are all stored in the DTMFAccessID table for easy searches. Moving forward the individual DTMFAccessID columns in the other tables will go away, however if you’re good boys and girls and use views properly, the views will pull the DTMFAccessID values for those tables out for you to maintain backwards compatibility.

SELECT vw_Subscriber.Alias, vw_DTMFAccessID.DTMFAccessID AS 'Extension', vw_DTMFAccessID.IsPrimaryID

FROM vw_Subscriber INNER JOIN vw_DTMFAccessID

ON vw_DTMFAccessID.ParentObjectID=vw_Subscriber.SubscriberObjectId

WHERE vw_Subscriber.SubscriberType IN ('1','3')

As with many other table relationships, the DTMFAccessID table references it’s corresponding subscriber association using it’s ParentObjectID column matching the SubscriberObjectId column. You’ll see that same convention for many of the sub collections including user input keys, contact rules, menu entries and other tables.

Again, we’re only filtering for full Exchange and Lotus subscriber types here. This query will simply kick out a table that has a row for each extension on every subscriber – in other words a subscriber with one primary (required) and 3 alternate extensions will have 4 rows in the resulting table. The primary extension will show up in the grid as “-1” which indicates true. A decent grid would offer a Boolean field here automatically which would show a checkbox instead but the built in grid for VB is anything but decent.

All call handler owners

A call handler can be owned by a subscriber or a public distribution list. This query produces a table that indicates what type of owner a handler has and the ObjectID value for the corresponding owner is included in one of two columns depending on the owner type. This same mechanism can be used for finding the message recipient for a call handler as well since that can also be either a subscriber or a public distribution list.

SELECT vw_Subscriber.Alias AS 'Subscriber Owner', vw_DistributionList.Alias AS 'DL Owner', vw_CallHandler.Alias AS 'Handler Alias', vw_CallHandler.AdministratorObjectIDType

FROM vw_CallHandler LEFT OUTER JOIN vw_Subscriber

ON vw_CallHandler.AdministratorObjectID=vw_Subscriber.SubscriberObjectId

LEFT OUTER JOIN vw_DistributionList

ON vw_CallHandler.AdministratorObjectID=vw_DistributionList.SystemDListObjectID

WHERE vw_CallHandler.IsPrimary='0'

We use LEFT OUTER JOINS here because I wanted to include the distribution list and call handler columns in all cases and obviously a call handler can only have one or the other as an owner. If we did an INNER JOIN instead, the tables would be empty. If that last statement caused your forehead to crinkle, make sure you take some time to read up on the joys of JOINs at some point.

If the call handler is owned by subscriber, the owner type value will be “1” and the alias of the subscriber will show up in the “Subscriber Owner” column. If, instead, it’s owned by a distribution list the owner type will be “2” and the alias of the distribution list will show up in the “DL Owner” column.

All active greetings for subscribers

This query displays up to 6 rows for each subscriber: one for each active greeting. This includes the alternate, standard, off hours, internal, busy and error greetings. The standard and error greetings should always be active (if not there’s a problem) so you should get at least two rows per subscriber here.

SELECT vw_Subscriber.Alias AS 'Subscriber Alias', vw_MessagingRule.Alias AS 'Active Greeting Alias'

FROM vw_CallHandler INNER JOIN vw_Subscriber

ON vw_CallHandler.AdministratorObjectID=vw_Subscriber.SubscriberObjectId

INNER JOIN vw_MessagingRule

ON vw_CallHandler.CallHandlerObjectID=vw_MessagingRule.ParentObjectID

WHERE vw_CallHandler.IsPrimary='1' AND vw_Subscriber.SubscriberType IN ('1','3') AND (vw_MessagingRule.TimeExpires IS NULL OR vw_MessagingRule.TimeExpires>GETDATE())

This is actually very similar to the transfer rules queries we did above. Again, the first INNER JOIN simply pairs up the subscribers with their corresponding primary call handlers. The second INNER JOIN pulls all the messaging rules (greetings) out for the corresponding primary call handlers. Notice that the messaging rules, like the contact rules, are associated with the primary call handler here not the subscriber. This is a common point of confusion for folks just jumping into the Unity database.

Technically the filter for “IsPrimary=’1’” in the WHERE clause is not necessary since a non primary call handler would not be tied to a subscriber but I add it here just to be complete. Again, we’re filtering only for regular Exchange and Lotus subscribers which have a SubscriberType value of 1 or 3 respectively. Just like contact rules, messaging rules are considered active if their TimeExpires column is null or is set to a date in the future which we check for using the IS NULL and GETDATE() functions.

Show zero key input for subscribers

This query pulls the Action, ConversationName and DestinationObjectID values for the “0” user input key for all subscribers. These three values determine what Unity does when a user presses “0” during the subscriber’s greeting. You’ll see very similar trilogies of data for other tables such as the messaging rules after greeting actions, the subscriber’s exit actions and call handler’s after message actions. See the CUDLE Database Explorer on CiscoUnityTools.com for more details on what the various values in these columns actually means.

SELECT vw_Subscriber.Alias AS 'Subscriber Alias', vw_MenuEntry.Action, vw_MenuEntry.ConversationName, vw_MenuEntry.DestinationObjectID, vw_Menuentry.DestinationObjectIDType

FROM vw_CallHandler INNER JOIN vw_Subscriber

ON vw_CallHandler.AdministratorObjectID=vw_Subscriber.SubscriberObjectId

INNER JOIN vw_MenuEntry

ON vw_CallHandler.CallHandlerObjectID=vw_MenuEntry.ParentObjectID

WHERE vw_CallHandler.IsPrimary='1' AND vw_Subscriber.SubscriberType IN ('1','3') AND vw_MenuEntry.Alias='0'

This should start looking pretty familiar. The first INNER JOIN pairs up the subscribers with their primary call handlers and the second INNER JOIN pulls all the menu entry values for that call handler out.

In this case we want to filter the menu entries only for the “0” key and, again, we’re only pulling full Exchange or Lotus subscribers here. As noted above the filter for IsPrimary=’1’ is not strictly necessary here but is added for style points.

All subscribers COS membership

This query simply creates a table that shows the text name of the COS object each subscriber is associated with. The Class of Service object dictates which licenses a user is granted, what access they have to the web based administration consoles, if any, and a number of other important details.

SELECT vw_Subscriber.Alias AS 'Subscriber Alias', vw_COS.TextName AS 'COS Text Name'

FROM vw_Subscriber INNER JOIN vw_COS

ON vw_Subscriber.COSObjectID=vw_COS.COSObjectID

WHERE vw_Subscriber.SubscriberType IN ('1','3')

ORDER BY vw_COS.TextName

Total subscribers assigned to COS

This is very similar to the previous query only this time we’re only going to show the total counts for how many subscribers are associated with each Class of Service object.

SELECT vw_COS.TextName AS 'COS Text Name', COUNT(*) AS 'Subscribers Assigned to COS'

FROM vw_Subscriber INNER JOIN vw_COS

ON vw_Subscriber.COSObjectID=vw_COS.COSObjectID

WHERE vw_Subscriber.SubscriberType IN ('1','3')

GROUP BY vw_COS.TextName

A subscriber is associated with one and only one COS object which is used for, among other things, enforcing license limits. When adding new subscribers it’s necessary to make sure you know how many licenses are used up currently and if adding a new user to a particular COS will put you “over the limit” on one or more counts. Using the COUNT() function as shown above provides an easy way of quickly grabbing how many of the various license counts are used up in your system.

You can see how to get the current license count information in the examples that show how to add new subscribers.

All subscribers in directory

This query shows the display name, the server name of their home Unity server and their primary extension number of every subscriber in the entire directory that the local Unity server can see.

SELECT vw_Subscriber_Global.DisplayName, vw_Location_Global.HomeServer AS 'Unity Server', vw_DTMFAccessID.DTMFAccessID AS 'Primary Extension'

FROM vw_Subscriber_Global INNER JOIN vw_Location_Global

ON vw_Location_Global.LocationObjectID=vw_Subscriber_Global.LocationObjectID

INNER JOIN vw_DTMFAccessID

ON vw_DTMFAccessID.ParentObjectID=vw_Subscriber_Global.SubscriberObjectID

WHERE vw_Subscriber_Global.SubscriberType IN ('1','3') AND vw_DTMFAccessId.IsPrimaryID='1'

The Unity directory monitor is always watching the directory (Active Directory in the case of Exchange 2000, the Exchange 5.5 directory or the Domino directory) for other Unity subscribers and location objects. All subscribes found are added to the Global Locations table and all location objects found are added to the Global Locations table. This can be very handy for quickly grabbing information about subscribers that span multiple Unity servers on your network without having to trudge out and connect to all the Unity servers individually and dig it out yourself or mess around in the directory on your own. This is, in fact, the same mechanism the Global Subscriber Manager tool uses to provide a quick view of your entire Unity layout. See the Architecture Overview document on the CiscoUnityTools.com documents page for more details on how the directory monitor works.

The first INNER JOIN is matching up all the subscribers in the global subscriber table with their corresponding location object in the global locations table. All subscribers, including those on the local Unity server, are represented in the global subscribers table. The location objects have information about the home Unity server they subscriber’s are “hosted” on, including the server name which is pulled out in this example.

The second INNER JOIN is pulling the primary extension value out of the DTMFAccessID table for each subscriber so we can show it. We’re only interested in the one primary extension here, not any alternates, so we filter against the IsPrimary flag being set to ‘1’ here in the WHERE clause.

Again, we’re only interested in full Exchange or Lotus subscribers here so we filter on a SubscriberType of 1 or 3 respectively.

All subscribers in dialing domain

This query is very similar to the above example where we pull out all subscribers in the directory. However in this case we limit it to only subscribers that are in the same dialing domain as the local Unity server we’re running on. If you don’t know what a “dialing domain” is, you should check out the Digital Networking documentation for Unity.

SELECT vw_Subscriber_Global.DisplayName, vw_Location_Global.HomeServer AS 'Unity Server', vw_DTMFAccessID.DTMFAccessID AS 'Primary Extension'

FROM vw_Subscriber_Global INNER JOIN vw_Location_Global

ON vw_Location_Global.LocationObjectID=vw_Subscriber_Global.LocationObjectID

INNER JOIN vw_DTMFAccessID

ON vw_DTMFAccessID.ParentObjectID=vw_Subscriber_Global.SubscriberObjectID

INNER JOIN vw_Location

ON vw_Location.DialingDomainName=vw_Location_Global.DialingDomainName

WHERE vw_Subscriber_Global.SubscriberType IN ('1','3') AND vw_DTMFAccessId.IsPrimaryID='1' AND vw_Location.Alias='default'

A dialing domain is defined simply with the DialingDomainName string in the location object. If the DialingDomainName is null the local Unity server is not in a dialing domain and the query will return only the local subscribers. We don’t need to check for this value being NULL specifically here since SQL has this neat little trick where two NULL values are not considered equal (i.e. “NULL=NULL” is false). Normally this is kind of annoying but here we put it to good use.

To get only subscribers in the dialing domain we add a 3^rd INNER JOIN here where we only include subscribers associated with locations that share the same DialingDomainName string as the local Unity server’s “default” location object. Since there can be multiple “delivery locations” on the local box we need to be sure to filter out only the “default” location which is created by the Unity setup and is where the dialing domain membership for the local Unity server resides. Again, if you don’t know what a “delivery location” is, be sure to check out the digital networking documentation for Unity.

Obtaining Updates

To check for updates to this tool, visit http://www.CiscoUnityTools.com

Revision History

Version 1.0.1

First release of sample application