I know we use UDD, but we just pull 7-day message count and total message size
- because lets be honest, size is more important than message count.
Ben Amick
Telecom Analyst
From: cisco-voip [mailto:[email protected]] On Behalf Of
Anthony Holloway
Sent: Wednesday, March 08, 2017 5:34 PM
To: Nick Barnett <[email protected]>
Cc: Cisco VoIP Group <[email protected]>
Subject: Re: [cisco-voip] Execute sql query via vmrest on CUC (or alternatative
approach)?
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.
On Wed, Mar 8, 2017 at 4:28 PM Anthony Holloway
<[email protected]<mailto:avholloway%[email protected]>>
wrote:
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.
import requests
from requests.packages.urllib3.exceptions import InsecureRequestWarning
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
api_host =
"cucpub.company.com<http://cp.mcafee.com/d/5fHCN0q6xAgdEIf6zCWaryqpKVJ55BZBcsehd79J55BZBcsY-Orhhpvuv7ffK6Qkn3hOqerTKzsSgRmlyEa9JGX3oSVsSjrlS6NJOVJ1UsZuPP_nUQszDT7bZuVtdBMQsYyUUesuuWyaqRQRrIesG7DR8OJMddECS3t-Kyyeso7nvd79KVI07pKOPp3BRJwmVsTqlblbCqOmbAaJMJZ0kIToHMd9_7wqrpLtVMS2_id41FrJaBGBPdpb6BQQg3gvZCV-q8a2NVEw5C_ymd41CmCy0OH7Wjd45o6y0i2oAq83hhm9EwQ-vNxIZD8PVEwRpqdtIjd598SYyrRlKzsCO>"
api_username = "cucadmin"
api_password = "Password123!"
users_per_page = 50
current_page = 1
url_users =
"https://{0}/vmrest/users?rowsPerPage={1}&pageNumber={2}<https://%7b0%7d/vmrest/users?rowsPerPage=%7b1%7d&pageNumber=%7b2%7d>"
url_user =
"https://{0}/vmrest/users/{1}/mailboxattributes<https://%7b0%7d/vmrest/users/%7b1%7d/mailboxattributes>"
headers = {'Accept': 'application/json'}
response = requests.get(url_users.format(api_host, 0, current_page),
auth=(api_username, api_password), verify=False, headers=headers)
data = response.json()
total_users = int(data['@total'])
print "Getting message counts for a total of {0} users...\n".format(total_users)
print "Alias,Message_Count"
for page in range(total_users / users_per_page + 1):
current_page = page + 1
response = requests.get(url_users.format(api_host, users_per_page,
current_page), auth=(api_username, api_password), verify=False, headers=headers)
data = response.json()
users = data['User']
for user in users:
obj = user['ObjectId']
alias = user['Alias']
response = requests.get(url_user.format(api_host, obj), auth=(api_username,
api_password), verify=False, headers=headers)
data = response.json()
message_count = data['NumMessages']
print "{0},{1}".format(alias, message_count)
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.
On Wed, Mar 8, 2017 at 4:08 PM Nick Barnett
<[email protected]<mailto:[email protected]>> wrote:
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
<[email protected]<mailto:[email protected]>> 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<https://%3cconnection-server%3e/vmrest/users>
for each user in response.users:
userobjectid = response.ObjectId
alias = response.Alias
response = HTTP GET https://<connection<https://%3cconnection>
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<http://cp.mcafee.com/d/1jWVIgdEIf6zCWaryqpKVJ55BZBcsehd79J55BZBcsY-Orhhpvuv7ffK6Qkn3hOqerTKzsSgRmlyEa9JGX3oSVsSjrlS6NJOVJ1UsZuPP_nUQszDT7bZuVtdBMQsYyUUesuuWyaqRQRrIesG7DR8OJMddFCS3t-Kyyeso7nvd79KVIDeqR4IOpykV_7BYQOjGuvkNFpxZNBDajSrtcujuvhvuuv7e6zAQsLIFLndSBiRiVCIByV2Hsbvg5bdSaY3ivNU6CSrTusdwLQzh0qmXiFqFsPmiNFtd40Q7_pKvCy2wIuq81pLUBzh0pBFEwcGN-APh1m1Ew4wC96y0Qklyq8dfDYorfpOc-q8dmmznr4PhiidL8CR6g3>
On Wed, Mar 8, 2017 at 2:52 PM Nick Barnett
<[email protected]<mailto:[email protected]>> 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
[email protected]<mailto:[email protected]>
https://puck.nether.net/mailman/listinfo/cisco-voip<http://cp.mcafee.com/d/k-Kr6h8edEIf6zCWaryqpKVJ55BZBcsehd79J55BZBcsY-Orhhpvuv7ffK6Qkn3hOqerTKzsSgRmlyEa9JGX3oSVsSjrlS6NJOVJ1UsZuPP_nUQszDT7bZuVtdBMQsYyUUesuuWyaqRQRrIesG7DR8OJMddICS3t-Kyyeso7nvd79KVIDeqR4INpKNnwqj-f0T1dnoovaAVgtHBFkJkKpH9oTqlblbCqOmbAaJMJZ0kIToHMd9_7wqrpLtVMS2_id41FrJaBGBPdpb6BQQg3gvZCV-q8a2NVEw5C_ymd41CmCy0OH7Wjd45o6y0i2oAq83hhm9EwQ-vNxIZD8PVEwRpqdtIjd598SYyr_dnI>
Confidentiality Note: This message is intended for use only by the individual
or entity to which it is addressed and may contain information that is
privileged, confidential, and exempt from disclosure under applicable law. If
the reader of this message is not the intended recipient or the employee or
agent responsible for delivering the message to the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited. If you have received this communication
in error, please contact the sender immediately and destroy the material in its
entirety, whether electronic or hard copy. Thank you_______________________________________________
cisco-voip mailing list
[email protected]
https://puck.nether.net/mailman/listinfo/cisco-voip
