<div dir="ltr">Actually, now that I think about it. The User Data Dump utility can probably achieve the exact same results, and requires no programming knowledge at all. I see a few different columns to pull from in the utility like unread messages count and deleted items message count, but nothing that just says total message count. So, you'll either need to pull all of those fields and add them up yourself, if just pull the mailbox size in bytes and use that as your criteria. Also, you can schedule the User Data Dump to run automatically on a schedule too.</div><br><div class="gmail_quote"><div dir="ltr">On Wed, Mar 8, 2017 at 4:28 PM Anthony Holloway <<a href="mailto:avholloway%2Bcisco-voip@gmail.com">avholloway+cisco-voip@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr" class="gmail_msg">I got a little too excited and wrote a starting Python script to get the Alias and Message counts and print them to stdout as a CSV format. You'll need the requests module, which if you don't already have it, you should get it. It's pretty nice for working with HTTP requests. Also, disclaimer, I don't know how to properly handle the SSL cert here, so I'm basically ignoring it. Not great security practice, I know.<div class="gmail_msg"><br class="gmail_msg"></div><div class="gmail_msg"><div class="gmail_msg"><font face="monospace" class="gmail_msg">import requests</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">from requests.packages.urllib3.exceptions import InsecureRequestWarning</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">requests.packages.urllib3.disable_warnings(InsecureRequestWarning)</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><br class="gmail_msg"></font></div><div class="gmail_msg"><font face="monospace" color="#ff0000" class="gmail_msg">api_host = "<a href="http://cucpub.company.com" class="gmail_msg" target="_blank">cucpub.company.com</a>"</font></div><div class="gmail_msg"><font face="monospace" color="#ff0000" class="gmail_msg">api_username = "cucadmin"</font></div><div class="gmail_msg"><font face="monospace" color="#ff0000" class="gmail_msg">api_password = "Password123!"</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">users_per_page = 50</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">current_page = 1</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">url_users = "https://{0}/vmrest/users?rowsPerPage={1}&pageNumber={2}"</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">url_user = "https://{0}/vmrest/users/{1}/mailboxattributes"</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">headers = {'Accept': 'application/json'}</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">response = requests.get(url_users.format(api_host, 0, current_page), auth=(api_username, api_password), verify=False, headers=headers)</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">data = response.json()</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">total_users = int(data['@total'])</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">print "Getting message counts for a total of {0} users...\n".format(total_users)</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><br class="gmail_msg"></font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">print "Alias,Message_Count"</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">for page in range(total_users / users_per_page + 1):</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>current_page = page + 1</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>response = requests.get(url_users.format(api_host, users_per_page, current_page), auth=(api_username, api_password), verify=False, headers=headers)</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>data = response.json()</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>users = data['User']</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>for user in users:</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>obj = user['ObjectId']</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>alias = user['Alias']</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>response = requests.get(url_user.format(api_host, obj), auth=(api_username, api_password), verify=False, headers=headers)</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>data = response.json()</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>message_count = data['NumMessages']</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>print "{0},{1}".format(alias, message_count)</font></div></div><div class="gmail_msg"><br class="gmail_msg"></div><div class="gmail_msg">I'll leave it to you if you want to sort it in the Python script, or just sort it in Excel afterwards, since it's CSV output anyway.</div></div><br class="gmail_msg"><div class="gmail_quote gmail_msg"><div dir="ltr" class="gmail_msg">On Wed, Mar 8, 2017 at 4:08 PM Nick Barnett <<a href="mailto:nicksbarnett@gmail.com" class="gmail_msg" target="_blank">nicksbarnett@gmail.com</a>> wrote:<br class="gmail_msg"></div><blockquote class="gmail_quote gmail_msg" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr" class="gmail_msg">Thanks Anthony and Brian. I think i can make this work, especially after reading your firefox script and your non-code example... I think i can hack this together with another project I made in python and probably get it to work.<div class="gmail_msg"><br class="gmail_msg"></div><div class="gmail_msg">Thanks,</div><div class="gmail_msg">Nick</div></div><div class="gmail_extra gmail_msg"><br class="gmail_msg"><div class="gmail_quote gmail_msg">On Wed, Mar 8, 2017 at 3:40 PM, Anthony Holloway <span dir="ltr" class="gmail_msg"><<a href="mailto:avholloway+cisco-voip@gmail.com" class="gmail_msg" target="_blank">avholloway+cisco-voip@gmail.com</a>></span> wrote:<br class="gmail_msg"><blockquote class="gmail_quote gmail_msg" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr" class="gmail_msg">I don't think the CUC API has an arbitrary SQL execution method call like AXL does.<div class="gmail_msg"><br class="gmail_msg"></div><div class="gmail_msg">Since the SQL query is effectively searching everyone's mailbox message counts, and then just filtering the output to you, you could write that same process using the CUPI API.</div><div class="gmail_msg"><br class="gmail_msg"></div><div class="gmail_msg">Here's a high level program flow in no particular actual language:</div><div class="gmail_msg"><br class="gmail_msg"></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">results = Array()</font></div><div class="gmail_msg"><div class="gmail_msg"><font face="monospace" class="gmail_msg">response = HTTP GET https://<connection-server>/vmrest/users</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">for each user in response.users:</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292inbox-inbox-Apple-tab-span gmail_msg" style="white-space:pre-wrap"> userobjectid = response.</span><span style="background-color:rgb(249,249,249)" class="gmail_msg">ObjectId</span></font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span style="white-space:pre-wrap" class="gmail_msg"> alias = response.Alias</span> <span style="white-space:pre-wrap" class="gmail_msg"><br class="gmail_msg"></span></font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>response = HTTP GET https://<connection server>/vmrest/users/<userobjectid>/mailboxattributes</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292Apple-tab-span gmail_msg" style="white-space:pre-wrap"> </span>count = response.<span style="background-color:rgb(249,249,249)" class="gmail_msg">NumMessages</span></font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg"><span class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292inbox-inbox-Apple-tab-span gmail_msg" style="white-space:pre-wrap"> results.append(alias, count)</span> </font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">results.sort(count, DESC)</font></div><div class="gmail_msg"><font face="monospace" class="gmail_msg">for each result in results:</font></div></div><div class="gmail_msg"><span style="white-space:pre-wrap" class="gmail_msg"><font face="monospace" class="gmail_msg"> print result.alias, results.count</font></span><br class="gmail_msg"></div><div class="gmail_msg"><span style="white-space:pre-wrap" class="gmail_msg"><br class="gmail_msg"></span></div><div class="gmail_msg"><span style="white-space:pre-wrap" class="gmail_msg">Oh, and this is probably a good time to plug my Firefox GreaseMonkey User Script which shows you the breakdown of message counts per folder, and even let's you empty the deleted items.</span></div><div class="gmail_msg"><span style="white-space:pre-wrap" class="gmail_msg"><br class="gmail_msg"></span></div><div class="gmail_msg"><span style="white-space:pre-wrap" class="gmail_msg"><a href="https://twitter.com/avholloway45633/status/828515885769953280" class="gmail_msg" target="_blank">https://twitter.com/avholloway45633/status/828515885769953280</a></span><br class="gmail_msg"></div><div class="gmail_msg"><span style="white-space:pre-wrap" class="gmail_msg"><br class="gmail_msg"></span></div></div><br class="gmail_msg"><div class="gmail_quote gmail_msg"><div class="gmail_msg"><div class="m_-1647230982396692319m_4349961610504487869h5 gmail_msg"><div dir="ltr" class="gmail_msg">On Wed, Mar 8, 2017 at 2:52 PM Nick Barnett <<a href="mailto:nicksbarnett@gmail.com" class="gmail_msg" target="_blank">nicksbarnett@gmail.com</a>> wrote:<br class="gmail_msg"></div></div></div><blockquote class="gmail_quote gmail_msg" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="gmail_msg"><div class="m_-1647230982396692319m_4349961610504487869h5 gmail_msg"><div dir="ltr" class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">I found<a href="https://www.cisco.com/c/en/us/support/docs/unified-communications/unity-connection/118299-technote-cuc-00.html#anc8" class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg" target="_blank"> this SQL query</a> to return a count of all message boxes in CUC. I modified it to return the top 10 by adding "FIRST 10" immediately after "select" on the first line:<br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><div class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></div><div class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">run cuc dbquery unitymbxdb1 select FIRST <b class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><span style="background-image:initial;background-position:initial;background-size:initial;background-repeat:initial;background-origin:initial;background-clip:initial;background-color:yellow" class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">10</span></b> alias as UserID,
count (*) as messages \<span class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p>
<p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">from vw_message, unitydirdb:vw_mailbox, unitydirdb:vw_user \<span class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p>
<p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">where mailboxobjectid in \<span class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p>
<p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">(select mailboxid from vw_mailbox where unitydirdb:
vw_user.objectid = unitydirdb:vw_mailbox.userobjectid) \<span class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p>
<p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">group by alias order by \<span class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p>
<p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">messages desc<span class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p><p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></p><p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">This works, but it's not very "dev ops friendly." I think I'd have to use an expect script and code in my CLI password... which I really don't want to do.</p><p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></p><p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">I looked through the VMREST kit for CUC 10.5 and I don't see anything like this. I can usually find my way around the AXL kit in CUCM but I frequently have issues finding what I need in the CUC VMREST calls.<br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></p><p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></p><p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">Is there a way to execute this specific query via VMREST to CUC? Is there a VMREST call already baked into CUC that will return similar information?</p><p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></p><p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">Thanks,</p><p class="MsoNormal m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">Nick</p></div></div></div></div>
_______________________________________________<span class="gmail_msg"><br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">
cisco-voip mailing list<br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">
<a href="mailto:cisco-voip@puck.nether.net" class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg" target="_blank">cisco-voip@puck.nether.net</a><br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">
<a href="https://puck.nether.net/mailman/listinfo/cisco-voip" rel="noreferrer" class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg" target="_blank">https://puck.nether.net/mailman/listinfo/cisco-voip</a><br class="m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">
</span></blockquote></div>
</blockquote></div><br class="gmail_msg"></div>
</blockquote></div></blockquote></div>