The documentation set for this product strives to use bias-free language. For the purposes of this documentation set, bias-free is defined as language that does not imply discrimination based on age, disability, gender, racial identity, ethnic identity, sexual orientation, socioeconomic status, and intersectionality. Exceptions may be present in the documentation due to language that is hardcoded in the user interfaces of the product software, language used based on RFP documentation, or language that is used by a referenced third-party product. Learn more about how Cisco is using Inclusive Language.
This document describes how to obtain the message count and size of a user mailbox with Structured Query Language (SQL) queries via the CLI. This data can also be retrieved with the User Data Dump tool, from the Cisco Unified Communications Tools page.
Cisco recommends that you have knowledge of Cisco Unity Connection (CUC).
The information in this document is based on CUC Versions 8.X and later, but this information might work for earlier versions as well.
The SQL queries are formed with the data from these databases:
The SQL queries are formed with the data in these views. A view is a table that is either a combination of two or more tables, or the same data in a single table.
These views are used in the UnityDirDB database:
These views are used in the UnityMbxDB1 database:
This section describes the various SQL queries that you can use in CUC.
Enter this command in order to obtain a list of the total messages count with a known alias:
admin: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='Anirudh')
messages
--------
3
This query is a high-complexity, double-database query that involves multiple tables. For servers with a very large database and mailbox size, an extended period of time might elapse before an output appears, even over an hour, which is not ideal. In such scenarios, you can use this query instead:
admin:run cuc dbquery unitymbxdb1 select count (*) as Messages from vw_message
where mailboxobjectid in (select mailboxobjectid from vw_mailbox where
description='Anirudh')
messages
--------
3
The first query returns the data when alias is mentioned, which is Unique. The second query returns the data when description is mentioned, which is NOT Unique.
Note: When the mailbox is created, the description is the same as the alias; however, when the alias is updated, the description is not updated. For small databases, the first query is ideal. In order to demonstrate that the description is not changed after the alias is altered, test3 is modified to Atest3 and used for the next sections.
Enter this command in order to obtain a list of users with the total messages count:
admin:run cuc dbquery unitymbxdb1 select alias as UserID, 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) group by alias order by
messages desc
userid messages
---------------------------- --------
Anirudh 3
Atest3 2
undeliverablemessagesmailbox 1
For the same reasons that are mentioned in the List the Total Messages Count with a Known Alias section, this query can also be used:
admin:run cuc dbquery unitymbxdb1 select description, count (*) as Messages from
vw_message, vw_mailbox where vw_mailbox.mailboxobjectid =
vw_message.mailboxobjectid group by description order by messages desc
description messages
---------------------------- --------
Anirudh 3
test3 2
undeliverablemessagesmailbox 1
Note: In the second query, the description does not change from test3 to Atest3 after the alias is changed.
Enter this command in order to obtain a list of users with the total messages count based on the first character of an alias:
admin:run cuc dbquery unitymbxdb1 select alias as UserID, count (*) as messages from
vw_message, unitydirdb:vw_mailbox, unitydirdb:vw_user where deleted='0' and
mailboxobjectid in (select mailboxid from vw_mailbox where unitydirdb:
vw_user.objectid = unitydirdb:vw_mailbox.userobjectid) and alias like 'A%' group by
alias order by messages
userid messages
------- --------
Atest3 2
Anirudh 3
For the same reasons that are mentioned in the List the Total Messages Count with a Known Alias section, this query can also be used:
admin:run cuc dbquery unitymbxdb1 select description, count (*) as Messages from
vw_message, vw_mailbox where vw_mailbox.mailboxobjectid =
vw_message.mailboxobjectid and description like 'A%' group by description order
by messages
Here are some important notes about this query:
Here are some example conditions:
The queries that are mentioned thus far are used in order to obtain the total messages (inbox and deleted items). The next section describes queries that are used in order to obtain the total number of messages in the inbox and deleted items.
Enter this command in order to obtain a list of users with the total inbox messages:
admin:run cuc dbquery unitymbxdb1 select alias as UserID, count (*) as
inboxmessages from vw_message, unitydirdb:vw_mailbox, unitydirdb:vw_user
where deleted='0' and mailboxobjectid in (select mailboxid from vw_mailbox
where unitydirdb:vw_user.objectid = unitydirdb:vw_mailbox.userobjectid) and
alias like 'A%' group by alias order by inboxmessages
userid inboxmessages
------- -------------
Atest3 2
Anirudh 3
For the same reasons that are mentioned in the List the Total Messages Count with a Known Alias section, this query can also be used:
admin:run cuc dbquery unitymbxdb1 select description, count (*) as InboxMessages
from vw_message, vw_mailbox where vw_mailbox.mailboxobjectid =
vw_message.mailboxobjectid and deleted = '0' and description like 'A%' group by
description order by InboxMessages
Here are some important notes about this query:
Here are some example conditions:
Note: In this example, a condition is used in order to limit users with an alias/description that begins with the letter A.
These are some variations of this query:
Enter this command in order to obtain a list of users with the total deleted messages:
admin: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 'A%' group by alias order by deletedmessages
No records found
For the same reasons that are mentioned in the List the Total Messages Count with a Known Alias section, this query can also be used:
admin:run cuc dbquery unitymbxdb1 select description, count (*) as
deletedmessages from vw_message, vw_mailbox where vw_mailbox.mailboxobjectid=
vw_message.mailboxobjectid and deleted = '1' and description like 'A%' group
by description order by deletedmessages
Note: In this example there are no deleted messages, so the output appears as No records found.
Here are some important notes about this query:
Here are some example conditions:
Note: In this example, a condition is used in order to limit users with an alias/description that begins with the letter A.
These are some variations of this query:
Enter this command in order to obtain a list of users with the total, inbox, and deleted messages:
admin:run cuc dbquery unitymbxdb1 select alias as UserID, count (*) as messages,
sum(case when deleted='0' then 1 else 0 end) as Inboxmessages, sum(case when
deleted='1' then 1 else 0 end) as Deletedmessages 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)
group by alias order by messages desc
userid messages inboxmessages deletedmessages
---------------------------- -------- ------------- ---------------
Anirudh 3 3 0
Atest3 2 2 0
undeliverablemessagesmailbox 1 1 0
For the same reasons that are mentioned in the List the Total Messages Count with a Known Alias section, this query can also be used:
admin:run cuc dbquery unitymbxdb1 select description as UserID, count (*) as
messages, sum(case when deleted='0' then 1 else 0 end) as Inboxmessages, sum
(case when deleted='1' then 1 else 0 end) as Deletedmessages from vw_mailbox
join vw_message on vw_message.mailboxobjectid=vw_mailbox.mailboxobjectid
group by description order by messages desc
Here are some important notes about this query:
Here are some example conditions:
Note: In this example, a condition is used in order to limit users with an alias/description that begins with the letter A.
These are some variations of this query:
This query can be used in order to determine whether the scheduled jobs for mailboxe cleanup take effect:
admin:run cuc dbquery unitymbxdb1 select alias as UserID, count (*) as messages,
min(arrivaltime) as OldestMessageTime 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) group
by alias order by messages desc
userid messages oldestmessagetime
---------------------------- -------- -----------------------
Anirudh 3 2013-03-19 14:38:14.459
Atest3 2 2013-01-18 05:49:45.355
undeliverablemessagesmailbox 1 2012-07-05 01:10:19.961
For the same reasons that are mentioned in the List the Total Messages Count with a Known Alias section, this query can also be used:
admin:run cuc dbquery unitymbxdb1 select description, count (*) as Messages,
min(arrivaltime) as OldestMessageTime from vw_message, vw_mailbox where
vw_mailbox.mailboxobjectid = vw_message.mailboxobjectid group by description
order by messages desc
Note: In order to obtain the Last Arrival Time for the queries in the previous sections, add min(arrivaltime) as OldestMessageTime just after count(*) as Messages.
These are some variations of this query:
Enter this command in order to obtain a list of the user message count with the oldest message's arrival time and the mailbox size (without Total Duration):
admin:run cuc dbquery unitymbxdb1 select alias as UserID, count (*) as messages,
min(arrivaltime) as OldestMessageTime, vw_mailbox.bytesize 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 messages desc
userid messages oldestmessagetime bytesize
---------------------------- -------- ----------------------- --------
Anirudh 3 2013-03-19 14:38:14.459 93319
Atest3 2 2013-01-18 05:49:45.355 59890
undeliverablemessagesmailbox 1 2012-07-05 01:10:19.961 317003
Note: To get the total duration of messages: add ", sum(duration/1000) as TotalDuration_In_sec" just before "from vw_message". Do not forget the comma before sum. This can also be used for the queries in the previous sections.
For the same reasons that are mentioned in the List the Total Messages Count with a Known Alias section, this query can also be used:
admin:run cuc dbquery unitymbxdb1 select description, count (*) as Messages,
min(arrivaltime) as OldestMessageTime, vw_mailbox.bytesize from vw_message,
vw_mailbox where vw_mailbox.mailboxobjectid = vw_message.mailboxobjectid
group by description, vw_mailbox.bytesize order by messages desc
Note: In order to get the total duration of messages: add ", sum(duration/1000) as TotalDuration_In_sec" just before "from vw_message". Do not forget the comma before sum. This can also be used for the queries in the previous sections.
These are some variations of this query:
Enter this command in order to obtain a list of the user inbox and deleted messages count with the oldest message's arrival time and the mailbox size (without Total Duration):
admin: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 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
userid total inbox deleted oldestmessagetime byte
messages size
---------------- -------- ----- ------- ----------------------- ------
Anirudh 3 3 0 2013-03-19 14:38:14.459 93319
Atest3 2 2 0 2013-01-18 05:49:45.355 59890
undeliverable 1 1 0 2012-07-05 01:10:19.961 317003
messagesmailbox
Note: In order to get the total duration of messages: add ", sum(duration/1000) as TotalDuration_In_sec" just before "from vw_message". Do not forget the comma before sum. This can also be used for the queries in the previous sections.
For the same reasons that are mentioned in the List the Total Messages Count with a Known Alias section, this query can also be used:
admin:run cuc dbquery unitymbxdb1 select description, 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 from vw_message, vw_mailbox
where vw_mailbox.mailboxobjectid = vw_message.mailboxobjectid group by
description, vw_mailbox.bytesize order by TotalMessages desc
Note: To get the total duration of messages: add ", sum(duration/1000) as TotalDuration_In_sec" just before "from vw_message". Do not forget the comma before sum. This can also be used for the queries in the previous sections.
These are some variations of this query:
Enter this command in order to obtain the total number of messages for all of the mailboxes combined:
admin:run cuc dbquery unitymbxdb1 select count(*) as messages
from vw_message
messages
----------
6
Enter this command in order to obtain the user mailbox size with send and receive limits:
admin: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='Anirudh')
userid bytesize send receive warning
------- -------- -------- -------- --------
Anirudh 93319 13000000 14745600 12000000
For the same reasons that are mentioned in the List the Total Messages Count with a Known Alias section, this query can also be used:
admin:run cuc dbquery unitymbxdb1 select description, bytesize,send,receive,
warning from vw_mailbox where description ='Anirudh'
Enter this command in order to obtain all of the the user mailbox sizes with send and receive limits:
admin: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) order by
bytesize desc
userid bytesize send receive warning
---------------------------- -------- -------- -------- --------
undeliverablemessagesmailbox 317003 13000000 14745600 12000000
Anirudh 93319 13000000 14745600 12000000
Atest3 59890 13000000 14745600 12000000
Solomon 0 13000000 14745600 12000000
UnityConnection 0 50000000 50000000 45000000
Suvir 0 13000000 14745600 12000000
dsas 0 13000000 14745600 12000000
test1 0 13000000 14745600 12000000
Atest2 0 13000000 14745600 12000000
operator 0 13000000 14745600 12000000
For the same reasons that are mentioned in the List the Total Messages Count with a Known Alias section, this query can also be used:
admin:run cuc dbquery unitymbxdb1 select description, bytesize,send,receive,
warning from vw_mailbox order by bytesize desc
As a variation of this query in order to list all of the users with aliases that begin with the letter A, add and alias like 'A%' in the first query just after the vw_usermailboxmap.mailboxid=unitymbxdb1:vw_mailbox.mailboxobjectid and before ) order by condition, or you can add where description like 'A%' in the second query just before the order by condition. Ensure that this is added in the correct position, or the query fails.
Enter this command in order to obtain the total size of all mailboxes combined:
admin:run cuc dbquery unitymbxdb1 select sum (bytesize) from vw_mailbox
(sum)
-------
2683210
admin: