Useful SQL queries for Cisco Unity
Have you ever wonder how to determine how many voicemails an user may have? or maybe how much space those voicemails are consuming? Well, fear not! This is exactly what we are going to discuss in this short post.
Using the CLI of Unity it is pretty simple to obtain those details if we know the alias of a particular user, the same results can be obtained for ALL users and for that please refer to Cisco documentation.
### How many voicemails an user has? ###
run cuc dbquery unitymbxdb1 select count (*) as Messages from vw_message, unitydirdb:vw_mailbox, unitydirdb:vw_user where mailboxobjectid in (select mailboxid from vw_mailbox where unitydirdb:vw_user.objectid = unitydirdb: vw_mailbox.userobjectid and alias='42lb')
### How many deleted voicemails an user has? ###
run cuc dbquery unitymbxdb1 select alias as UserID, count (*) as deletedmessages from vw_message, unitydirdb:vw_mailbox, unitydirdb:vw_user where deleted='1' and mailboxobjectid in (select mailboxid from vw_mailbox where unitydirdb:vw_user.objectid = unitydirdb:vw_mailbox.userobjectid) and alias like '42lb' group by alias order by deletedmessages
### How much space are those voicemails using? ###
run cuc dbquery unitydirdb select alias as UserID,bytesize,send,receive, warning from vw_user,unitymbxdb1:vw_mailbox where vw_user.objectid in (select userobjectid from vw_usermailboxmap where vw_usermailboxmap.mailboxid=unitymbxdb1:vw_mailbox.mailboxobjectid and alias like '42lb') order by bytesize desc
Very simple but immensely helpful.
I am a Cisco Certified Network and Collaboration Engineer (CCNP) with over 9 years of experience and a passion for all things technology. I have spent most of my time mastering the world of networking and collaboration, but still find myself dabbling in cloud computing and programming.
Solving problems and learning something new in the process is my favorite part of the job. Having a depth understanding of the technology is what has driven my success.
