<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.m-1647230982396692319apple-tab-span
{mso-style-name:m_-1647230982396692319apple-tab-span;}
span.gmailmsg
{mso-style-name:gmail_msg;}
span.m-1647230982396692319m4349961610504487869m-5630290769599260292inbox-inbox-apple-tab-span
{mso-style-name:m_-1647230982396692319m_4349961610504487869m_-5630290769599260292inbox-inbox-apple-tab-span;}
span.m-1647230982396692319m4349961610504487869m-5630290769599260292apple-tab-span
{mso-style-name:m_-1647230982396692319m_4349961610504487869m_-5630290769599260292apple-tab-span;}
span.m-1647230982396692319m4349961610504487869m-5630290769599260292gmailmsg
{mso-style-name:m_-1647230982396692319m_4349961610504487869m_-5630290769599260292gmail_msg;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">+100<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> cisco-voip [mailto:cisco-voip-bounces@puck.nether.net]
<b>On Behalf Of </b>Anthony Holloway<br>
<b>Sent:</b> Wednesday, March 08, 2017 5:34 PM<br>
<b>To:</b> Nick Barnett<br>
<b>Cc:</b> Cisco VoIP Group<br>
<b>Subject:</b> Re: [cisco-voip] Execute sql query via vmrest on CUC (or alternatative approach)?<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">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:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">import requests</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">from requests.packages.urllib3.exceptions import InsecureRequestWarning</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">requests.packages.urllib3.disable_warnings(InsecureRequestWarning)</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New";color:red">api_host = "<a href="http://cucpub.company.com" target="_blank">cucpub.company.com</a>"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New";color:red">api_username = "cucadmin"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New";color:red">api_password = "Password123!"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">users_per_page = 50</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">current_page = 1</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">url_users = "https://{0}/vmrest/users?rowsPerPage={1}&pageNumber={2}"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">url_user = "https://{0}/vmrest/users/{1}/mailboxattributes"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">headers = {'Accept': 'application/json'}</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">response = requests.get(url_users.format(api_host, 0, current_page), auth=(api_username, api_password), verify=False, headers=headers)</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">data = response.json()</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">total_users = int(data['@total'])</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">print "Getting message counts for a total of {0} users...\n".format(total_users)</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">print "Alias,Message_Count"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">for page in range(total_users / users_per_page + 1):</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">current_page = page + 1</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">response = requests.get(url_users.format(api_host, users_per_page, current_page), auth=(api_username, api_password), verify=False, headers=headers)</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">data = response.json()</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">users = data['User']</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">for user in users:</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">obj = user['ObjectId']</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">alias = user['Alias']</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">response = requests.get(url_user.format(api_host, obj), auth=(api_username, api_password), verify=False, headers=headers)</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">data = response.json()</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">message_count = data['NumMessages']</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">print "{0},{1}".format(alias, message_count)</span><o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Wed, Mar 8, 2017 at 4:08 PM Nick Barnett <<a href="mailto:nicksbarnett@gmail.com" target="_blank">nicksbarnett@gmail.com</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Thanks,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Nick<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">On Wed, Mar 8, 2017 at 3:40 PM, Anthony Holloway <span class="gmailmsg">
<<a href="mailto:avholloway+cisco-voip@gmail.com" target="_blank">avholloway+cisco-voip@gmail.com</a>></span> wrote:<o:p></o:p></p>
<div>
<p class="MsoNormal">I don't think the CUC API has an arbitrary SQL execution method call like AXL does.<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Here's a high level program flow in no particular actual language:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">results = Array()</span><o:p></o:p></p>
</div>
<div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">response = HTTP GET https://<connection-server>/vmrest/users</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">for each user in response.users:</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="m-1647230982396692319m4349961610504487869m-5630290769599260292inbox-inbox-apple-tab-span"><span style="font-family:"Courier New"">userobjectid = response.</span></span><span class="gmailmsg"><span style="font-family:"Courier New";background:#F9F9F9">ObjectId</span></span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="gmailmsg"><span style="font-family:"Courier New"">alias = response.Alias</span></span><span style="font-family:"Courier New""> </span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">response = HTTP GET https://<connection server>/vmrest/users/<userobjectid>/mailboxattributes</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">count = response.<span class="gmailmsg"><span style="background:#F9F9F9">NumMessages</span></span></span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="m-1647230982396692319m4349961610504487869m-5630290769599260292inbox-inbox-apple-tab-span"><span style="font-family:"Courier New"">results.append(alias, count)</span></span><span style="font-family:"Courier New""> </span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">results.sort(count, DESC)</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">for each result in results:</span><o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><span class="gmailmsg"><span style="font-family:"Courier New"">print result.alias, results.count</span></span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="gmailmsg">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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="gmailmsg"><a href="https://twitter.com/avholloway45633/status/828515885769953280" target="_blank">https://twitter.com/avholloway45633/status/828515885769953280</a></span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<div>
<div>
<p class="MsoNormal">On Wed, Mar 8, 2017 at 2:52 PM Nick Barnett <<a href="mailto:nicksbarnett@gmail.com" target="_blank">nicksbarnett@gmail.com</a>> wrote:<o:p></o:p></p>
</div>
</div>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<div>
<div>
<div>
<p class="MsoNormal">I found<a href="https://www.cisco.com/c/en/us/support/docs/unified-communications/unity-connection/118299-technote-cuc-00.html#anc8" 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:<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">run cuc dbquery unitymbxdb1 select FIRST
<span class="m-1647230982396692319m4349961610504487869m-5630290769599260292gmailmsg">
<b><span style="background:yellow">10</span></b></span> alias as UserID, count (*) as messages \<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">from vw_message, unitydirdb:vw_mailbox, unitydirdb:vw_user \<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">where mailboxobjectid in \<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">(select mailboxid from vw_mailbox where unitydirdb: vw_user.objectid = unitydirdb:vw_mailbox.userobjectid) \<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">group by alias order by \<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">messages desc<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><o:p> </o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">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.<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><o:p> </o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">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.<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><o:p> </o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">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?<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><o:p> </o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Thanks,<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Nick<o:p></o:p></p>
</div>
</div>
</div>
</div>
<p class="MsoNormal">_______________________________________________<br>
<span class="gmailmsg">cisco-voip mailing list</span><br>
<span class="gmailmsg"><a href="mailto:cisco-voip@puck.nether.net" target="_blank">cisco-voip@puck.nether.net</a></span><br>
<span class="gmailmsg"><a href="https://puck.nether.net/mailman/listinfo/cisco-voip" target="_blank">https://puck.nether.net/mailman/listinfo/cisco-voip</a></span><o:p></o:p></p>
</blockquote>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</blockquote>
</div>
</blockquote>
</div>
</div>
</body>
</html>