<div dir="ltr">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><br></div><div><div><font face="monospace">import requests</font></div><div><font face="monospace">from requests.packages.urllib3.exceptions import InsecureRequestWarning</font></div><div><font face="monospace">requests.packages.urllib3.disable_warnings(InsecureRequestWarning)</font></div><div><font face="monospace"><br></font></div><div><font face="monospace" color="#ff0000">api_host = "<a href="http://cucpub.company.com">cucpub.company.com</a>"</font></div><div><font face="monospace" color="#ff0000">api_username = "cucadmin"</font></div><div><font face="monospace" color="#ff0000">api_password = "Password123!"</font></div><div><font face="monospace">users_per_page = 50</font></div><div><font face="monospace">current_page = 1</font></div><div><font face="monospace">url_users = "https://{0}/vmrest/users?rowsPerPage={1}&pageNumber={2}"</font></div><div><font face="monospace">url_user = "https://{0}/vmrest/users/{1}/mailboxattributes"</font></div><div><font face="monospace">headers = {'Accept': 'application/json'}</font></div><div><font face="monospace">response = requests.get(url_users.format(api_host, 0, current_page), auth=(api_username, api_password), verify=False, headers=headers)</font></div><div><font face="monospace">data = response.json()</font></div><div><font face="monospace">total_users = int(data['@total'])</font></div><div><font face="monospace">print "Getting message counts for a total of {0} users...\n".format(total_users)</font></div><div><font face="monospace"><br></font></div><div><font face="monospace">print "Alias,Message_Count"</font></div><div><font face="monospace">for page in range(total_users / users_per_page + 1):</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>current_page = page + 1</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </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><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>data = response.json()</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>users = data['User']</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>for user in users:</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>obj = user['ObjectId']</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>alias = user['Alias']</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>response = requests.get(url_user.format(api_host, obj), auth=(api_username, api_password), verify=False, headers=headers)</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>data = response.json()</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>message_count = data['NumMessages']</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>print "{0},{1}".format(alias, message_count)</font></div></div><div><br></div><div>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><div class="gmail_quote"><div dir="ltr">On Wed, Mar 8, 2017 at 4:08 PM Nick Barnett <<a href="mailto:nicksbarnett@gmail.com">nicksbarnett@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">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_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_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_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_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_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_4349961610504487869h5 gmail_msg"><div dir="ltr" class="m_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_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_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><div class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><br class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></div><div class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><p class="MsoNormal m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">run cuc dbquery unitymbxdb1 select FIRST <b class="m_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_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">10</span></b> alias as UserID,
count (*) as messages \<span class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p>
<p class="MsoNormal m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">from vw_message, unitydirdb:vw_mailbox, unitydirdb:vw_user \<span class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p>
<p class="MsoNormal m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">where mailboxobjectid in \<span class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p>
<p class="MsoNormal m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">(select mailboxid from vw_mailbox where unitydirdb:
vw_user.objectid = unitydirdb:vw_mailbox.userobjectid) \<span class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p>
<p class="MsoNormal m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">group by alias order by \<span class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p>
<p class="MsoNormal m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">messages desc<span class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></span></p><p class="MsoNormal m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><br class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></p><p class="MsoNormal m_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_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><br class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></p><p class="MsoNormal m_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_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></p><p class="MsoNormal m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><br class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></p><p class="MsoNormal m_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_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"><br class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg"></p><p class="MsoNormal m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">Thanks,</p><p class="MsoNormal m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">Nick</p></div></div></div></div>
_______________________________________________<span class="gmail_msg"><br class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">
cisco-voip mailing list<br class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">
<a href="mailto:cisco-voip@puck.nether.net" class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg" target="_blank">cisco-voip@puck.nether.net</a><br class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">
<a href="https://puck.nether.net/mailman/listinfo/cisco-voip" rel="noreferrer" class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg" target="_blank">https://puck.nether.net/mailman/listinfo/cisco-voip</a><br class="m_4349961610504487869m_-5630290769599260292gmail_msg gmail_msg">
</span></blockquote></div>
</blockquote></div><br class="gmail_msg"></div>
</blockquote></div>