<div dir="ltr">I don't think the CUC API has an arbitrary SQL execution method call like AXL does.<div><br></div><div>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><br></div><div>Here's a high level program flow in no particular actual language:</div><div><br></div><div><font face="monospace">results = Array()</font></div><div><div><font face="monospace">response = HTTP GET https://<connection-server>/vmrest/users</font></div><div><font face="monospace">for each user in response.users:</font></div><div><font face="monospace"><span class="inbox-inbox-Apple-tab-span" style="white-space:pre"> userobjectid = response.</span><span style="background-color:rgb(249,249,249)">ObjectId</span></font></div><div><font face="monospace"><span style="white-space:pre"> alias = response.Alias</span> <span style="white-space:pre"><br></span></font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>response = HTTP GET https://<connection server>/vmrest/users/<userobjectid>/mailboxattributes</font></div><div><font face="monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>count = response.<span style="background-color:rgb(249,249,249)">NumMessages</span></font></div><div><font face="monospace"><span class="inbox-inbox-Apple-tab-span" style="white-space:pre"> results.append(alias, count)</span> </font></div><div><font face="monospace">results.sort(count, DESC)</font></div><div><font face="monospace">for each result in results:</font></div></div><div><span style="white-space:pre"><font face="monospace"> print result.alias, results.count</font></span><br></div><div><span style="white-space:pre"><br></span></div><div><span style="white-space:pre">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><span style="white-space:pre"><br></span></div><div><span style="white-space:pre"><a href="https://twitter.com/avholloway45633/status/828515885769953280">https://twitter.com/avholloway45633/status/828515885769953280</a></span><br></div><div><span style="white-space:pre"><br></span></div></div><br><div class="gmail_quote"><div dir="ltr">On Wed, Mar 8, 2017 at 2:52 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">I found<a href="https://www.cisco.com/c/en/us/support/docs/unified-communications/unity-connection/118299-technote-cuc-00.html#anc8" class="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="gmail_msg"><div class="gmail_msg"><br class="gmail_msg"></div><div class="gmail_msg"><p class="MsoNormal gmail_msg">run cuc dbquery unitymbxdb1 select FIRST <b class="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="gmail_msg">10</span></b> alias as UserID,
count (*) as messages \<span class="gmail_msg"></span></p>
<p class="MsoNormal gmail_msg">from vw_message, unitydirdb:vw_mailbox, unitydirdb:vw_user \<span class="gmail_msg"></span></p>
<p class="MsoNormal gmail_msg">where mailboxobjectid in \<span class="gmail_msg"></span></p>
<p class="MsoNormal gmail_msg">(select mailboxid from vw_mailbox where unitydirdb:
vw_user.objectid = unitydirdb:vw_mailbox.userobjectid) \<span class="gmail_msg"></span></p>
<p class="MsoNormal gmail_msg">group by alias order by \<span class="gmail_msg"></span></p>
<p class="MsoNormal gmail_msg">messages desc<span class="gmail_msg"></span></p><p class="MsoNormal gmail_msg"><br class="gmail_msg"></p><p class="MsoNormal 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 gmail_msg"><br class="gmail_msg"></p><p class="MsoNormal 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="gmail_msg"></p><p class="MsoNormal gmail_msg"><br class="gmail_msg"></p><p class="MsoNormal 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 gmail_msg"><br class="gmail_msg"></p><p class="MsoNormal gmail_msg">Thanks,</p><p class="MsoNormal gmail_msg">Nick</p></div></div>
_______________________________________________<br class="gmail_msg">
cisco-voip mailing list<br class="gmail_msg">
<a href="mailto:cisco-voip@puck.nether.net" class="gmail_msg" target="_blank">cisco-voip@puck.nether.net</a><br class="gmail_msg">
<a href="https://puck.nether.net/mailman/listinfo/cisco-voip" rel="noreferrer" class="gmail_msg" target="_blank">https://puck.nether.net/mailman/listinfo/cisco-voip</a><br class="gmail_msg">
</blockquote></div>