Any faster alternative to Subscriber / Report / Distribution Lists???

[dsiwa]

Have a need to generate a report of all Distribution lists (showing ext. & members) on our system. We ran the Dist. List report one day and it didn't complete till sometime the following morning. Is that even normal? At the time, there were only a handful of users hitting the server. Is there another behind-the-scenes way of obtaining the info I need?

[jschless]

This does not sound normal. Those reports should only take a few minutes to format. How many users are on the system? How many Distribution lists do you have? How many reports were kicked off at the same time?

thx

[dsiwa]

That's what I thought. This server has approx. 15-20 distribution lists and only ~50 mailboxes. The job was kicked off afterhours. One thing I was thinking, the distribution lists have members from 2 other Unity installations across the WAN, could it be searching those....

[lindborg]

You can pull this information right out of SQL if you want to get your SQL query shoes on. All the distribution lists that Unity is aware of (i.e. they have an extension and or a voice name recorded and are, as such, available for addressing over the phone) are stored in the PublicDistributionList table in the UnityDB database in SQL. The members for all those distribution lists are stored in the SystemDListMembers table in the same database. Be aware that only the top level members are stored, it's not a flattened list of all users in all lists that may be contained IN that list. The top level members will include other public distribution lists that may have been added to this list. In the case of lists that subscribers are added to automatically via subscriber templates, they'll all be top level members - this is just something to be aware of.

Here's an SQL string you can pop right into the Query Analyzer that will kick out a list of all public distribution lists and the members of each in a little table. If you need more details other than the alias and display name for the top level members, you'll have to get fancier and write a little app that actually plunges into the subscriber and call handlers table to pull out info such as extension number and the like. If that's necessary, let me know and I can post a little VB script that uses ODBC to do just that.

select DistributionList.Alias, SystemDListMember.Alias, SystemDListMember.DisplayName from DistributionList Inner Join SystemDListMember on SystemDListMember.ParentObjectID=DistributionList.SystemDListObjectID

[dsiwa]

I'll give the SQL string you provided a shot, however, Unity 3.1(3) doesn't seem to have Query Analyzer installed like the Call Mgrs do. Without the risk of corrupting the UnityDB, can I copy it to a machine that has Query Analyzer and run the script from there?

Thanks for ALL your assistance.

[lindborg]

If you have SQL 2000 installed it'll have the query analyzer, if you have MSDE it wont... it's not a Unity install thing. CM doesn't support MSDE so it'll always have the full SQL 7 on it which comes with all the bells and whistles.

You can do this old school using the OSQL interface which is there on all systems with SQL installed. From the command line you can enter this (caps and quotes count here):

osql -d UnityDB -E -q "select DistributionList.Alias, SystemDListMember.Alias, SystemDListMember.DisplayName from DistributionList Inner Join SystemDListMember on SystemDListMember.ParentObjectID=DistributionList.SystemDListObjectID" -o fun.txt

That will spit out the query results to the Fun.txt file from the directory where you ran this. The formatting is a little funny but you can twiddle with that if you need to... if you run "OSQL /?" it'll give you all the options which includes column seperators and column widths and the like.

It's easy enough to do a VB app or a script or whatever that opens an ODBC connection to the local SQL database, uses that same query string and have it kick the information out to a text file. You just need to be logged into the box as a member of the local admins group and you have full access to the UnityDB database. There's no risk of corrupting the database if all you're doing is reading the information out and spitting it to a file.

[jschless]

You can install the Tools as well from the Data Store CD. Follow the install guide listed here:

http://www.cisco.com/univercd/cc/td/doc/product/voice/c_unity/unity31/inst/inst31/inst_020.htm#xtocid23

Make sure you choose to install the Client Tools only. You do not want to install another instance of SQL.