CUDLE using Stored Procedures for external source META, LDAP, etc

[Tommer Catlin]

Ok, I know someone other than Jeff knows about this... unfortuantly, Jeff is the only one I know of and he is on vacation. I email Jeff about pushing new subscribers directly into Unity from an external source such as META. META apparently can push things such as:

last_name first_name ALIAS ext to unity to the "Stored Procedures" in the tables. Once this data reaches the store procedures, the flags go off that this new users should be created in Unity.

Anyone know of which Stored Procedures does this and how to do this? I'm using CriticalPath to push this data and link the data. It works great for automatic Active Directory / Exchange users in our Production network.

Here is what Jeff had to say...

You can absolutely do that - I have dozens and dozens of sites that have been doing this type of thing since 4.0(5) released (I'm assuming you're at least on 4.0(5)). You do not drop data directly into the tables, though - there are no triggers there that will pick this up and do it for you. To add/update data in Unity (including adding/removing subscribers) you use stored procedures. There are flags on the stored procs that will tell Unity to synch the subscriber with the directory and such which is what you want. I have several examples in VB showing how to do just this type of thing - you can check them out here:

http://www.ciscounitytools.com/CodeSamples.htm

You'll also find tools for seeing the data dictionary (CUDLE) and on the documents page you'll find a paper on administering Unity programmatically which should get you started.

Anyone have any information on this?

Thanks

Tommer

[mapennin]

Hi Tommer,

The stored procs you will want to use are:

sp_ModifySubscriber

sp_DeleteSubscriber

sp_CreateSubscriber

I recommend reading Jeff's document that he referred to above, the direct link is http://www.ciscounitytools.com/Documents/programaticAdmin.doc

- Matt

[Tommer Catlin]

I'm really kind of lost in this document. It looks very complete, but me not being a SQL admin, I'm looking for the proceedure for an external source to dump into the Stored Procs above. Is there a process that I can follow or give my SQL admins and META admins?

For instance:

Step One: connect to the unityDB (best way to connect?)

Step Two: Push the new subscriber information to the sp_createSubscriber table

Step Three: sp_createSubscriber table will automaticlly update the UnityDb with the new user(s).

Does the Stored Proc, keep a copy of this new account or all accounts, or if we get the informatio of the new subscriber to to the sp_create, does it create the account in the UnityDB, then empty it's table (the sp_create). Because if it empties it out, my "meta push" will keep trying to push it to that table thinking that it's not there.

Thanks much!!! I'm so close on this... I can almost taste the automation!

tc

[mapennin]

Step by step goes something like this:

1) Connect to UnityDb

In a Windows shop just make sure the windows account you are running under has database privileges - your SQL Admin should know how to do that. If that's not possible, you can consider using the web service interface (CUALS) instead.

2) Call sp_CreateSubscriber to create the new subscriber

This is a function - you can give it the name, COS, the template to use, etc. as parameters and it will immediately create the user in the table vw_Subscriber.

It will also (in an AD environment) push the subscriber into AD.

3) To verify, run a select against vw_Subscriber to see your new subscriber.

Hope that helps,

- Matt

[Tommer Catlin]

Do the subscribers stay in the sp_createsubscriber table after they are created?

Thanks Matt!!!

[mapennin]

sp_CreateSubscriber is a stored *procedure*, not a table at all. It is a function, stored in the database, that when called will create a subscriber in the subscriber table (which can be accessed via the subscriber view vw_Subscriber).

- Matt

[Tommer Catlin]

So in my Meta database of:

last_name first_name ALIAS ext

and i wanted to push this to the stored procedure, I would have create a script for this? My thought was I could push this information to a table in Unity, but apparently not.

[mapennin]

Right - subscriber's are complex objects in the database consisting of entries in multiple tables, so you have to go through the stored procedures.

I'm not familiar with Meta so I can't tell you what you need to do on that end to make that happen though.

[Tommer Catlin]

What is the typical way that people remotely or automatically remove and add subscribers into Unity then in a large organization? I'm sure its not one Unity Admin that logs onto the SA page, adds the users or remove the users???

[Tommer Catlin]

I was looking at Jeff's information in the Unity book on pages 856-865. It looks like the code for creating new subscribers can be put into a web interface and uses for moves/adds/changes. This dump new subscribers directrly into the stored Procs on the Unitydb. correct?

META works a little different.. I think we can can use the same script, but I can't figure out how it will know what subscribers I am trying to create in Unity. Whereas in the example I believe in the book, the script or code is built for a custom website and an administrator can just enter the firstname, last name, and ext and hit enter and it will dump it directly to the store procs that will create the new subscriber.

Maybe I'm not a SQL person and just not getting this. But there has to some sort of administrative help for moves/adds/changes in a VM only large scale enviroment.

many thanks