Import Subscriber
You can import subscribers from AD (Exchange 2000/20003), Exchange 5.5 or Domino. This example uses the alias of the subscriber to find the user in the directory and, assuming that value is unique (it really should be for Exchange) this will work fine across all back ends Unity can be connected to. If you have a situation where for whatever reason the alias is not unique in AD or Domino then you'll need to also pass in the DirectoryID value which is discussed at the end of this section. In the case of Domino the "alias" maps to the short name for the user which legitimately may not be unique so it's a good idea to pass in the Unique ID (UUID) of the user as the DirectoryID to ensure we match up with the right user. The full code listing for this example on www.CiscoUnityTools.com is specific to importing users from AD since it shows how to navigate around the directory using a tree control and the like, however the base code for importing users via SQL stored procedures is value for all back ends.
Importing a subscriber is actually very similar to creating a new one except you're expecting the SQLSyncer to bind to an existing object in the directory instead of creating a new one. The chances of failure here are much less than in creating a new user, however it is not a "slam dunk" by any means so you still need to check for success or failure here.
You use the same sp_CreateSubscriber stored procedure that you do for creating a new user. Yes, there is a sp_ImportSubscriber stored procedure but that's specifically for use by the dbImport tool as it's pulling 2.x data into a 3.x and 4.x system, you definitely do not want to use that one for importing users from your directory. In fact you wont even find it in Unity 4.0(3) and later since the FullDBImport application was "depreciated" in that version and it was no longer necessary. You need to follow the same procedure for importing a user as for creating a new user outlined above. First check for available licenses, then fill in the required values for the sp_CreateSubscriber stored procedure, then synchronize with the directory and check the results. There are a couple of differences for the import scenario, however.
The first and most important difference is in the flags passed in for the synchronization option. We want to pass in a resynch mask that synchs the subcriber, the DLs they were added to and to only try and bind to an existing AD object, not create a new one if a match in AD is not found for the alias (and directory ID if you choose to pass that in). Remember from the list of flags in the last section that 0x02000000 means to not create anything, only look for an existing directory object to bind with and if none is found to fail. The values for these flags are 0x02000000 + 0x00000001 (sync subscribers)+ 0x00000002 (sync distribution list changes) = 33554435 decimal. So the DirectorySyncFlags parameter like would look like this for import:
oCommand.Parameters.Item("@DirectorySyncFlags") = oCommand.CreateParameter("DirectorySyncFlags", adInteger, adParamInput, , 33554435)
Yes, you could just not pass any specific flag in and tell it to synch the subscriber and distribution list and not specifically limit it to creating a new user or importing an existing user only. If you don't pass in the 0x02000000 or the 0x04000000 values then the syncher will simply try to find the user and if no one is found then create a user on the fly. I don't recommend this, however, as it's easy to get into situations where you accidentally create a directory account you didn't mean to or bind to an existing account you didn't intend to. It's best to have explicit imports and create functions to prevent problems like this.
The second difference is you can pass in the DirectoryID of a user in the directory and the SQLSyncSvr will use that value for searching for the user in the directory before then searching for the alias. You must still pass in the Alias value here as well but if you're concerned about mail aliases not being unique in your directory you may wish to go this route. Technically you can have the same alias in multiple domains in AD although the practice is a seriously bad idea in most cases. The SQLSyncSvr will first try to find the directory ID if it's passed in and if no match is found or it's not passed in, it'll then search for a user by alias in the domain. If that fails to find a match in this case we'll consider it a failure since we're passing in synchronization flags that force it to only bind to an existing user an not to create one on the fly in this example. You could, of course, have it create a new account (for AD and Exchange 5.5 anyway) on the fly in that case but I don't recommend that practice as a rule.
To add the directory ID you'd include this extra line in the stored procedure construction cod:
oCommand.Parameters.Item("@DirectoryId") = oCommand.CreateParameter("DirectoryId", adVarChar, adParamInput, , strDirectoryId
The full code listing for the Import Subscriber example on the web site also shows how to navigate around AD and fetch the directory Id of the user you want to import. What we refer to as the DirectoryID value is actually the ObjectGUID value in AD – we convert this into a string before stuffing it into SQL or using it in a stored procedure. The same conversion needs to take place if you're importing a subscriber by DirectoryID or a public distribution list (discussed later). The logic for converting the GUID in AD into a string looks like this:
'This function converts the ObjectGUID value in Active Directory into a DirectoryID string that we store in SQL and that the stored procedures can use to find the user in the directory. The ObjectGUID property on the user is stored as an array of 16 byes - to convert it to a usable DirectoryID string we need to convert each of those byes to a zero padded hex value (i.e. 7=0x07, 18=0x12 etc...) and concatonate the values into one 32 character long string which is what's stored in SQL and passed into the stored procedures. If you've used ADSI to search by this value you also know you have to do a similiar conversion to get it to work properly there as well.
Function strConvertObjectGUIDToString(unityUser As IADsUser) As String
Dim iCounter As Integer
Dim strTemp As String
Dim strDirectoryID As String
'walk through each of the 16 bytes in the array off the ObjectGUID property for the user. For the record the ObjectGUID off a distribution list needs to be converted in the same way.
For iCounter = 0 To 15
strTemp = vbNullString
strTemp = Trim(Hex(unityUser.Get("OBjectGUID")(iCounter)))
'if the value is only one character, force it to be zero padded on the left
If Len(strTemp) = 1 Then
strTemp = "0" + strTemp
End If
strDirectoryID = strDirectoryID + strTemp
Next iCounter
strConvertObjectGUIDToString = strDirectoryID
Exit Function
In Domino the DirectoryID value corresponds to the UUID (Unique Identifier) of the user in the directory. Again, you can pass just the alias of the user or both the alias and the DirectoryID (UUID) to bind to an existing user in Domino – this is the only method for adding new subscribers to Domino programmatically since creating new users on the fly is not supported.
Other than those difference the procedure is identical to that outlined for creating new subscribers including the synchronization ticket handling. In the Batch Subscriber Operations section later we'll discuss how to import in bulk where which is again very similar to bulk create operations.
Edit Subscriber
Editing properties on subscribers is pretty straight forward stuff in and of itself. However you need to keep in mind that when you change some properties on a subscriber, if you don't force those values to write through to the directory they will not "stick". As soon as another change happens on the subscriber or their user object in the directory, the local value you stuffed into SQL will get stomped on. For instance if you add an alternate extension for a subscriber in SQL but don't initiate a directory synchronization to force that value into the directory it'll appear to be fine for a while. Being the careful engineer that you are you do test calls and SA lookups to be sure it's working as you expect and then go home. Perhaps days later suddenly your extension is gone! Unity has a heinous bug and is dropping data randomly! Yes, I've gotten several such escalations. This is an easy trap to fall in, one I've tripped up on myself from time to time. Eventually SQL triggers will take care of "knowing" which changed values require a synch with the directory for you but for now it's up to you to ask for the synch when you edit the user.
So, which properties do you need to worry about? The easiest way to approach this is to look at what all Unity will sync with the directory. For Active Directory you can turn on AvDSAD traces to see what the directory monitor writes through when you make a change to a subscriber in the SA as was demonstrated in the Architectural Overview chapter. For review there are 27 values that are pushed into and/or pulled from the directory when you make a change to a subscriber. All properties are pushed and fetched regardless of what you've changed, the SA isn't smart enough to know which property you've changed so it asks the directory writer to push or get them all regardless. Some of these properties are "read only" from Unity's perspective such that we'll only get updated values from the directory and pull them in.
Here's a quick run down on the 27 properties we keep synched with in the directory, a good chunk of them are things you will not (or should not) be changing in the local SQL database:
- AddrType. This indicates what type of mailstore the subscriber is connected to. Currently this can be "55" for Exchange 55, "2K" for Exchange 2000 or "DO" for Domino. This is one of those "read only" properties you should never be changing in SQL, or you'll wreak all kinds of havoc.
- Alias. Once a user is created in the directory you should not be changing this value in SQL and then synching it through. You should only set this value in SQL when creating or importing a new user. Once created this value should not be changed in SQL again.
- AMISDisableOutbound. This only applies for AMIS internet subscribers. It's used to indicate that a human recipient received an AMIS delivery attempt through an internet subscriber and requested we stop calling their number by pressing the indicated DTMF keys in the conversation. For full subscribers this property does not exists.
- AlternateDTMFIDs. If you add, remove or update any alternate DTMF Ids in the DTMFAccessID table you need to synch those changes through to the directory or they will be lost.
- DirectoryID. This is the unique identifier for the user in the directory and once it's set you should never change it in SQL. You can set it when importing a new subscriber from the directory but once it's filled in you should never touch it. It's a read only property from Unity's perspective.
- DisplayName. If you change the display name on a subscriber you need to synch that change to the directory or it will be lost.
- DTMFAccessID. If you change the primary extension number of a subscriber you need to synch that change through to the directory or it will be lost. In versions prior to Unity 4.0(3) this value was actually stored in the DTMFAccessID column of the primary call handler associated with the subscriber. In 4.0(3) and later that column is removed (though preserved through the views to remain backwards compatible) and is instead stored in the DTMFaccessID table itself.
- FirstName. If you change the first name of a subscriber you need to synch it through to the directory or it will be lost.
- LastName. If you change the last name of a subscriber you need to synch it through to the directory or it will be lost.
- ListInDirectory. If you change the "list in directory" status of a subscriber you need to synch it through to the directory or it will be lost. This allows or restricts the subscriber from appearing in any name lookup handler (directory handler) in the system.
- LocationObjectID. For full subscribers you would never change their location object Id assignment since all full subscribers are associated with the one primary location object on the Unity server they are created on. You cannot change location assignments on the fly.
- MailDatabase. This points to the message store in the mail database this subscriber's inbox is stored on. You should never touch this value, it's read only from Unity's perspective. If the user is actually moved between mail servers in the back end, the directory monitors will pick up on this change and update this value for you – there's no need to ever change this value on your own.
- MailboxID. This points to the specific mailbox in the mail database this subscriber is associated with. You should never touch this value, it's read only from Unity's perspective.
- MailboxSendLimit. This value is only pulled from the directory in, it's value is not pushed out so you should never change it, it's read only from Unity's perspective. If you need to change this value for an Exchange back end you can use the Message Store Manager to accomplish that as well as the receive and warning limits below. The MSM utility uses CDO to make this change, by the way. The Unity directory monitors do not have the ability to fiddle with these values in the mailstore at this point.
- MailboxRecieveLimit. This value is only pulled from the directory in, it's value is not pushed out so you should never change it, it's read only from Unity's perspective.
- MailboxWarningLimit. This value is only pulled from the directory in, it's value is not pushed out so you should never change it, it's read only from Unity's perspective.
- ObjectChangedID. This value is used to keep track of who has the "newer" version of the user's information: the directory or the local SQL database. The only time you should touch this value is if you're trying to force the synch to pull in data from the directory into SQL for this user. You could set the value to 0 in SQL which would force the directory monitor to take the information in the directory and pull it in for that record since obviously 0 is going to be less than any change ID found in the directory. The reasons for doing this are rare but include disaster recovery scenarios. Outside of that you should never be fiddling with this value in the database.
- PrimaryFaxNumber. This is the value stored in the "Fax Number" field in Exchange and it shows up as the "Fax ID" value in the SA. If you change it in the database you need to synch it through to the directory or the change will be lost.
- RecipientEmailAddress. This maps through to the AddressId in SQL and is read only from Unity's perspective.
- RemoteAddress. The only time you would be editing this value in SQL would be for internet subscribers (AMIS, Bridge, VPIM and SMTP subscribers) and even then only if you know what you're doing. You should never touch this for full subscribers.
- SID. You should never be fiddling with this value in SQL at all. This is what's passed through to us from IIS when a user attempts to gain access to the web based system administration consol or the PCA or the like. This is read only from Unity's perspective.
- SIDHistory. This value is read only and should not be fiddled with in SQL. If you need to map an alternate directory SID to a Unity subscriber account you can do this in the Credentials table in SQL using the GrantUnityAccess command line tool if necessary.
- SMTPAddress. The full SMTP address associated with the subscriber. This is usually generated by the mail server automatically upon user creation. You shouldn't be changing this value in SQL, it's read only from Unity's perspective.
- VoiceName. If you change the recorded voice name of a subscriber (or remove it) you need to synch that change through to the directory or it will be lost.
- XferString. If you change the "Extension" value on the Alternate Contact rule associated with the subscriber's primary call handler, that change should write through to the "XferString" column on the subscriber via a trigger. If you're going to change the subscribers transfer string you need to do it on the alternate contact rule and let the trigger do the copy and then synch the change through to the directory. If you just change the XferString directly it wont have the desired effect. This value is used for cross box transfers when servers are in the same dialing domain.
So the short story is there are 9 properties you may be changing on subscribers that will require you request the SQLSyncSvr to push the information into the directory for you:
alternate extensions, primary extension, display name, first name, last name, list in directory, primary fax number, voice name and transfer string. You'll want to keep this list handy somewhere since you'll want to be careful to force a synch when necessary but not otherwise. Doing a synchronization to the directory is time consuming stuff and if you're editing properties on users that don't need to be updated in the directory, you don't want to ask for the synch.
The next thing you should be concerned about is how you go about editing properties associated with subscribers. As we've noted throughout, whenever doing adds and updates you should always use stored procedures if you can. Information about subscribers is, of course, stored in several tables as you'll know since of course you've carefully read the data object model chapter. As such I wont belabor the topic here other than to note the list of stored procedures necessary to edit values in all the tables you need for subscriber information:
Sp_ModifyCallHandler
Sp_ModifyContactRule
Sp_ModifyMenuEntry
Sp_ModifyNotificationDevice
Sp_ModifyNotificationMWI
Sp_ModifySubscriber
Sp_AddAltDTMFID
Sp_RemoveAltDTMFIdsByOid
The Alternate DTMFAccessID values don't require you edit the table so much as add and remove rows from the table so there's a pair of stored procedures necessary for that. Be aware, however, that the sp_RemoveAltDTMFIDsByOID will remove all alternate Ids for the subscriber you identify by their ObjectID. There's currently no stored proc to remove or edit a standing alternate ID. In versions of Unity prior to 4.0(3) you'd change the primary extension of the subscriber on the DTMFAccessID column of the primary call handler associated with that subscriber and SQL triggers would take care of pushing the updated value into the DTMFAccessID table as necessary. For Unity 4.0(3) that column only exists in the view for backwards compatibility. Regardless, the sp_ModifySubscriber stored procedure allows you to update the primary extension, it'll take care of doing the dirty work on the back end for you.
The keen observer will notice one important omission here. There is no sp_ModifyMessagingRule to update greetings for subscribers. There is a sp_SetMessagingRule but I seriously dislike that stored procedure since if it doesn't find the rule you asked it to find it creates one on the fly! This is very bad practice indeed since you don't want to be creating new messaging rules unless you're writing the setup scripts for Unity. Also, it doesn't contain all the properties for messaging rules that you may need to edit. This is an unfortunate state of affairs but unavoidable until the release of 4.0(4) can address this. For now we will have to edit the messaging rule tables directly through raw table access. I will give you a note for your teacher to do this for now but when 4.0(4) releases you'll definitely want to change over to using the stored procedure since as I mentioned earlier, changes in the database architecture will be phased in over the next few 4.0(x) releases.
I'm not going to exhaustively cover every possible property on subscribers that you can change, you should be able to extrapolate what you need here and use the Data Object Model information to run down which table has the information you want to twiddle with. I'll cover a couple examples here and you can check out the Edit Subscriber sample project on the code samples page of www.CiscoUnityTools.com for more.
Add an alternate extension to a subscriber
The process of adding an alternate extension is pretty easy. You just need the SubscriberObjectID value of the user you want to add it for and the DTMFID you want to add and you call the sp_AddAltDTMFIdByOID stored procedure. However, you need to be aware of a few pitfalls here.
First, it's up to you to limit the number of alternate extensions to 9. If you add more than 9 the SA pages that show this information will not be happy.
Second, and more importantly, there are no defined "slots" for alternate extensions to live in. They are all carted around the directory in an unsorted "blob" of data that is unpacked by the directory monitor and stuffed into the DTMFAccessID table. As such you can't, say, make a company wide policy that alternate extension "4" is going to be used for cell phones and alternate extension "7" is everyone's fully qualified 10 digit North American Dialing Plan ID. This would be really handy in some situations and is something that will be provided in future releases however for now you have to treat alternate extensions as an unsorted bag of Ids. Once you add an ID for a large number of users in bulk, for instance, there's no easy way to go change that ID for everyone since it's difficult if not impossible to identify it as opposed to Ids users may have had included for their home phone or cell phone or the like. Unity 4.0(4) will rectify this by introducing "named" alternate extensions that can be edited by name. Just keep this in mind before tearing off down a path where you start slamming alternate extensions in for folks left and right. You may create a mess if you're not careful.
Finally, it's up to you to make sure the alternate extension does not conflict with any ID in the dialing domain. Earlier in this chapter we covered a couple ways to do this depending on which version of Unity you're running. Just be aware the stored procedure for adding alternate extensions does not make such checks for you and will happily hand you a gun and let you shoot yourself in the foot. Don't do that.
So, to actually do the add is fairly straight forward stuff. In this example I assume you already have the SubscriberObjectID of the subscriber you want to add the ID to and, of course, the DTMFID you want to add that you've already checked meets the requirements: it's all digits, less than 30 characters long and doesn't conflict with any other ID in the dialing domain. See the Subscriber Create section for details on how to check for ID uniqueness.
Dim oCommand As ADODB.Command
Set oCommand = New ADODB.Command
oCommand.ActiveConnection = strConnectionString
oCommand.CommandType = adCmdStoredProc
oCommand.CommandText = "sp_AddAltDTMFID"
'yes, strGUID is not a very descriptive parameter name. This is the SubscriberObjectID of the subscriber you want to add the alternate extension for.
oCommand.Parameters.Item("@strGUID") = oCommand.CreateParameter("strGUID", adGUID, adParamInput, , strSubscriberObjectID)
'ParentOBjectIDType for subscriber is 1. Refer to CUDLE.
oCommand.Parameters.Item("@nParentObjType") = oCommand.CreateParameter("nParentObjType", adInteger, adParamInput, , 1)
'The actual ID you want to associate with this user
oCommand.Parameters.Item("@strAltID") = oCommand.CreateParameter("strAltID", adVarChar, adParamInput, , Trim(txtNewExtension.Text))
oCommand.Execute
There is no synchronization options for this stored procedure, however as noted above this is one of the values you need to make sure is written through to the directory. You can either call the sp_SqlDirectorySync stored procedure directly or the sp_ModifySubscriber stored procedure which takes a set of synchronization flags just as the sp_CreateSubscriber stored procedure does and is a much better choice for this since it'll only synchronize information about the user you're updating where as the sp_SqlDirectorySync is targeted more at batch operations where it will synch groups of users. If you're going to be making other changes to the subscriber while you're in ere the sp_ModifySubscriber stored procedure is an ideal choice but I'd recommend using it regardless. If you just want to kick of a sync for the user, the code would look like this:
oCommand.CommandText = "sp_ModifySubscriber"
oCommand.Parameters.Item("@SubscriberObjectID") = oCommand.CreateParameter("SubscriberObjectID", adGUID, adParamInput, , strSubscriberObjectID)
oCommand.Parameters.Item("@DirectorySync") = oCommand.CreateParameter("DirectorySync", adInteger, adParamInput, , 1)
'We want to pass in a sync mask that synchs the just the subscriber here. That's a value of 1 which is the default but it's always good to pass it in explicitly anyway.
oCommand.Parameters.Item("@DirectorySyncFlags") = oCommand.CreateParameter("DirectorySyncFlags", adInteger, adParamInput, , 1)
'We need to create a GUID that we can pass into the stored proc such that we can look for it in the DirectorySyncTicket table in SQL which is used to pass back the results of this sync - we have to use an asynchronous process here since the synch with AD can take a while and doing a blocking call for such things is not a good idea. See below where we wait for the ticket to get inserted into the table and let us know how things went. If you don't pass a synch ticket here, no record is created in the DirectorySyncTicket table.
strSyncTicket = generateDirectorySyncTicket()
oCommand.Parameters.Item("@DirectorySyncTicket") = oCommand.CreateParameter("DirectorySyncTicket", adGUID, adParamInput, , strSyncTicket)
'Let the stored procedure call rip.
oCommand.Execute
You can then wait for the ticket to be inserted into the DirectorySyncTicket table just as we did in the Create Subscriber section above by using the WaitforSyncToFinish. Yes, the chances of a sync failure here are much less likely than in creating or importing subscribers but if there are problems with the directory connection or the rights of the directory facing account for the Unity services or the like, this can fail on you so it's always a smart move to be good citizens and make sure your directory write went through went OK.
Update everyone's "7" key rule to go directly to the opening greeting
Let's just make up a goofy example here. Say for instance your company has decided that they want the "7" user input key on all subscribers (not application call handlers) configured to go to the opening greeting. You could, of course, apply this same process to setting subscriber's 0 keys to map to local area operators for groups of users or similar types of user input settings. We'll be grabbing the CallHandlerObjectID for the opening greeting and then using that value to update the by now familiar set of Action, Conversation and Destination settings for the "7" user input key on each subscriber in the system. The code for this follows:
Dim rsMenuEntries As New ADODB.Recordset
Dim rsTemp As New ADODB.Recordset
Dim strSQL As String
Dim strOpeningGreetingObjectID As String
Dim oCommand As ADODB.Command
'First, try to find the opening greeting call handler by its alias. This call handler should always be there, if it's not the system will likely have all kinds of interesting problems.
strSQL = "SELECT CallHandlerObjectID FROM CallHandler WHERE Alias='OpeningGreetingch'"
'if the opening greeting call handler can't be found, exit out of the function
rsTemp.Open strSQL, strConnectionString, adOpenKeyset, adLockReadOnly
If rsTemp.RecordCount <> 1 Then
MsgBox "Error could not find opening greeting call handler by alias"
Exit Sub
End If
'Grab the CallHandlerObjectID off the opening greeting call handler – we'll be using this value to update the menu entry values for all the subscribers later.
strOpeningGreetingObjectID = rsMenuEntries("CallHandlerObjectID")
'This query will snag all the subscriber's user input keys for the "7" key. Remember to be a good SQL citizen and only get the columns we need for this operation.
StrSQL=" SELECT vw_MenuEntry.MenuEntryObjectID
FROM vw_MenuEntry INNER JOIN vw_CallHandler
ON vw_CallHandler.CallHandlerObjectID=vw_MenuEntry.ParentObjectID
INNER JOIN vw_Subscriber
ON vw_Subscriber.CallHandlerObjectID=vw_CallHandler.CallHandlerObjectID
WHERE vw_MenuEntry.Alias='7'"
'We'll be using stored a stored procedure for updating the values on the user input table so we'll be opening this recordset as read only.
rsMenuEntries.CursorLocation = adUseClient
rsMenuEntries.Open strSQL, strConnectionString, adOpenKeyset, adLockReadOnly
'Setup the command connection for the stored procedure we'll be using to update the menu entry rows.
Set oCommand = New ADODB.Command
oCommand.ActiveConnection = strConnectionString
oCommand.CommandType = adCmdStoredProc
oCommand.CommandText = "sp_ModifyMenuEntry"
'Now iterate through all the rows in the table and update the Menu Entry value as needed.
rsMenuEntries.MoveFirst
Do While rsMenuEntries.EOF = False
'Pass in the ObjectID of the menu entry row we want to update
oCommand.Parameters.Item("@MenuEntryObjectID") = oCommand.CreateParameter("MenuEntryObjectID", adGUID, adParamInput, , rsMenuEntries("MenuEntryObjectID"))
'The "goto" action is 2 – check CUDLE for other otpions
oCommand.Parameters.Item("@Action") = oCommand.CreateParameter("Action", adInteger, adParamInput, , 2)
'Setting the conversation name to PHTransfer here sends the caller to contact rules (transfer rules) for the openeing greeting call handlers. By default the openeing greeting doesn't have any transfer rules enabled but if you decided later to do that you could.
oCommand.Parameters.Item("@ConversationName") = oCommand.CreateParameter("ConversationName", adVarChar, adParamInput, , "PHTransfer")
oCommand.Parameters.Item("@DestinationObjectID") = oCommand.CreateParameter("DestinationObjectID", adGUID, adParamInput, , strOpeningGreetingObjectID)
'The Object type for a call handler is 3. Check CUDLE for more details.
oCommand.Parameters.Item("@DestinationObjectIDType") = oCommand.CreateParameter("Action", adInteger, adParamInput, , 3)
'Let the stored procedure fly. There's no sync required for this and no need to check to see if it "worked", just move right on to the next item.
oCommand.Execute
rsMenuEntries.MoveNext
Loop
Of course in a production application you'd want some sort of logging function and a nice progress indicator for your always-stressed-out users and good stuff like that. However what you're looking at here is the basic engine behind applications such as BulkEdit.
Update subscriber's password
Setting the password is easy going through the stored procedure since it will take care of hashing your raw phone password into an MD5 string for you on the back end. MD5 strings should always be exactly 32 characters here. Since phone passwords can only be 20 digits in length, the stored procedure assumes that if the string is 32 characters long it's already been hashed into an MD5 string and will pass it through directly. Otherwise it'll hash it for you on the back end so you can simply pass through the raw digits for the phone password you want to set and it'll take care of it for you. If you're paranoid you can also hash the phone password into it's MD5 string and then pass that into the stored procedure instead. It'll handle it either way.
When setting the password for a subscriber it's up to you to be sure it complies with the password policy for the site. If you pass in a blank string for the password, for instance, the phone password will be cleared. You can check the phone password policies in the PwPolicy table in UnityDB. The stored procedure does not do any enforcement of the site's policy, so make sure you're careful here.
oCommand.CommandText = "sp_ModifySubscriber"
oCommand.Parameters.Item("@SubscriberObjectID") = oCommand.CreateParameter("SubscriberObjectID", adGUID, adParamInput, , strSubscriberObjectID)
'The phone password is passed in as clear text in this example - it gets crunched and encrypted by the SP during the subscriber update process. You can pass in a "pre hashed" MD5 string yourself – if the string is exactly 32 characters long the stored proc will pass it through "as is".
oCommand.Parameters.Item("@PWDTMF") = oCommand.CreateParameter("PWDTMF", adVarChar, adParamInput, , txtPhonePassword.Text)
'Passwords are not written through to the directory, no need to synch.
oCommand.Parameters.Item("@DirectorySync") = oCommand.CreateParameter("DirectorySync", adInteger, adParamInput, , 0)
'Let the stored procedure call rip.
oCommand.Execute
Since phone passwords are not pushed into the directory there's no need to issue a synchronization request for this. You're done.
Update the Error Greeting to Go back to itself
Just because it's the ugly duckling here without a stored procedure to work with, we'll cover editing the greeting for a subscriber such that the Error greeting is configured to loop back to itself instead of going to the opening greeting which is the default behavior. This means that if a user is listening to a greeting for that subscriber and "fat fingers" an entry, they'll hear the usual "I'm sorry, I did not hear that entry" system greeting but will then hear the subscriber's greeting start over instead of being tossed to the opening greeting call handler.
Say for instance we wanted to change the error greeting here for the Example Subscriber account. We'd construct an SQL Query that snagged the items we needed to update from their Error messaging rule along with the CallHandlerObjectID for the primary call handler associated with that subscriber. The messaging rules reference the primary call handler of a subscriber as their parent, not the subscriber itself.
StrSQL="SELECT vw_CallHandler.CallHandlerObjectID, vw_MessagingRule.Action, vw_MessagingRule.ConversationName, vw_MessagingRule.DestinationObjectID, vw_MessagingRule.DestinationObjectIDType
FROM vw_CallHandler INNER JOIN vw_Subscriber
ON vw_Subscriber.CallHandlerObjectID=vw_CallHandler.CallHandlerObjectID
INNER JOIN vw_MessagingRule
ON vw_MessagingRule.ParentObjectId=vw_CallHandler.CallHandlerObjectID
WHERE vw_Subscriber.Alias='esubscriber' AND vw_MessagingRule.Alias='Error'"
Set rsGreetings New ADODB.Recordset
rsGreetings.CursorLocation = adUseClient
rsGreetings.Open strSQL, strConnectionString, adOpenKeyset, adLockOptimistic
'In this case we were looking for one specific user and if we didn't get that, bail out.
If rsGreetings.RecordCount <> 1 Then
MsgBox "Could not find subscriber by alias"
Exit Sub
End If
'You can check CUDLE for explanations for all 4 columns here. We're going to use the "GoTo" action of 2 and send the call to the PHGreeting entry point for the primary call handler of this subscriber. The ObjectIDType for a call handler is 3.
rsGreetings("Action") = 2
rsGreetings("ConversationName") = "PHGreeting"
rsGreetings("DestinationObjectID") = rsGreetings("CallHandlerObjectID")
rsGreetings("DestinationObjectIDType") = 3
rsGreetings.Update
If you wanted to make this change for all subscribers and call handlers in your system instead of for an individual user, you could just take out the "vw_Subscriber.Alias='esubscriber'" from the WHERE clause and get a full list of all Error greetings and then just iterate through them making the same change as above:
rsGreetings.MoveFirst
Do While rsGreetings.EOF = False
rsGreetings("Action") = 2
rsGreetings("ConversationName") = "PHGreeting"
rsGreetings("DestinationObjectID") = rsGreetings("CallHandlerObjectID")
rsGreetings("DestinationObjectIDType") = 3
rsGreetings.Update
rsGreetings.MoveNext
Loop
Piece of cake. Since there's no synchronization issues with the directory for messaging rules you're in and out pretty quick here. Just remember, when 4.0(4) hits the streets and the sp_ModifyMessagingRule stored procedure is available, you'll want to make sure to switch over to using that.
Batch Subscriber Operations
There's actually nothing much special about doing bulk subscriber imports, creates or edits. You can, in fact, do large numbers of all three operations using the techniques outlined above. If you call for individual user synchs to the directory on each subscriber you create, for instance, the operation wont really take any longer than if you "save up" your synch till the very end and synch everyone all at once assuming you're moving on to the next user after creating/importing them and not waiting for their sync to complete. If you're importing many hundreds of users at a crack you may notice a small increase in performance doing it in batch but not enough to justify using a different method just for that.
The one exception here is synchronizing public distribution lists. Unity currently does not provide a way to synch a single user into a specific distribution list or set of lists in the directory. When you select to synchronize public distribution lists when adding a subscriber to that list it does all members for the list. Worse, if you select to include distribution lists in the synch flags when adding a new subscriber to the system, it'll synch all users for all lists. This can be very time consuming and if you issue such a request for each user you import in a large set, it may take many hours to complete all of them. This is not a good idea and if you can you should issue a single synch request for distribution lists after you've completed the import or creates of all subscribers in a batch to minimize this.
For adding or importing large numbers of users, doing it in batch mode does make a couple things other than the distribution list issue somewhat nicer. For instance you can provide an overall synchronization progress indicator for your users fairly easily. If you synch each individual user as you add or import them it's up to you to maintain the table of synch tickets and know when they're all complete and providing progress on this is tricky at best since you have to assume other folks may be requesting synch tickets for their various tasks as well.
Let's say you're adding a number of new users as Unity subscribers from a CSV file and having them created in the directory as well. One way to do this would be to add them to SQL and but not request a sync for each user as we did in the create subscriber example above. Simply don't pass the DirecotrySync, DirecotrySyncFlags or DirectorySyncTicket flags into the stored procedure. Then when you're all done adding users, issue a command to synchronize new subscribers and public distribution lists using the sp_SqlDirectorySync stored procedure. This stored procedure is nice since it provides explicit flags for indicating if you want to sync subscribers, distribution lists, new users only or the like instead of having to construct the flags manually as we do in the sp_CreateSubscriber stored procedure. The code for constructing this procedure would look like this:
oCommand.CommandText = "sp_SqlDirectorySync"
oCommand.Parameters.Item("@SyncSubscriber") = oCommand.CreateParameter("SyncSubscriber", adInteger, adParamInput, , 1)
oCommand.Parameters.Item("@SyncDistributionList") = oCommand.CreateParameter("SyncDistributionList", adInteger, adParamInput, , 1)
'This will tell the synch process to only look at rows in the subscriber table that have a NULL DirectoryId column which indicates they don't have an object associated with them in the directory. We assume these are "new" users waiting to be created.
oCommand.Parameters.Item("@SyncNewOnly") = oCommand.CreateParameter("SyncNewOnly", adInteger, adParamInput, , 1)
'Again we force it to only attempt to create new users in this process. If a user with the same mail alias is encountered in the directory already, the sync of that user will fail.
oCommand.Parameters.Item("@SyncCreateOnly") = oCommand.CreateParameter("SyncCreateOnly", adInteger, adParamInput, , 1)
'As we did in the individual create example we generate a GUID and pass it in such that we know what ticket to look for in the DirecotyrSyncTicket table so we know when the synchronization process is complete.
strSyncTicket = generateDirectorySyncTicket
oCommand.Parameters.Item("@DirectorySyncTicket") = oCommand.CreateParameter("DirectorySyncTicket", adGUID, adParamInput, , strSyncTicket)
You'd then execute the stored procedure and wait for the synch ticket to arrive in the DirectorySyncTicket table just as we did in the individual create example earlier in this chapter. You may want to check less aggressively since every 2 seconds is probably a little much for a large sync. Perhaps checking every 30 seconds or something in that range would be more appropriate.
So, how do you go about showing progress here while your waiting for the synch process to complete? You can't check the progress of the distribution list sync, unfortunately, but you can see how many users have been added and how many are left to be added easily. You can do this by querying the subscriber table for the DirectoryId column being NULL or not. If a value is NULL that user has not been synch to the directory yet or it's failed for some reason. Assuming you don't have a significant number of users that fail the synch process you can check for how many rows have a NULL DirectoryID column and update a progress bar as you go. For example, before starting the subscriber add process, execute this query:
SELECT Count(*) FROM vw_Subscriber WHERE DirectoryID IS NULL AND SubscriberType NOT IN (0,6)
And save this number as your "base count". There really shouldn't be any subscribers in the table that have NULL DirectoryIDs other than the installer account (type 0) and if you get anything other than 0 here you should investigate. But be that as it may, after you finish adding users to your subscriber table issue the same query and the difference of the two values will be how many subscribers you've added to the table that are waiting to be synched to the directory. I assume here that your process is the only one afoot doing such adds, if that's not a good assumption you'll need to use a different technique here.
Once you issue the synchronization process outlined above you can periodically issue the same query and update a progress bar or a rolling counter or the like to show basic subscriber synchronization progress. As noted, however, if a user fails to synch they're left at null so your progress may never get to 100%. Also, there is no way to check for progress on the distribution list synchronization, however the synch ticket wont show up until that's complete so you may sit there for a while waiting for this. One way to get around this is to issue two separate sync requests. One for just subscribers where you pass in a synch ticket and wait while showing progress as discussed and another one for just distribution lists where you don't bother to pass a synch ticket and just let it run in the background until complete.
Once the synchronization ticket for the subscriber sync shows up you can issue another query against the subscriber table and get a full list of all the users that did not get synched properly. This can be used to generate a report for the user and also for deleting those rows out of the subscriber table since you don't want to leave unsynced users in the table sine they're not valid subscribers. For instance this query should work nicely:
StrSQL="SELECT Alias, DisplayName, FirstName, LastName, SubscriberObjectID FROM vw_Subscriber WHERE DirectoryID IS NULL"
RsTemp.Open strSQL, strConnectionString, adOpenKeyset, adLockOptimistic
You can then iterate through the rsTemp recordset and dump user readable information out about which users failed to synch properly and then use the sp_DeleteSubscriber stored procedure shown in the Add Subscriber example to remove these rows from the table.
When importing vs. creating new users you may be passing in the DirectoryId value to find users in the directory by which means you can't use the NULL value in that column as we did for the create example above. Another technique is to use the ObjectChangedID value which for objects that have not been synchronized with the directory will be 0. If the value is greater than 0 you can assume the sync has taken place for that user.
For batch updates, as opposed to adds, there's really no easy way to make a bunch of changes in SQL, issue a synchronization request and then check for progress. You just have to wait until it's complete. When making edits in batch there's only a couple pieces of advice I have for you. First, if you don't have to synchronize to the directory, don't. Keep track of what values are being edited and be smart about sending the synch flag in. Second, if you have to synchronize public distribution lists do it once at the very end of your update process. Issuing a bunch of DL sync requests over a batch operation will result in very long delays for the sync to complete.
Delete Subscriber
Compared to the complexity of creating or importing a subscriber into Unity, deleting a subscriber properly without introducing any database corruption in the form of broken links is an order of magnitude higher on the difficulty scale. It demands you understand all the ways objects in Unity can reference a subscriber and how to accommodate the fact the subscriber is going away. The really tricky part is collecting information about what to do with these references. For instance if a subscriber is the owner and message recipient on a number of call handlers, you have to replace that with another valid subscriber or distribution list or you'll introduce corruption.
Yes, even the casual observer will note that the Unity web based system administration interface happily toasts subscribers, call handlers and other objects without a care in the world about the havoc that may wreak on the database. This has kept me churning out updated versions of dbWalker to help find and patch up these links. The Global Subscriber Manger does provide for deleting users from Unity without potentially breaking such links but folks in the field don't tend to use that capability a lot either because they prefer the web interface or they don't realize the capability is there. There are a lot of reasons why the SA is in the condition it is now, but mostly it's a result of the fact that it was built on top of Unity 2.x which did not have a relational database under it at the time and so the job of rummaging through the database and finding all references to an object was slow and painful. The SA was not redesigned when the Unity back end went to SQL and now looks a little silly around the edges for such things. The administration interface is undergoing a major rearchitecture to remove its dependency on the DOH, provide a much improved interface and which will hopefully support user extensible "plug in" pages so folks can do their own thing. That's all part of project Kubrick which at the time of this writing is not execute committed and there's no schedule in place for it. That said, it is in the works. Honest.
So why should you do a better job than the SA? Why not just blow away the subscriber, call a resynch and let the chips fall where they may? Because you're better than that. If you're spending the time to read through this material and understand the Unity object model you're clearly a cut above the rest. Painting with a broad brush I can say with some amount of certainty that you're more productive, easier to work with, smarter and better looking that most of the folks you have to associate with. As a members of civilization's top tier you have a certain responsibility to lead the way for the rest of the unwashed masses. I realize it's a heavy burden to bear, but you're up to the task.
So, what's involved in this process? First and foremost it's gathering from the user what to do with all possible references to the subscriber being removed. This extends to the removal of any object, of course, but for subscribers it's particularly important since they can be references as message recipients. Unity will not load a call handler or interview handler that does not have a valid message recipient assigned to it even if that handler is not configured to take a message. You'll get the ever-popular "failsafe" message that tells you to try your call again later and then dumps you to the opening greeting. Callers tend not to appreciate this behavior a whole lot and you want to avoid that in your Unity adventures. The next question, of course, is what are all the ways a subscriber can be referenced by another object? Oh, let me count the ways:
- They can be an message recipient on a call handler or interview handler.
- They can be an administrator (owner) on a call handler, interview handler, public distribution list or a name lookup handler.
- The after greeting action on a messaging rule can send the caller to the removed subscriber.
- The after message action on a call handler can send the caller to the removed subscriber
- A menu entry key associated with a call handler or a subscriber's primary call handler can be set to go to the greeting or transfer rule entry point for the removed subscriber.
- The exit action from the subscriber conversation (introduced in 4.0(1)) could send the caller to the removed subscriber.
- The four exit actions from a name lookup handler can each reference the removed subscriber.
- A call routing rule may reference the removed subscriber
- They could be referenced by subscriber templates for the subscriber exit destination, after message destination, after greeting destination or a caller input destination.
Dealing with all these possibilities when removing a user form the system is a daunting task. Fortunately for items 2 through 8 there is a stored procedure that simplifies the process a whole lot. For item #1 on the list there are two separate stored procedures to help you replace all owners and recipient references in the system. As for item #9, I don't deal with it in my deletion example since it's an extremely rare case that sites will have specific subscribers referenced as destination points in their subscriber templates. You can, of course, deal with this possibility in your applications if you like but the stored procedure mentioned above does not include templates in it's sweep of the database.
As noted, the hard part is collecting information about what to replace links for items 1 through 8 with when removing the user. If you take a quick look at the SA you'll see there are a lot of possibilities for setting a user input key and after message options and the like. The Global Subscriber Manager has an example of one way to approach this which is a full wizard interface that interrogates the user making the deletion about what they wish to do with each and every possible link that will break. While this is very robust it's also a lot of work and users are less than thrilled with having to walk through this wizard each time they delete a user. You can take it a step further and "remember" the previous set of values and let the user skip the wizard, however you need to be careful that references selected by the user and saved are valid. For instance if they've selected a public distribution list as a replacement for the message recipient link on removed subscribers and that distribution list is removed, you need to note that and force them to select another object. Another approach is to simply hard code as many of the replacements as you can and ask only for a subscriber or distribution list as the owner and recipient replacement. For instance all one key actions that referenced the deleted subscriber could be set to "ignore", after message actions could be set to "hang up" and the like. This is certainly easier to code and involves less dancing with the administrator but may produce less than desirable phone behavior – callers tend to frown on getting complimentary dial tone right after leaving a message when they wanted to now try another user in the system.
How you approach the "replacement information collection" issue is up to you and depends on the type of application you're providing. An automated provisioning application that runs off scripts doesn't have the option of asking a human such questions on the fly so you'll end up having to go with some predefined hard coded values. Other types of tools have other options. I didn't spend a whole lot of time in the example application here gathering information from the user. I took a bit of a "middle of the road" approach where some items were hard coded and a few items were adjustable but with a limited set of options. For instance the after message action can only be sent to a selected call handler. I did not give the full range of options available for this in an effort to keep the application as simple as possible. Given the information covered in the Object Model chapter and the data available in CUDLE you should be able to apply the full range of options to your application if that's what you decide you want to do.
Enough with the shameless pandering and blathering preamble here. Let's walk through part of the code found in the Delete Subscriber example application and take a look.
Private Sub DeleteSubscriber()
Dim rsCreate As ADODB.Recordset
Dim rsTemp As ADODB.Recordset
Dim oCommand As ADODB.Command
Dim strSyncTicket As String
Dim iCounter As Integer
Dim strDeletedSubscriberObjectID As String
Dim strNewOwnerRecipientObjectID As String
Set rsTemp = New ADODB.Recordset
'first, we need to fetch the SubscriberobjectId of the subscriber we want to delete which will get used later. The alias of the subscriber we're about to remove is stored in the txtAlias text box on the form here.
rsTemp.Open "select Alias, SubscriberObjectID from vw_Subscriber where Alias='" + txtAlias.Text + "'", strConnectionString, adOpenKeyset, adLockReadOnly
If rsTemp.RecordCount <> 1 Then
MsgBox "Error pulling the ObjectID for the subscriber to be deleted... aborting removal"
rsTemp.Close
Exit Sub
End If
rsTemp.MoveFirst
strDeletedSubscriberObjectID = rsTemp("SubscriberObjectID")
rsTemp.Close
'next we want to make sure we can get the SubscriberobjectId of the subscriber that will replace this guy as the owner and/or recipient which we'll be updating later. We do this now since it'd be a bummer to find out we can't find this replacement user after we cleaned up other links to this user with the spPreDeleteObject stored procedure. In this sample I only allow the selection of another subscriber to replace the removed user as both owner and recipient and the alias for this replacement user is stored in the txtOwnerRecipientSubscriberAlias text box on the form.
rsTemp.Open "select Alias, SubscriberObjectID from vw_Subscriber where Alias='" + txtOwnerRecipientSubscriberAlias.Text + "'", strConnectionString, adOpenKeyset, adLockReadOnly
If rsTemp.RecordCount <> 1 Then
MsgBox "Error pulling the ObjectID for the subscriber that will be the replacement as the owner/recipient... aborting removal"
rsTemp.Close
Exit Sub
End If
rsTemp.MoveFirst
strNewOwnerRecipientObjectID = rsTemp("SubscriberObjectID")
rsTemp.Close
'now we're ready to start the pre deletion activities.
Set rsCreate = New ADODB.Recordset
Set oCommand = New ADODB.Command
oCommand.ActiveConnection = strConnectionString
oCommand.CommandType = adCmdStoredProc
'first, do the pre delete actions and clean up references to this subscriber in other objects in the database before removing them such that we don't corrupt the database. Note that this stored procedure can also be used when removing other objects such as a call handler. This stored procedure handles fixing up all the links to the removed user except the owner and recipient links which we'll deal with shortly.
oCommand.CommandText = "sp_PreDeleteObject"
'Pass in the SubscriberObjectID of the user to be removed.
oCommand.Parameters.Item("@ObjectID") = oCommand.CreateParameter("ObjectID", adGUID, adParamInput, , strDeletedSubscriberObjectID)
'The object type is a subscriber which is "1", you can check CUDLE for this if you forget in the future.
oCommand.Parameters.Item("@ObjectType") = oCommand.CreateParameter("ObjectType", adInteger, adParamInput, , 1)
'In this case we are going to set the user input keys mapped to this subscriber to "ignore". I don't give the user a choice on the form here but, of course, you can do what you like here. I'm simply hard coding the action to ignore which maps to a value of "0". Again, check CUDLE under the MenuEntry table to see what your options are here. With a value of 0 for the action the conversation name, destination object ID and the destination object ID type values are meaningless and don't get used, however the stored procedure is still expecting them to be passed in regardless. As such we pass in some dummy values here to make it happy.
oCommand.Parameters.Item("@CallerInputAction") = oCommand.CreateParameter("CallerInputAction", adInteger, adParamInput, , 0)
'This value is not used in this case.
oCommand.Parameters.Item("@CallerInputConversationName") = oCommand.CreateParameter("CallerInputConversationName", adBSTR, adParamInput, , "PHTransfer")
'since a blank GUID is not considered valid and the stored proc requires this parameter, I'm just passing in the ObjectID of the guy we're deleting to the procedure here - this is not used by anything - this parameter should really be optional.
oCommand.Parameters.Item("@CallerInputDestObjectID") = oCommand.CreateParameter("CallerInputDestObjectID", adGUID, adParamInput, , strDeletedSubscriberObjectID)
'Arbitrarily pass in the type for call handler here (2) - again the stored proc demands a value here but it's not used in this case
oCommand.Parameters.Item("@CallerInputDestObjectIDType") = oCommand.CreateParameter("CallerInputDestObjectIDType", adInteger, adParamInput, , 2)
'For the after message action I allow the user to select a call handler - again, I don't offer a lot of options here but you can do what you like. We need to go fetch the ObjectID for the destination call handler which the user selected and has it's alias stored in the txtCallHandlerAfterMessageAlias text box on the form.
rsTemp.Open "SELECT Alias, CallHandlerObjectID FROM vw_CallHandler WHERE alias='" + txtCallHandlerAfterMessageAlias.Text + "'", strConnectionString, adOpenKeyset, adLockReadOnly
If rsTemp.RecordCount <> 1 Then
MsgBox "Error pulling the ObjectID for the call handler destination for after message destinations... aborting removal"
rsTemp.Close
Exit Sub
End If
rsTemp.MoveFirst
'Pass in the destination object ID for the after message replacement which is the call handler we just looked up.
oCommand.Parameters.Item("@AfterMessageDestObjectID") = oCommand.CreateParameter("AfterMessageDestObjectID", adGUID, adParamInput, , rsTemp("CallHandlerObjectID"))
rsTemp.Close
'The AfterMessageDestObjectIDType needs to be a "2" for a call handler in this case.
oCommand.Parameters.Item("@AfterMessageDestObjectIDType") = oCommand.CreateParameter("AfterMessageDestObjectIDType", adInteger, adParamInput, , 2)
'The conversation name is "PHTransfer" here - to do this right you'd give them the option of attempting the transfer or going right to the greeting for the call handler in question. Defaulting to PHTransfer is generally pretty safe since the handler itself will be configured to do a transfer or not the way you want them to regardless. If you don't know what I mean by PHTransfer vs. PHGreeting you need to review the Object Model and Audio Text Applications chapters.
oCommand.Parameters.Item("@AfterMessageConversationName") = oCommand.CreateParameter("AfterMessageConversationName", adBSTR, adParamInput, , "PHTransfer")
'The action for this needs to be "2" for "goto" - refer to CUDLE for more details
oCommand.Parameters.Item("@AfterMessageAction") = oCommand.CreateParameter("AfterMessageAction", adInteger, adParamInput, , 2)
'Again, I don't give any options for the after greeting action - it's hard coded to take a message if it happened to have been set to go to the subscriber being deleted. You can, of course, go wild here. The Action for "take a message" corresponds to a value of "4". Check CUDLE under the MessagingRule table to find out what all your options for the after greeting action are. With an action of "take message" it's not necessary to fill in the AfterGreetingConversationName, AfterGreetingDestObjectID or the AfterGreetingDestObjectIDType values, however you need to pass the parameters anyway because the stored procedure expects to see them even though they don't get used.
oCommand.Parameters.Item("@AfterGreetingAction") = oCommand.CreateParameter("AfterGreetingAction", adInteger, adParamInput, , 4)
'This value is not used in this case since an action of 4 does not use a conversation
oCommand.Parameters.Item("@AfterGreetingConversationName") = oCommand.CreateParameter("AfterGreetingConversationName", adBSTR, adParamInput, , "PHTransfer")
'since a blank GUID is not considered valid and the stored proc requires this parameter, I'm just passing in the ObjectID of the guy we're deleting to the procedure here - this is not used by anything - this parameter should really be optional.
oCommand.Parameters.Item("@AfterGreetingDestObjectID") = oCommand.CreateParameter("AfterGreetingDestObjectID", adGUID, adParamInput, , strDeletedSubscriberObjectID)
'Arbitrarily pass in the type for call handler here (2) - again the stored proc demands a value here but it's not used in this case
oCommand.Parameters.Item("@AfterGreetingDestObjectIDType") = oCommand.CreateParameter("AfterGreetingDestObjectIDType", adInteger, adParamInput, , 2)
'I lumped the subscriber exit destination and the name lookup handler exit destinations into one option and it's forced to go to a call handler to simplify things here. First we need to get the objectId of the call handler the user has selected as the alternative destination. It's alias is stored in the txtCallHandlerAfterMessageAlias text box on the form.
rsTemp.Open "SELECT Alias, CallHandlerObjectID FROM vw_CallHandler WHERE alias='" + txtCallHandlerAfterMessageAlias.Text + "'", strConnectionString, adOpenKeyset, adLockReadOnly
If rsTemp.RecordCount <> 1 Then
MsgBox "Error pulling the ObjectID for the call handler target for the exit destinations on subscribers and name lookup handlers... aborting removal"
rsTemp.Close
Exit Sub
End If
rsTemp.MoveFirst
'Pass the CallHandlerObjectID we just grabbed into the stored procedure as both the name lookup handler exit destination and the subscriber exit destination object Ids.
oCommand.Parameters.Item("@SubExitObjectID") = oCommand.CreateParameter("SubExitObjectID", adGUID, adParamInput, , rsTemp("CallHandlerObjectID"))
oCommand.Parameters.Item("@NameLookupHandlerExitObjectID") = oCommand.CreateParameter("NameLookupHandlerExitObjectID", adGUID, adParamInput, , rsTemp("CallHandlerObjectID"))
rsTemp.Close
'again we're forcing a call handler here and the objectType for that is "2"
oCommand.Parameters.Item("@SubExitObjectIDType") = oCommand.CreateParameter("SubExitObjectIDType", adInteger, adParamInput, , 2)
oCommand.Parameters.Item("@NameLookupHandlerExitObjectIDType") = oCommand.CreateParameter("NameLookupHandlerExitObjectIDType", adInteger, adParamInput, , 2)
'again, we'll assume the PHTransfer entry point here instead of the PHGreeting.
oCommand.Parameters.Item("@SubExitConversationName") = oCommand.CreateParameter("SubExitConversationName", adBSTR, adParamInput, , "PHTransfer")
oCommand.Parameters.Item("@NameLookupHandlerExitConversationName") = oCommand.CreateParameter("NameLookupHandlerExitConversationName", adBSTR, adParamInput, , "PHTransfer")
'The action for all transfers is "2" for goto - again, refer to CUDLE for help here.
oCommand.Parameters.Item("@SubExitAction") = oCommand.CreateParameter("SubExitAction", adInteger, adParamInput, , 2)
oCommand.Parameters.Item("@NameLookupHandlerExitAction") = oCommand.CreateParameter("NameLookupHandlerExitAction", adInteger, adParamInput, , 2)
'OK, let's let the PreDelete procedure rip.
rsCreate.CursorLocation = adUseClient
rsCreate.Open oCommand, , adOpenDynamic, adLockOptimistic
'the rsCreate recordset now contains a list of all the objects that were changed as part of this procedure. If you want to get fancy you can generate a report based on this information. I'm not getting fancy here so we're moving on.
rsCreate.Close
'We now need to make sure we change any call handler, interview handler or distribution list that's setup with their owner and/or recipient properties pointing at the subscriber we're going to remove. We'll replace references to this subscriber with the subscriber selected on the form that we looked up earlier in this routine. We need to use the sp_ChangeOwner and sp_ChangeRecipient stored procs for this.
oCommand.CommandText = "sp_ChangeOwner"
oCommand.Parameters.Item("@PrevOwnerObjectID") = oCommand.CreateParameter("PrevOwnerObjectID", adGUID, adParamInput, , strDeletedSubscriberObjectID)
oCommand.Parameters.Item("@NewOwnerObjectID") = oCommand.CreateParameter("NewOwnerObjectID", adGUID, adParamInput, , strNewOwnerRecipientObjectID)
'since we're hard coding this to only allow a subscriber to be the replacement we pass in a "1" as the type here. If you were allowing a public distribution list as a replacement you'd use a value of 2 here.
oCommand.Parameters.Item("@NewOwnerObjectIDType") = oCommand.CreateParameter("NewOwnerObjectIDType", adInteger, adParamInput, , 1)
'execute the stored procedure
rsCreate.CursorLocation = adUseClient
rsCreate.Open oCommand, , adOpenDynamic, adLockOptimistic
'The rsCreate recordset now contains a list of all the handlers updated by the stored proc. You can include this in a reporting option for your tools if you like. We're keeping this simple so I'm not going to go that route, we'll just move on.
rsCreate.Close
'now change the recipient in the same way we did the owner.
oCommand.CommandText = "sp_ChangeRecipient"
oCommand.Parameters.Item("@PrevRecipientObjectID") = oCommand.CreateParameter("PrevRecipientObjectID", adGUID, adParamInput, , strDeletedSubscriberObjectID)
oCommand.Parameters.Item("@NewRecipientObjectID") = oCommand.CreateParameter("NewRecipientObjectID", adGUID, adParamInput, , strNewOwnerRecipientObjectID)
'since we're hard coding this to only allow a subscriber to be the replacement we pass in a "1" as the type here
oCommand.Parameters.Item("@NewRecipientObjectIDType") = oCommand.CreateParameter("NewRecipientObjectIDType", adInteger, adParamInput, , 1)
'execute the stored proc.
rsCreate.CursorLocation = adUseClient
rsCreate.Open oCommand, , adOpenDynamic, adLockOptimistic
'Again, the rsCreate recordset contains a list of all the handlers updated by this stored proc which you can use for a reporting function here if you like.
rsCreate.Close
'now that we have the pre delete activity complete we can delete the subscriber themselves without worrying about causing any kind of broken links or other problems in the database unless the site is completely weird and has referenced this subscriber in their subscriber templates for whatever reason.
oCommand.CommandText = "sp_DeleteSubscriber"
oCommand.Parameters.Item("@SubscriberObjectID") = oCommand.CreateParameter("SubscriberObjectID", adGUID, adParamInput, , strDeletedSubscriberObjectID)
'You definitely want to tell the stored procedure to synchronize SQL to the directory for this subscriber after deleting them so you should pass a 1 in here. There's really no valid reason not to pass this in, frankly, but it's an optional parameter nonetheless. If you don't pass this flag in the user will be removed from Unity's database but will still be "stamped" as a subscriber in the directory and you will not be able to import them as a subscriber on any Unity server in the directory.
oCommand.Parameters.Item("@DirectorySync") = oCommand.CreateParameter("DirectorySync", adInteger, adParamInput, , 1)
'We want to pass in a sync mask that syncs the subscriber info back to the directory. You have the option of removing the subscriber from the directory if your back end is Exchange 55 or Exchange 2000 here. If the user has checked that option on the form you want to pass in 0x50000000 + 0x00000001 = 1342177281 decimal. If this option is not checked, just you need to pass in 0x20000000 + 0x00000001 = 536870913. You can also simply pass the "ForceRemove" flag into the stored procedure which will simply "or" in the 0x50000000 for you rather than making you do all that complicated math on your own. You must be extremely careful with this option! The SA does not offer this option for a very good reason. Folks get real annoyed if you blow away their directory and messaging accounts by accident so make sure you know what you're doing with this.
If Me.chkRemoveMailbox.Value = vbChecked Then
'pass the synch flags to remove the user from the directory and blow away their mailstore account as well.
oCommand.Parameters.Item("@DirectorySyncFlags") = oCommand.CreateParameter("DirectorySyncFlags", adInteger, adParamInput, , 1342177281)
Else
'pass the sync flags to remove just the subscriber properties from the user in the directory. This should definitely be the default option.
oCommand.Parameters.Item("@DirectorySyncFlags") = oCommand.CreateParameter("DirectorySyncFlags", adInteger, adParamInput, , 268435457)
End If
'We need to create a GUID that we can pass into the stored proc such that we can look for it in the DirectorySyncTicket table in SQL which is used to pass back the results of this sync - we have to use an asynchronous process here since the synch with AD can take a while and doing a blocking call for such things is not a good idea. See below where we wait for the ticket to get inserted into the table and let us know how things went. If you don't pass a synch ticket here, no record is created in the DirectorySyncTicket table.
strSyncTicket = generateDirectorySyncTicket
oCommand.Parameters.Item("@DirectorySyncTicket") = oCommand.CreateParameter("DirectorySyncTicket", adGUID, adParamInput, , strSyncTicket)
'Let the stored procedure call rip.
rsCreate.CursorLocation = adUseClient
rsCreate.Open oCommand, , adOpenDynamic, adLockOptimistic
'As we've done earlier, we need to wait for the sync to complete and check the results. You can check the code for the WaitForsyncToFinish in the Add Subscribers example.
If WaitForSyncToFinish (strSyncTicket) = False Then
MsgBox "Synch to directory failed during subscriber removal. Check the SQLSyncSvr logs in \Commserver\Logs for more details"
Else
MsgBox "Subscriber removed"
End If
BailOut:
'Clean up a little on the way out.
Set rsCreate = Nothing
Set oCommand = Nothing
End Sub
Remove Unity Properties from an AD object
I've had a number of folks ask how they can automate the "cleaning" process for users in Active Directory when, for instance, a Unity server was removed from the network without uninstalling it properly first. This "strands" users in the directory since they are stamped as Unity subscribers and other Unity servers will not allow you to import them. I should note here that using the sp_CreateSubscriber to import these users will let you do that – the stored procedure does not check to see if the user you identified is a subscriber already or not. This is why DiRT restores work when you reinstall a Unity server that's crashed into a directory with subscribers in the directory still.
However, if you need to clean the user of Unity properties you can do this fairly easily using code that looks a lot like navigating around databases. The Active Directory services can be bound to using ADO in much the same way you can bind to SQL. There are certainly other ways to find your way around AD and update objects and the like, however this is by far the easiest I've encountered. For this to fly, it requires you include the "Active DS Type Library" in your VB project which will give you the ADSI interface plumbing you need.
The first thing we need is to connect to the "root" of the Active Directory domain such that we can then find folders/users in that container and move down through the tree. The root container can be fetched using the "RootDSE" keyword in your query. You could go out and find your domain controller and build this yourself but it's easy to just ask AD to tell you what default domain you're running in just now.
Dim sContext As String
Dim rootDSE As IADs
'Get the rootDSE. This is basically the default container for the domain. It'll look something like this: "LDAP://DC=AnswerMonkey,DC=net".
Set rootDSE = GetObject("LDAP://RootDSE")
sNamingContext = "LDAP://" & rootDSE.Get("defaultNamingContext")
the sContext string will now contain a string you can use to go fetch users in containers of your choice. Now that you have the path to the root container, you can go build a recordset of all the user and/or containers in that container.
Dim con As New Connection
Dim rs As New Recordset
Dim sFilter As String
Dim sLdapQuery As String
'Open the connection using the special Active Directory connection string.
con.Provider = "ADsDSOObject"
con.Open "Active Directory Provider"
'Build the query. In this case we're only interested in seeing users in the container in question, not all the other stuff that you find handing out in an Active Directory container.
sFilter = "(objectClass=User)"
'If the user has opted to only see subscribers, include a filter that will only show users that have the ciscoECSBUUMLocationOBjectID value on them. Otherwise leave the filter off and all user objects will be returned. If a user in the directory does not have a location object Id associated with them, we assume they are non Unity subscribers.
If bShowAllUsers = False Then
sFilter = "(&" & sFilter & "(ciscoEcsbuUMLocationObjectId=*)" & ")"
End If
'Remember the sContext string constructed from the RootDSE above looks something like "LDAP://DC=AnswerMonkey,DC=net"
sLdapQuery = "<" & sContext & ">;" & sFilter & ";AdsPath,cn;onelevel"
'Get the users from the query into a nice recordset
Set rs = con.Execute(sLdapQuery)
At this point you have a list of all users in the container – you probably don't have users in the root container, however, so the recordset is likely empty. You adjust this by adding to the sContext string either on your own if you know the container you want to see or you can build it dynamically and do the tree control navigation thing by getting all the containers in the current container and letting the user navigate down to the point where they want to go. The query to get containers is very similar to the one to get users, you just provide a different filter.
'Build the query. We're only interested in seeing OUs and containers here so filter everything else out or else it'll show a huge clutter of stuff we don't want to see.
sFilter = "(|(objectClass=organizationalUnit)(objectClass=container))"
sLdapQuery = "<" & sContext & ">;" & sFilter & ";AdsPath,cn;onelevel"
'Get the result record set which contains all the containers and OUs in the passed in container
Set rs = con.Execute(sLdapQuery)
The recordset now contains all the containers found in the container path you passed it using the sContext string. To navigate to a sub container you just need to adjust the sContext string and reload the users and or containers in that container and so on. To do this all you need to do is iterate through the recordset returned and assign the "ADSPath" column value as desired. You can assign this to an Active Directory object and get at the GUID, class, path, name, parent and schema values on that node in AD if you wish. For instance if you had the above container recordset positioned on the Users container off the root you could do this:
Dim adsContainer As IADs
Set adsContainer = GetObject(rs.("ADSPath"))
MsgBox adsContainer.ADSPath
MsgBox adsContainer.Name
The first message box would spit out something like this "LDAP://OU=Users,OU=Unity,DC=AnswerMonkey,DC=com" and the second message box would show simply "CN=Users".
Using this mechanism you can easily build a tree control to let users navigate around the directory which is, in fact, exactly what the Remove Subscriber Properties example project does.
Once you've found the user you want to clean, you need to remove two properties from that object such that they can be imported into another Unity server: the ciscoEcsbuUMLocationObjectId and ciscoEcsbuObjectType properties. The sAdsPath used here is an LDAP string that identifies the user in the directory similar to what we used above. It'll look something like this: "LDAP://CN=JLindborg,CN=Users,DC=AnswerMonkey,DC=net". You can get it in the same way we got the ADSPath on the container above, by iterating through the recordset created with the users filter and populating a list for users to choose from or constructing it manually or whatever works for you.
Dim unityUser As IADs
Set unityUser = GetObject(sAdsPath)
unityUser.PutEx ADS_PROPERTY_CLEAR, "ciscoEcsbuUMLocationObjectId", vbNull
unityUser.PutEx ADS_PROPERTY_CLEAR, "ciscoEcsbuObjectType", vbNull
unityUser.SetInfo
At this point the Active Directory user is now "clean" and can be imported into any Unity server in the directory.
Adding a new public distribution list
A couple of important notes about working with distribution lists here before we dive into the code example. First, the stored procedures for dealing with public distribution lists were not added and working properly until the release of Unity 4.0(3). If you're running 4.0(2) or earlier you will not be able to create or remove distribution lists or add members to lists using SQL.
When I'm talking about "public distribution lists" I'm referring to "system distribution lists" that were dicussed in the Data Object Model chapter as opposed to the "scope distribution lists" that are used to bound user lists for the name lookup handlers. The csp_DistributionListCreate stored procedure discussed here can be used to either create a brand new distribution list or to import an existing distribution list from the directory.
The distribution lists stored procedures will all work with Exchange 5.5, 2000, 2003 and Domino, however there's a few things to keep in mind:
In AD (Exchange 2000,2003) new lists are created as new universal distribution lists in the container you selected in AD during the Unity installation. The account associated with the directory facing services must have rights to create distribution lists so if you restricted such rights during the installation of Unity you'll need to re run the permissions wizard and grant such rights or these scripts will fail, of course. When importing lists in AD you can include universal (distribution) or security groups, both will work just fine. When importing lists in AD you use the directory ID value from the object which corresponds to the ObjectGUID field. You need to convert this into a usable string which we covered in the Importing a Subscriber example earlier.
In the case of Domino new lists are created as Multipurpose groups. When importing with Domino, be sure to only import either Multipurpose or Mail groups. If you import an Access Control, Deny Access or Servers type group, strange things will happen since you can't send mail to those types of groups. Don't do that. When importing a list from Domino, pass in the value from the Unique Identifier (UUID) field into the DirectoryId parameter and you're good to go.
The keen observer will notice that there are stored procedures for creating scope distribution lists and adding members to them as well, however you'll also notice they lack many of the features found in the system distribution list procedures, most notably the directory syncronization flags. These scope distribution lists are, at the time of this writing, intended only for internal use by the directory monitors and should be avoided until they're cleaned up and ready for prime time.
OK, on with the exercises here. The creation of a new distribution list here is actually pretty easy. You only need to provide a display name, alias and the ObjectID of a subscriber or distribution list to act as the "owner" for the list. You can optionally pass in an extension as wel, but it's not required. If you're importing an existing distribution list from the directory you only need to pass in the DirectoryID and alias of that list as well as providing the ObjectID of the owner you're good to go. You can refer back to the importing subscribers example to see how to pull directory ID values off objects in the directory if you like, we'll only be covering the creation of a new list here.
'add a new public distribution list to the system
Private Sub cmdCreateList_Click()
Dim strDLAlias As String
Dim strDLDisplayName As String
Dim strAdministratorObjectID As String
Dim oCommand As ADODB.Command
Dim strSyncTicket As String
Dim rsTemp As ADODB.Recordset
Dim strText as String
'Collect the display name of the distribution list to be added. Since this is the string used to identify distribution lists in the SA interface you need to make sure it's unique. While not necessary from a database perspective, presenting the user with multiple lists named the same thing and expecting them to select the right one is not terribly friendly.
strDLDisplayName = InputBox("Enter display name for new public distribution list", "Enter distribution list display name")
'if the display name is empty bail out. We'll limit the display name here to 40 characters since the SA limits the input of the display name for DLs to 40 characters as well - this should be sufficient for our purposes here.
If Len(strDLDisplayName) = 0 Then
Exit Sub
ElseIf Len(strDLDisplayName) > 40 Then
MsgBox "Easy there Tex. Keep the display name under 40 characters please."
Exit Sub
End If
'Check to see if the display name is unique - technically the display name doesn't have to be unique but the SA only shows the display name as a selection criteria and there'd be no way to distinguish the lists apart - as such we'll enforce uniuqueness here.
Set rsTemp = New ADODB.Recordset
rsTemp.CursorLocation = adUseClient
rsTemp.Open "SELECT Alias, DisplayName FROM vw_DistributionList WHERE DisplayName='" + strDLDisplayName + "'", strConnectionString, adOpenKeyset, adLockReadOnly
If rsTemp.RecordCount > 0 Then
MsgBox "That display name conflicts with one or more public distribution lists already in the database. Please select a unique display name."
rsTemp.Close
GoTo CleanUp
End If
rsTemp.Close
'Generate an Alias from the display name - the SQL Syncher will tack on the system ID of the local Unity install for this so as long as the alias is unqiue within the set of public DLs we know about in SQL you can be pretty sure the alias is unqiue in the directory - if you want to be extra sure you can tack on the number of seconds since midnight on 1/1/2000 or something but it shouldn't be necessary. Regardless, if the alias is not unique the stored procedure will fail and return to you a description to that effect so it wont be the end of the world regardless.
strDLAlias = Replace(strDLDisplayName, " ", "_")
'We could get pretty sophisticated and involved checking for illegal characters in the display name, however I'm going to be heavily draconian here and only allow characters, letters, spaces and underscores. If you want to get fancy and let your users add various special characters (making sure you account for the differences in Exchange 5.5 and AD and Domino here) you can go wild. I leverage the handy "Like" operator here to evaluate the string quickly in one shot. I first remove the underscores from the alias here to make this easier and since the spaces were removed when constructing the alias we only need to check that all characters are letters or numbers here.
strText=Replace(strDLAlias,"_","")
If strText Like "*[!0-9,!a-z,!A-z]*" Then
MsgBox "The display name can contain only spaces, letters, numbers and underscores. No other characters are allowed"
Goto CleanUp
End If
'Finally, force the user to select a subscriber to act as the owner/administrator for this list. At this time this value is not actually used for anything other than display purposes in the SA - however eventually it will be and it's considered a database inconsistency if it's not set properly. You could also allow the user to select a distribution list for this, of course, but for our purposes here we'll just use a subscriber. The frmSubscriberSearch form exposes 3 public variables that include the ObjectId of the subscriber selected, their display name and a bCancelHit Boolean that indicates the user exited out of the form without selecting a user.
frmSubscriberSearch.Show vbModal
'yeah, this isn't the greatest user interface in the world but hey... for a production appliction I'd probably put this on a seperate form such that the display name and administrator and possibly an optional extension could be entered and checked seperately - for our purposes here, however, the user is just going to take it in the shorts if they screw up here and they'll have to start again.
If frmSubscriberSearch.bCancelHit or Len(frmSubscriberSearch.strObjectID) = 0 Then Then
MsgBox "You must select a subscriber to act as the administrator for the new list... please try again."
GoTo CleanUp
Else
strAdministratorObjectID = frmSubscriberSearch.strObjectID
End If
'force a redraw of the main form and then put the mouse into "I'm working on it" mode. For a production application I'd probably float a splash screen to the top indicating the DL was being created or the like - but for our purposes here this is fine.
frmMain.Refresh
frmMain.MousePointer = vbHourglass
DoEvents
Set oCommand = New ADODB.Command
oCommand.ActiveConnection = strConnectionString
oCommand.CommandType = adCmdStoredProc
'OK, we have everything we need - the alias and display name of the DL to create and the ObjectId of the subscriber that will act as the administrator - let's create the new DL.
oCommand.CommandText = "csp_DistributionListCreate"
oCommand.Parameters.Item("@Alias") = oCommand.CreateParameter("Alias", adVarChar, adParamInput, , strDLAlias)
oCommand.Parameters.Item("@DisplayName") = oCommand.CreateParameter("DisplayName", adVarChar, adParamInput, , strDLDisplayName)
oCommand.Parameters.Item("@AdministratorObjectID") = oCommand.CreateParameter("AdministratorObjectID", adGUID, adParamInput, , strAdministratorObjectID)
'tell the SQLSyncher to push this new DL into the directory. The synch Flag of 2 means just distribution lists here, of course. It's the default value and it's not strictly necessary to pass it in here but whenever forcing a synch I like to be explicit about the flags regardless.
oCommand.Parameters.Item("@DirectorySync") = oCommand.CreateParameter("DirectorySync", adInteger, adParamInput, , 1)
oCommand.Parameters.Item("@DirectorySyncFlags") = oCommand.CreateParameter("DirectorySyncFlags", adInteger, adParamInput, , 2)
'We need to create a GUID that we can pass into the stored proc such that we can look for it in the DirectorySyncTicket table in SQL which is used to pass back the results of this sync
strSyncTicket = generateDirectorySyncTicket
oCommand.Parameters.Item("@DirectorySyncTicket") = oCommand.CreateParameter("DirectorySyncTicket", adGUID, adParamInput, , strSyncTicket)
'Let the stored proc fly.
oCommand.Execute
'wait for sync process to complete. This is the same generic synch ticket wait routine used in the subscriber add routine, you can check the code details there if you like.
If WaitForSyncToFinish(strSyncTicket) = False Then
MsgBox "SQL Sync to directory failed when adding new distributionlist" + vbCrLf + "You can check the SQLSyncSvr logs in the \commserver\logs directory for more details"
Else
MsgBox "The directory sync is complete for new distribution list"
End If
frmMain.MousePointer = vbDefault
CleanUp:
'close out the recordsets and exit
On Error Resume Next
rsTemp.Close
Set rsTemp = Nothing
Set oCommand = Nothing
End Sub
Removing an existing public distribution list
Removing a public distribution list is very similar to removing a subscriber, however the potential for creating broken links in the database as a result of it's removal is considerably more limited. You do still need to worry about removing a list that is set as an owner or a message recipient of a handler and replacing the list you're removing with another user or list but that's about it. No other references to a public distribution list are allowed.
You have the option of either removing just the Unity properties off a distribution list and leaving it in the directory or using the "force remove" flag on the directory syncronization options to delete the object in the directory entirely. This is very similar to removing subscribers discussed earlier, however there's an important difference for public distribution lists that should be noted here. Unlike subscribers, distribution lists can be "shared" across multiple Unity servers that are installed into the same directory. As such if you remove a distribution list from the directory entirely or just remove the Unity properties off of it you can actually be yanking it out of other Unity servers. Once a distribution lists has it's Unity properties removed, all Unity servers will drop that list out of it's local database of system distribution lists (as noted earlier that's not the case for scope distribution list references). There's no real easy way to tell what other Unity servers in the directory may be affected by this (i.e. which servers may have used that distribution list as an owner/recipient on a handler object) other than connecting to each one remotely and checking. Not ideal. It's pretty unusual to delete distribution lists programmatically but just in case you find yourself in such a situation we'll cover the high points here.
The first thing to do is to go through the "pre delete" steps to see if any objects on the local Unity server are using this distribution list as an owner and/or message recipient and to provide a suitable replacement object. You can use the sp_ChangeOwner and sp_ChangeRecipient stored procedures to do this as was discussed in the Delete Subscriber example discussed earlier in this chapter. Once you've taken care of any potential reference issues there, the process of removing the distribution list is straight forward.
'Delete the distribution list currently selected in the listDLs list box on the form.
Private Sub cmdDeleteList_Click()
Dim oCommand As ADODB.Command
Dim strSyncTicket As String
Dim rsTemp As ADODB.Recordset
'verify that the user wishes to delete this distribution list
If MsgBox("Are you sure you want to delete the selected distribution list?", vbYesNo) = vbNo Then
Exit Sub
End If
Set rsTemp = New ADODB.Recordset
rsTemp.CursorLocation = adUseClient
'we need to pass the SystemDListOBjectId of the selected distribution list into the stored procedure for deletion - as such we'll need to find this record in SQL to fetch that data.
rsTemp.Open "SELECT SystemDListObjectID, Alias FROM vw_DistributionList WHERE Alias='" + listDLs.Text + "'", strConnectionString, adOpenKeyset, adLockReadOnly
'really none of the 3 error conditions would happen in a typical system but hey... can't be too careful.
If rsTemp.RecordCount = 0 Then
MsgBox "Unable to find distribution list by the selected alias."
GoTo CleanUp
ElseIf rsTemp.RecordCount > 1 Then
MsgBox "More than one match for in the distribution list table for the selected alias."
GoTo CleanUp
End If
If IsNull(rsTemp("SystemDListObjectID")) Then
MsgBox "The SystemDListObjectId property for the selected distribution list is NULL."
GoTo CleanUp
End If
Set oCommand = New ADODB.Command
oCommand.ActiveConnection = strConnectionString
oCommand.CommandType = adCmdStoredProc
'OK, we have the ObjectID of the distribution list to delete which is all we need here.
oCommand.CommandText = "csp_DistributionListDelete"
oCommand.Parameters.Item("@SystemDListObjectID") = oCommand.CreateParameter("SystemDlistObjectID", adGUID, adParamInput, , rsTemp("SystemDListObjectID"))
'tell the SQLSyncher to push this change into the directory of course. The "2" for the synch flags here tells the syncher to only work with the DL – which in this case is really the only flag that make sense. It's the default if you don't pass anything in but it's good to be explicit anyway.
oCommand.Parameters.Item("@DirectorySync") = oCommand.CreateParameter("DirectorySync", adInteger, adParamInput, , 1)
oCommand.Parameters.Item("@DirectorySyncFlags") = oCommand.CreateParameter("DirectorySyncFlags", adInteger, adParamInput, , 2)
'NOTE - we COULD pass in a ForceRemove flag set to 1 here to make the directory monitor remove the distribution list from the directory as well but for this example I just remove it from Unity which will also clean the Unity properties off the list in the directory. As such you can import it again later.
'We need to create a GUID that we can pass into the stored proc such that we can look for it in the DirectorySyncTicket table in SQL which is used to pass back the results of this sync
strSyncTicket = generateDirectorySyncTicket
oCommand.Parameters.Item("@DirectorySyncTicket") = oCommand.CreateParameter("DirectorySyncTicket", adGUID, adParamInput, , strSyncTicket)
'Let the stored proc fly.
oCommand.Execute
'wait for sync process to complete
If WaitForSyncToFinish(strSyncTicket) = False Then
MsgBox "(error)SQL Sync to directory failed when deleting the distributionlist" + vbCrLf + "You can check the SQLSyncSvr logs in the \commserver\logs directory for more details"
Else
MsgBox "The directory sync is complete for the distribution list removal"
End If
CleanUp:
'close out the recordsets and exit
On Error Resume Next
rsTemp.Close
Set rsTemp = Nothing
Set oCommand = Nothing
End Sub
Adding a member to a public distribution list
Before adding users to a system distribution list you must first create a new one or import an existing distribution list from the directory. The distribution list you reference that you want to add the subscriber to must be in the DistributionList table in SQL which will only be the case if you've created them through Unity or imported them.
'Select a subscriber to add to a distribution list. The distribution list is referenced by an alias string in the listDLs list box on the form. The user to add to the list is selected in a subscriber search dialog that returns the ObjectID of the selected subscriber.
Private Sub cmdAddUser_Click()
Dim strSubscriberObjectID As String
Dim strSubscriberDirectoryID As String
Dim strSystemDlistObjectID As String
Dim strDLDirectoryID As String
Dim rsTemp As ADODB.Recordset
Dim strSQL As String
Dim oCommand As ADODB.Command
Dim strSyncTicket As String
'make sure a distribution list is selected in the list box.
If Me.listDLs.ListIndex < 0 Then
MsgBox "You must first select a distribution list to add users to"
Exit Sub
End If
'get the user to select a subscriber on the local Unity server. This form allows the user to select a subscriber in a simple grid and then exposed the selection via public variables. If the user hits cancel without selecting a subscriber the public bCancel Boolean will be set to true. Showing this using the vbModal option means it will require input before the user is allowed to do anything else which simplifies things a great deal.
frmSubscriberSearch.Show vbModal
If frmSubscriberSearch.bCancelHit = True Then
'the user opted to cancel out of the dialog without selecting a user - exit out
Exit Sub
End If
'snag the directoryID and objectID of the subscriber to add. This stored procedure is a little extra fussy in that it wants both the objectID and DirectoryID of both the subscriber being added and the distribution list they are being added to. The reasons for this are a bit mysterious but my role is not to ask why...
strSubscriberObjectID = frmSubscriberSearch.strObjectID
Set rsTemp = New ADODB.Recordset
rsTemp.CursorLocation = adUseClient
'go fetch the subscriber's directoryID which is required for this SP to work properly.
strSQL = "Select DirectoryID from vw_Subscriber WHERE SubscriberObjectID='" + strSubscriberObjectID + "'"
rsTemp.Open strSQL, strConnectionString, adOpenKeyset, adLockReadOnly
If rsTemp.RecordCount = 0 Then
MsgBox "Error! Subscriber could not be found by SubscriberObjectID=" + strSubscriberObjectID
GoTo CleanUp
ElseIf rsTemp.RecordCount > 1 Then
MsgBox "Error! More than one subscriber found by SubscriberObjectID=" + strSubscriberObjectID
GoTo CleanUp
End If
'check to see if the DirectoryID for the selected subscriber is NULL - this usually means there was a problem synching them to the directory when they were first entered - either way it spells bad news for us so we'll have to bail out.
If IsNull(rsTemp("DirectoryID")) Then
MsgBox "Error! The selected subscriber has a NULL directory ID - this usually means they did not synch to the directory properly when created."
GoTo CleanUp
End If
strSubscriberDirectoryID = rsTemp("DirectoryID")
rsTemp.Close
'Check to see if this subscriber is already a top level member of the distribution list
strSQL = "SELECT vw_Subscriber.Alias FROM vw_Subscriber INNER JOIN vw_SystemDListMember ON vw_systemDListMember.DirectoryID=vw_Subscriber.DirectoryID WHERE ParentAlias='" + listDLs.Text + "' and vw_Subscriber.Alias='" + strSubscriberAlias + "'"
rsTemp.Open strSQL, strConnectionString, adOpenKeyset, adLockReadOnly
If rsTemp.RecordCount > 0 Then
MsgBox "This subscriber is already a member of the selected distribution list"
GoTo CleanUp
End If
rsTemp.Close
'now go fetch the SystemDListObjectID and DirectoryID of the currently selected distribution list that the user wants to add this subscriber to. We'll need both for the stored procedure to work properly.
strSQL = "SELECT Alias, SystemDListObjectID, DirectoryID from vw_DistributionList WHERE Alias='" + listDLs.Text + "'"
rsTemp.Open strSQL, strConnectionString, adOpenKeyset, adLockReadOnly
If rsTemp.RecordCount = 0 Then
MsgBox "Error! Could not find distribution list by selected alias."
GoTo CleanUp
ElseIf rsTemp.RecordCount > 1 Then
MsgBox "Error! Found more than one match in the distribution list table for selected alias."
GoTo CleanUp
End If
'check to see if the DirectoryID is NULL as above - again this usually indicates there was a problem synching the object to the directory when it was created originally.
If IsNull(rsTemp("DirectoryID")) Then
MsgBox "Error! The DirectoryID of the selected distribution list is NULL - this usually means there was a problem synching the list to the directory when it was created."
GoTo CleanUp
End If
strDLDirectoryID = rsTemp("DirectoryID")
strSystemDlistObjectID = rsTemp("SystemDListObjectID")
rsTemp.Close
'OK, we have the ObjectID and DirectoryID of both the subscriber to be added and the distribution list to add them to so we're ready to roll with the stored procedure here.
Set oCommand = New ADODB.Command
oCommand.ActiveConnection = strConnectionString
oCommand.CommandType = adCmdStoredProc
oCommand.CommandText = "csp_SystemDListMemberCreate"
oCommand.Parameters.Item("@SystemDListObjectID") = oCommand.CreateParameter("SystemDListObjectID", adGUID, adParamInput, , strSystemDlistObjectID)
oCommand.Parameters.Item("@SystemDListDirectoryID") = oCommand.CreateParameter("SystemDListDirectoryID", adVarChar, adParamInput, , strDLDirectoryID)
oCommand.Parameters.Item("@SubscriberObjectID") = oCommand.CreateParameter("SubscriberObjectID", adGUID, adParamInput, , strSubscriberObjectID)
oCommand.Parameters.Item("@SubscriberDirectoryID") = oCommand.CreateParameter("SubscriberDirectoryID", adVarChar, adParamInput, , strSubscriberDirectoryID)
'tell the stored proc to force this change into the directory for us, of course. The "1" for the directory synch flag here indicates to only synch the user information.
oCommand.Parameters.Item("@DirectorySync") = oCommand.CreateParameter("DirectorySync", adInteger, adParamInput, , 1)
oCommand.Parameters.Item("@DirectorySyncFlags") = oCommand.CreateParameter("DirectorySyncFlags", adInteger, adParamInput, , 1)
'We need to create a GUID that we can pass into the stored proc such that we can look for it in the DirectorySyncTicket table in SQL which is used to pass back the results of this sync
strSyncTicket = generateDirectorySyncTicket
oCommand.Parameters.Item("@DirectorySyncTicket") = oCommand.CreateParameter("DirectorySyncTicket", adGUID, adParamInput, , strSyncTicket)
frmMain.Refresh
frmMain.MousePointer = vbHourglass
DoEvents
'let the stored proc fly.
oCommand.Execute
frmMain.MousePointer = vbDefault
'wait for sync process to complete
If WaitForSyncToFinish(strSyncTicket) = False Then
MsgBox "(error)SQL Sync to directory failed when adding subscriber to DL" + vbCrLf + "You can check the SQLSyncSvr logs in the \commserver\logs directory for more details"
Else
MsgBox " Directory sync complete for subscriber add"
End If
CleanUp:
'close out the recordsets and exit
On Error Resume Next
rsTemp.Close
Set rsTemp = Nothing
Set oCommand = Nothing
End Sub
ไม่มีความคิดเห็น:
แสดงความคิดเห็น