Automated "Subscriber Message Activity" Reports

[brnhornt]

Hi everyone,

Just a quick question. Does anyone know if there is a way to automate Unity reporting? I'm looking to be able to run a "Subscriber Message Activity" report once a month.

Thanks!

Kevin

[Ginger Dillon]

Hi Kevin -

I don't believe this automation or scheduling functionality is in Unity or Unity Connection yet for Reports.

Ginger

[Rob Huffman]

Hi Kevin,

Ginger is most correct! +5 points Ginger :)

For Unity;

In the current release of Cisco Unity, reports cannot be scheduled in advance, and if you turn off Cisco Unity while there are reports in the report queue, the reports will be deleted.

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

For UC;

Note that reports cannot be scheduled in advance. If you shut down the Connection or Cisco Unified CMBE server, or close the Connection Administration browser session while reports are being generated, the report generation will be canceled.

http://www.cisco.com/en/US/docs/voice_ip_comm/connection/7x/serv_administration/guide/7xcucservag050.html#wp1051587">http://www.cisco.com/en/US/docs/voice_ip_comm/connection/7x/serv_administration/guide/7xcucservag050.html#wp1051587

Hope this helps!

Rob

[jessakhanian]

Hello,

We have Unity Connection 7.0.2 running and when we run this report (Subscriber Message Activity) against any particular user, we only get N/A under all column headings. Also, the information seems to be repeated (everything is listed twice), plus we don't seem to have captured all activities.

The report just doesn't seem right. Does anyone have any clues if we're doing something wrong or if we might be running into something.

Thanks,

Joseph

[Rob Huffman]

Hi Joseph,

I think you are seeing this bug;

CSCsr45007 Bug Details

Subscriber Message Activity report problems

Symptom:

Subscriber Message Activity Report doesn't have data that is expected. Many fields come up as N/A or blank.

Conditions:

This happens any time the report is run.

Workaround:

None.

Further Problem Description:

This underlying problems were diagnosed after 7.0 shipped and will be addressed in 8.0. It entails a rewrite of this report.

Status

Open

Severity

4 - minor

Last Modified

In Last month

Product

Cisco Unity Connection

Technology

1st Found-In

7.0(0.313)

Hope this helps!

Rob

[lindborg]

For Connection you can write your own reporting tool that fetches data from the reports database - the report harvester runs every half hour or so (depending on how busy the system is) and keeps those tables populated - you can see them using CUDLI and the data dictionary has most of the entries filled in. I've had a few sites do this, although not for the call activity report.

It should be possible if you're comfortable using ODBC data calls to fetch that data on your own - if you're interested in pursuing it I can poke in there and see what's in the tables.

[Ginger Dillon]

Hi Jeff -

I would be interested in this as well :-)

Thanks, Ginger

[jessakhanian]

Same here. The customer really wants to utilize this report so that they can monitor Subscriber activities on several key users. The CUDD (data dump utility on ciscounitytools website) doesn't do the trick for them.

Is there a plan to fix this issue with an ES or in a future release? Or maybe another off-box application (similar to CUDD) could be made available to feth the data and present it in a report? Neither I, nor the customer, have the expertise to do this on our own as of now.

Thanks,

Joseph

[Rob Huffman]

Hi Joeseph,

From the bug details;

This underlying problems were diagnosed after 7.0 shipped and will be addressed in 8.0. It entails a rewrite of this report.

Hope this helps!

Rob

[jessakhanian]

8.0?? We just got 7.0. I'm sure it will take a few months util 8.0 is released, approved, stable, etc. Are there any plans to get a fix for this prior to 8.0 release?

I realize I'm asking this without anyone here knowing the answer for certain, but I'm not sure what else to do. Maybe Jeff knows something.

Thanks Rob.

Joseph

[lindborg]

sorry - the reports stuff is run out of the admin group and I have zero visibility into their work queue - if the bug says 8.0 then it's probably going to be in 8.0 that it gets addressed. I know they have a big pile of stuff to work on.

I'm running through some testing with older Unity versions this afternoon (I always forget how much fun installing 4.0(5) can be) but after that I'll eyeball the reports tables and see if I can figure out where they're pulling the data for that report.

[lindborg]

Ok, so item #1 is to go out and fetch the latest version of the Connection database explorer (CUDLI) I posted this afternoon:

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

The reports folks used a somewhat different enumerated type format in the XML for their dictionary than the directory folks did so I had to update CUDLI to handle it. This is important for the reports output because they use enumerated types often.

Once you fire it up, select the reports database (UnityRptDb) in the database menu. Doesn't matter if you're using views or tables here - there's no joins going on in the views for the report stuff.

For the message activity report the table you're interested in is tbl_subscribermessage. Most of the columns are straight forward but you'll need to check the column data dictionary output for the eventsource and eventtype fields - the source is either the pone or “other” (i.e. a desktop client). The phone2 is a mystery to me - I think that's AMIS but don't hold me to it - none of the systems I connected to had this event source value. The big one here is the eventtype field - there's 18 possible values here for all message box activity, the enumerated type shown in the column view of CUDLI has them all for you - should be pretty self explanatory there.

As far as I can tell, this is the only table used by the message activity report.

However, you may also be interested in the tbl_messageactivity table - this is a little more involved but shows all message actions (new, marked read, deleted) for every message of every type on all mailboxes. Doesn't have the detail that the tbl_subscribermessage does but may be of interest - if you want to use this you'll have to use an inner join with the UnityDirDB tables to resolve the mailbox object Id to a readable subscriber's name/alias - not difficult.

Anyway - if you guys need help getting your ODBC connection up and flying for you own apps, let me know - we can walk through that. Step one is to get the Informix ODBC client libraries and install it on the box you want to connect to (you've already done this if you've installed CUDLI and got it running). The connection string and such is easy enough - I can help with that if you want to pursue it.