Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

How to pull User Reports from CUCM and Unity

Each month I get an attrition report and I remove any users who are no longer using the phone system and their associated voicemails. Currently I search almost one by one to get the information but, I am sure there must be a way to export it out to spreadsheet?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Hall of Fame Super Red

How to pull User Reports from CUCM and Unity

Hi Greg,

Have a look at this good thread where Hailey and I were working on this question

https://supportforums.cisco.com/thread/2041397

Cheers!

Rob

Hall of Fame Super Red

How to pull User Reports from CUCM and Unity

Hi Greg,

Requirements/Special Notes

This version of User Data Dump runs on Windows XP/2000/2003/2008, Vista or Windows 7. 

You must Install the 32 bit Informix ODBC drivers even if you are running on a 64 bit version of your operating system.  The User Data Dump tool is strictly 32 bit.  See the Informix ODBC Driver Download Page for details.

This version of User Data Dump only works with Unity Connection 7.0(1) or later.

You must enable the ODBC proxy service on the Unity Connection server and attach to the database using an account enabled for the remote database access role.  See the next section for details on this.

This tool uses Microsoft’s full .NET 4.0 library (the limited “client” version of .NET 4.0 is not sufficient).  The installation will check to see if you have that installed and if not will offer you the option to download and install it automatically.

Connecting to Unity Connection 7.x or Later

For installations of 7.0(1) and later you need to use the database proxy service for access to the database from off box for any DB tool including User Data Dump.

Task 1: Configure a User without a mailbox with the Remote Administrator and System Administrator roles

  • •1.     Go to the Cisco Unity Connection Administration web interface for your installation.
  • •2.     You can leverage a user with or without a mailbox for off box data access purposes, but it’s strongly suggested that you create a new user without a mailbox that is used solely for the purpose of remote administration tasks for security reasons.  This is also required if you want to export messages from a Connection 7.0(2) or later server – a user with a mailbox may not be allowed to extract messages from other mailboxes, whereas a user without a mailbox should have no problem.
  • •3.     Be sure the web administration password for this user is not configured to require a change at first login on the “Password Policy” page for that user.
  • •4.     If necessary, change the web administration password on the “Change Password” page.  Note that only the web application password comes into play for remote data access.
  • •5.     Finally, on the “Role” page for the user, add the “Remote Administrator” and the “System Administrator” roles to the “Assigned Roles” list and save.  You can assign any or all other roles as well but for the purposes of remote access to the database and making updates to users those two are necessary.

Task 2: Set the Database Proxy Service Shutdown Time

Out of the box the database proxy service is not running and if you try to start the service it will shut down right away.  First you need to set the “Database Proxy: Service Shutdown Timer” value found in the System Settings -> Advanced -> Connection Administration section of the Cisco Unity Connection Administration page.  By default this is 0.  You can set it to as high as 999 days if you like.  After the number of days configured here the remote database proxy service will shut down.  This is useful if you want to do some migration work, for instance, and don’t want to forget to have the service disabled for security reasons.

NOTE: If you restart the server, the remote database proxy service will remain shut off.  After a system restart you have to go in and manually turn on the service again (see step 3)

Task 3: Activate the Remote Database Proxy Service

  • •1.     Out of the box the service that listens to remote database requests is not active, you must turn it on.  To do this, go to the “Cisco Unity Connection Serviceability” web admin page.
  • •2.     On the Tools menu, select the “Service Management” page.
  • •3.     The “Connection Database Proxy” item under the “Optional Services” section will be marked as “Deactivated” and stopped.  Press the “Activate” button and it will be activated and started automatically.

Once you’ve started the proxy service you can connect with any tool that needs off box database access using the user name, web administration password and port “20532”.

NOTE: The service will automatically shut down after the number of days configured in step 2 above or if you restart the server.

Task 4: Login to the Remote Server

When you first start User Data Dump, you will see a login dialog box that will be empty except for port 20532 filled in as the default for the port.  You must provide the server name or IP address for the “Server” field and provide the login and password for the database connection account.  Use the alias and web administration password of the database user created above for the login and password fields.

