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

[Nick Barnett]

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.

Thanks,
Nick

On Wed, Mar 8, 2017 at 3:40 PM, Anthony Holloway <
avholloway+cisco-voip at gmail.com> wrote:

> 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/08be63f8/attachment.html>