sql: unity message counts

Cisco has a good writeup on CUC SQL queries, which are very useful to determine which voicemail accounts are active and which are just taking up a license, and perhaps could be skipped over during migrations. To get everything you need, use the query below:

lists all message counts, durations, size, and dates per alias:

run cuc dbquery unitymbxdb1 select alias as UserID, count (*) as TotalMessages, sum(case when deleted=’0′ then 1 else 0 end) as Inbox, sum(case when deleted=’1′ then 1 else 0 end) as Deleted,  min (arrivaltime) as OldestMessageTime, vw_mailbox.bytesize, sum(duration/1000) as TotalDuration_In_sec from vw_message,vw_mailbox, unitydirdb:vw_mailbox, unitydirdb:vw_user where vw_message.mailboxobjectid=vw_mailbox.mailboxobjectid and vw_mailbox.mailboxobjectid in (select mailboxid from vw_mailbox where unitydirdb:vw_user.objectid = unitydirdb:vw_mailbox.userobjectid) group by alias, vw_mailbox.bytesize order by TotalMessages desc