[cisco-voip] Execute sql query via vmrest on CUC (or alternatative approach)?

[Anthony Holloway]

I don't think the CUC API has an arbitrary SQL execution method call like
AXL does.

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.

Here's a high level program flow in no particular actual language:

results = Array()
response = HTTP GET https://<connection-server>/vmrest/users
for each user in response.users:
userobjectid = response.ObjectId
alias = response.Alias
response = HTTP GET https://<connection
server>/vmrest/users/<userobjectid>/mailboxattributes
count = response.NumMessages
results.append(alias, count)
results.sort(count, DESC)
for each result in results:
print result.alias, results.count

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.

https://twitter.com/avholloway45633/status/828515885769953280


On Wed, Mar 8, 2017 at 2:52 PM Nick Barnett <nicksbarnett at gmail.com> wrote:

> I found this SQL query
> <https://www.cisco.com/c/en/us/support/docs/unified-communications/unity-connection/118299-technote-cuc-00.html#anc8>
> 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:
>
> run cuc dbquery unitymbxdb1 select FIRST *10* 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
>
>
> 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.
>
>
> 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.
>
>
> 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?
>
>
> Thanks,
>
> Nick
> _______________________________________________
> cisco-voip mailing list
> cisco-voip at puck.nether.net
> https://puck.nether.net/mailman/listinfo/cisco-voip
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://puck.nether.net/pipermail/cisco-voip/attachments/20170308/d370c79e/attachment.html>