User Data Dump will remember your entries including the password (which is stored in a secure hash).  Each time you run User Data Dump it will load the settings of the last connection you made.  Every server you’ve successfully connected to in the past will be listed in the drop down list in the order in which you connected to it last – most recent to least recent.

NOTE: The login and password information is stored along with the local Windows login name.  Only those servers that have been attached to successfully using the current Windows login will be listed.  If you are logging into the same Windows server with different users you will only see servers connected to with that particular Windows login.

NOTE: Providing the wrong password or login will fail quickly and give you a chance to try a different pair.  Providing an incorrect server or port, however, results in a 60 second timeout while UDD waits for the Informix ODBC driver to return.  Unfortunately this cannot be shorted.  Type carefully.

Cheers!

Rob

9 REPLIES
Hall of Fame Super Red

How to pull User Reports from CUCM and Unity

Hi Greg,

Have a look at this good thread where Hailey and I were working on this question

https://supportforums.cisco.com/thread/2041397

Cheers!

Rob

How to pull User Reports from CUCM and Unity

"Quick Hand" Huffman caught me mid composition again!  That is a good thread, I should have remembered it as I do recall reading it!  +5 Rob.

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

How to pull User Reports from CUCM and Unity

Greg,

CUCM

Method 1 (sql): I am not sure what version CUCM you are running but in releases after 5x you should be able to dump information on end users using a query from the CLI.

admin:run sql select userid,firstname,lastname from enduser

You will need to log your CLI session and do some text manipulation before putting it in your spreadsheet.

Method 2 (BAT): Depending on your CUCM version, you may have an option to bulk export users. In later releases, there are multiple export options.

- Bulk Administration>Users>Generate User Reports .  If available, this is the best option given your requirement. You can create a custom report on users with only the fields you are interested in. I think this was introduced in 7x but that is guesswork.

- Bulk Administration>Users>Export Users. Should be available in all modern releases. You can also configure a custom file format to control fields that are exported. Similar to previous, but with more steps

- Bulk Administration>Import/Export>Export.  Not ideal but I included it for completeness.  You can export entire tables from the database

Unity

Again, version dependant.  If Unity 4x/5x/7x then you can run reports from system maintenance:

http://www.cisco.com/en/US/docs/voice_ip_comm/unity/5x/maintenance/guide/ex/5xcumge100.html#wp1343247

If Unity Connection, then you can run user reports to get what you want.

http://www.cisco.com/en/US/docs/voice_ip_comm/connection/8x/administration/guide/8xcucsag300.html

In both cases, you can get electronic files that you should be able to use for your purposes.

HTH.

Regards,

Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

Hall of Fame Super Red

How to pull User Reports from CUCM and Unity

Hey Bill,

Nice answer my friend +5

Cheers!

Huff

PS: I don't think if you saw me typing that you'd call me "quick hands" hahahahahaha

New Member

How to pull User Reports from CUCM and Unity

Thank You for the quick reply, now if it will just concatenate into a spreadsheet and figure out who needs to be deleted and perform those tasks automated.....now there would be a tool

New Member

How to pull User Reports from CUCM and Unity

So great information on how to pull the report from Unity but, I did not see instructions for installing and in particular I am not sure if this is installed on my local pc or on the server side?

New Member

How to pull User Reports from CUCM and Unity

I have the tool installed, are there any additional instructions

Hall of Fame Super Red

How to pull User Reports from CUCM and Unity

Hi Greg,

Good stuff!

If you are referring to the CUDD tool, it's installed on the user PC

There are a number of steps that are required, they are pretty well explained in the Tool Help;

http://www.ciscounitytools.com/Applications/CxN/UserDataDump/Help/UserDataDump.htm

Cheers!

Rob

Hall of Fame Super Red

How to pull User Reports from CUCM and Unity

Hi Greg,

Requirements/Special Notes

This version of User Data Dump runs on Windows XP/2000/2003/2008, Vista or Windows 7. 

You must Install the 32 bit Informix ODBC drivers even if you are running on a 64 bit version of your operating system.  The User Data Dump tool is strictly 32 bit.  See the Informix ODBC Driver Download Page for details.

