Cisco Unity Connection - Map Active Schedules to Holiday Schedules (CUDLI \ SQL)

[Kita]

I searched the forums and I didn't see this come up.  I have a client with over 150 site specific schedules and most are linked to several holiday schedules.  Some of the holiday schedules are old, and some active schedules don't have associated holiday schedules at all.  They wanted an audit report which users / call handlers had which holiday schedule, but the first challenge was to map the active to holiday schedules.  I used the Connection Database Explorer to find the information I needed and ended up with this SQL statement:

run cuc dbquery unitydirdb SELECT b.displayname as ActiveSchedule, a.displayname as HolidaySchedule FROM tbl_schedule a JOIN tbl_schedulesetmembermap c ON a.objectid=c.scheduleobjectid JOIN tbl_scheduleset b ON c.schedulesetobjectid=b.objectid where c.exclude=1

If some of the experts want to improve it or make it better, I welcome that!  I'm not sure what the difference is in the tbl_ vs vw_

Also, I only mapped active schedules to holiday schedules.  Then I used the User and Call Handler dump tools to map those components to the schedules and holidays to complete my report with Excel formulas.  Is there an SQL guru that can build on this statement to map the following:

user_alias | active schedule | holiday schedule

system call handler | active schedule | holiday schedule

This isn't an urgent request, my initial requirement was fulfilled, but I wanted to post it in case someone else could use it or build off of it.  It also peaked my interest even more in the power of SQL queries.  Up until now, I've always been able to find a query that was already build to do what I needed, or get me so close I only needed a couple tweaks.  This is the first time I had to build one from scratch.  So I hope it helps someone else.

[Adam Pawlowski]

tbl is table and vw is view . I can try and poke at this tomorrow in my lab if I get a moment , I think you’re on the right track save for mapping alias which I believe is subscriber for users . I don’t know about call handler off the top of my head unfortunately .

[Kita]

Thanks! So for queries is is better to use the vw_ version of the tables for performance reasons?

[Adam Pawlowski]

I can't say I don't know enough about it to answer that question, but a view will combine data into a more useful format in some cases.

I ran out of time this morning to play with this, and my brain isn't compatible with the SQL query spaghetti that is needed here.

What I did learn is that:

tbl_schedule contains the various schedules, which include both system schedules, personal schedules, holiday schedules etc. There's a column 'isholiday' which is 1 if it's a holiday schedule.

tbl_scheduledetail contains the schedule entries, mapped back to the schedule's object ID but that's not what you want.

tbl_scheduleset describes schedules that are assigned to subscribers, locations, etc. You'll find a record in this table for each schedule that has a holiday schedule applied. The owner_subscriberobjectid and owner_personalrulesetobjectid will be NULL, and on my system these things are the only ones that appear to have the owner_locationobjectid populated.

tbl_schedulesetmembermap maps the schedule's object ID to the scheduleset's object ID.

So you could look for schedule object IDs where isholiday = 1 , find instances of those in schedulesetmembermap , which map to a scheduleset row . The display name there does appear to match the schedule's but I woudln't trust that. You could then take that result set of scheduleset IDs, and go back to schedulesetnumbermap, find the associated schedule object IDs associated with those... and then go and look at the schedule name.

That should tell you which schedules are using which holiday schedules.

From there, you can then go back to scheduleset , look at the owner_subscriberobjectid , and map that to objectid in vw_subscriberbasic to get the subscriber's alias. What I don't know is where you're going to grab call handlers - I believe "subscribers" and call handlers are all call handler objects in the system (subscribers are in view form as an amalgam of data), so perhaps if you can figure out where those are, or if owner_subscriberobjectid is some other sort of objectid relating to another table like tbl_alias or something, then you'd be able to go from there.

It's a huge mess in my head, and I didn't even check to see if there's a report or if the user data dump utility will make this easier.

[Kita]

Yep, that matches my findings and to take it a step further the schedulesetnumbermap table has an exclude flag that seems to call out the ones that are matches (excludes All hours for example)