This version of User Data Dump only works with Unity Connection 7.0(1) or later.

You must enable the ODBC proxy service on the Unity Connection server and attach to the database using an account enabled for the remote database access role.  See the next section for details on this.

This tool uses Microsoft’s full .NET 4.0 library (the limited “client” version of .NET 4.0 is not sufficient).  The installation will check to see if you have that installed and if not will offer you the option to download and install it automatically.

Connecting to Unity Connection 7.x or Later

For installations of 7.0(1) and later you need to use the database proxy service for access to the database from off box for any DB tool including User Data Dump.

Task 1: Configure a User without a mailbox with the Remote Administrator and System Administrator roles

  • •1.     Go to the Cisco Unity Connection Administration web interface for your installation.
  • •2.     You can leverage a user with or without a mailbox for off box data access purposes, but it’s strongly suggested that you create a new user without a mailbox that is used solely for the purpose of remote administration tasks for security reasons.  This is also required if you want to export messages from a Connection 7.0(2) or later server – a user with a mailbox may not be allowed to extract messages from other mailboxes, whereas a user without a mailbox should have no problem.
  • •3.     Be sure the web administration password for this user is not configured to require a change at first login on the “Password Policy” page for that user.
  • •4.     If necessary, change the web administration password on the “Change Password” page.  Note that only the web application password comes into play for remote data access.
  • •5.     Finally, on the “Role” page for the user, add the “Remote Administrator” and the “System Administrator” roles to the “Assigned Roles” list and save.  You can assign any or all other roles as well but for the purposes of remote access to the database and making updates to users those two are necessary.

Task 2: Set the Database Proxy Service Shutdown Time

Out of the box the database proxy service is not running and if you try to start the service it will shut down right away.  First you need to set the “Database Proxy: Service Shutdown Timer” value found in the System Settings -> Advanced -> Connection Administration section of the Cisco Unity Connection Administration page.  By default this is 0.  You can set it to as high as 999 days if you like.  After the number of days configured here the remote database proxy service will shut down.  This is useful if you want to do some migration work, for instance, and don’t want to forget to have the service disabled for security reasons.

NOTE: If you restart the server, the remote database proxy service will remain shut off.  After a system restart you have to go in and manually turn on the service again (see step 3)

Task 3: Activate the Remote Database Proxy Service

  • •1.     Out of the box the service that listens to remote database requests is not active, you must turn it on.  To do this, go to the “Cisco Unity Connection Serviceability” web admin page.
  • •2.     On the Tools menu, select the “Service Management” page.
  • •3.     The “Connection Database Proxy” item under the “Optional Services” section will be marked as “Deactivated” and stopped.  Press the “Activate” button and it will be activated and started automatically.

Once you’ve started the proxy service you can connect with any tool that needs off box database access using the user name, web administration password and port “20532”.

NOTE: The service will automatically shut down after the number of days configured in step 2 above or if you restart the server.

Task 4: Login to the Remote Server

When you first start User Data Dump, you will see a login dialog box that will be empty except for port 20532 filled in as the default for the port.  You must provide the server name or IP address for the “Server” field and provide the login and password for the database connection account.  Use the alias and web administration password of the database user created above for the login and password fields.

User Data Dump will remember your entries including the password (which is stored in a secure hash).  Each time you run User Data Dump it will load the settings of the last connection you made.  Every server you’ve successfully connected to in the past will be listed in the drop down list in the order in which you connected to it last – most recent to least recent.

NOTE: The login and password information is stored along with the local Windows login name.  Only those servers that have been attached to successfully using the current Windows login will be listed.  If you are logging into the same Windows server with different users you will only see servers connected to with that particular Windows login.

NOTE: Providing the wrong password or login will fail quickly and give you a chance to try a different pair.  Providing an incorrect server or port, however, results in a 60 second timeout while UDD waits for the Informix ODBC driver to return.  Unfortunately this cannot be shorted.  Type carefully.

Cheers!

Rob

1156
Views
24
Helpful
9
Replies
CreatePlease login to create content