Python Scripting Host

Version	1.0.9
Last Update	3/10/2015
Statistics	9 meg EXE self extracting install file.  No reboot required.
Compatibility	Connection 7.x, 8.x, 9.x, 10.x, 11.x Tested on Windows 2008 (32/64), 2012 R2 (32/64), Windows 7 (32/64), Windows 8 (32/64) May require administative rights on Windows client Requires the Informix ODBC drivers to be installed with ADO.NET
Support	NOT TAC supported
Current Version	Download Now
Help	Tool Help

Training Videos

All videos in H264 Flash format, should play in most modern browsers

These videos show me actually making most of the scripts shown in the Sample Scripts section below providing some more detail and commentary on what's going on.

Python scripting engine overview, goals, data fetch basics	Play video - 11 minutes
Data fetch intro, writing to logs and files, iterating over a data table	Play video - 12 minutes
Bulk Call Handler creation - stored procs, error handling	Play video - 12 minutes
Reading from and using CSV files to update user properties	Play video - 14 minutes
Creating CSV files from data in Connection	Play video - 12 minutes
Fetching mailbox size for all users	Play vdieo - 18 minutes
Get unread voice message counts for all users (builds on mailobox size video)	Play video - 7 minutes
Update conversation name for all users in a selected Class of Service	Play video - 10 minutes
Disable alternate greeting for all users in a selected partition	Play video - 14 minutes
Reports Examples
Mailbox Store Report, including scheduling and emailing report results	Play video - 12 minutes
Inbox Report	Play video - 10 minutes
Message Activity Report	Play video - 8 minutes
Transfer Call Billing Report	Play video - 4 minutes
Call Handler Activity Report	Play video - 14 minutes

Sample Scripts

Show First 20 User Aliases

import clr
clr.AddReference('System.Data')
from System.Data import DataTable

Helpers.SetActiveDatabase("UnityDirDb")

query = 'SELECT FIRST 20 Alias FROM vw_subscriber'
dtAlias = Helpers.CreateDataTable(query)

assert dtAlias.Rows.Count>0,"No users found!"
print("Processing user count="+ str(dtAlias.Rows.Count))

for row in dtAlias.Rows:
    print("user="+row['alias'])

print("Finished")

Export User Aliases and Extension to CSV

import clr
clr.AddReference('System.Data')
from System.Data import DataTable

Helpers.SetActiveDatabase("UnityDirDb")

#get all users that have an alias that starts with "o"
query = 'SELECT Alias, DTMFAccessID FROM vw_subscriber WHERE alias LIKE ?'
dtAlias = Helpers.CreateDataTable(query,'o%')

assert dtAlias.Rows.Count>0,"No users returned!"

#convert the dataset into a CSV on file.  
Helpers.SaveDataTableAsCsvFile(dtAlias,'AliasExtensionOutput.csv')

print('Finished output, users exported='+str(dtAlias.Rows.Count))

Get Mailbox Size For All Users

import clr 
clr.AddReference('System.Data')
from System.Data import DataTable

Helpers.SetActiveDatabase("UnityDirDb")

userQuery = "SELECT vw_subscriber.Alias, vw_mailboxstoremap.MailDatabase FROM vw_Subscriber, vw_mailboxstoremap, vw_mailboxmap WHERE vw_subscriber.ObjectId = vw_mailboxmap.UserObjectId AND vw_mailboxmap.MailboxStoreObjectId = vw_mailboxstoremap.ObjectId"
dtUsers = Helpers.CreateDataTable(userQuery)
assert dtUsers.Rows.Count>0,"No users found!"

for user in dtUsers.Rows:
    mailboxQuery = "SELECT bytesize FROM %s:vw_mailbox WHERE description=?" % user['maildatabase']
    size = Helpers.GetSingleValue(mailboxQuery,user['alias'])
    if (len(size))==0:
        continue
    print(user['alias']+" mailbox size="+size)

print("done")

Get Unread Voice Message Count For All Users

import clr 
clr.AddReference('System.Data')
from System.Data import DataTable

Helpers.SetActiveDatabase("UnityDirDb")

userQuery = "SELECT vw_subscriber.Alias, vw_mailboxstoremap.MailDatabase, vw_mailboxmap.MailboxId FROM vw_Subscriber, vw_mailboxstoremap, vw_mailboxmap WHERE vw_subscriber.ObjectId = vw_mailboxmap.UserObjectId AND vw_mailboxmap.MailboxStoreObjectId = vw_mailboxstoremap.ObjectId"
dtUsers = Helpers.CreateDataTable(userQuery)
assert dtUsers.Rows.Count>0,"No users found!"

for user in dtUsers.Rows:
    messagesQuery="SELECT count(*) FROM %s:vw_message WHERE type=2 AND seen=0 AND mailboxObjectId=?" % user['maildatabase']
    count = Helpers.GetCount(messagesQuery,user['MailboxId'])
    print (user['alias'] + " message count="+str(count))

print ("done")

Disable Alternate Greetings For All Users In Partition

import clr
clr.AddReference('System.Data')
clr.AddReference('Cisco.Unity.Connection.PythonScriptingHost')
from System.Data import DataTable
from System import DateTime
from Cisco.Unity.Connection.PythonScriptingHost import ProcDataType

Helpers.SetActiveDatabase("UnityDirDb")

query = 'SELECT vw_Subscriber.Alias, vw_Subscriber.CallHandlerObjectID FROM vw_Subscriber, vw_Partition WHERE vw_Subscriber.PartitionObjectId=vw_Partition.ObjectID AND fn_tolower(vw_Partition.Name)=?'
dtUsers = Helpers.CreateDataTable(query,"jeffs test partition")

assert dtUsers.Rows.Count>0,"No users found in partition!"

#use a date in the past for the timeexpires field to disable a greeting 
theDate = DateTime.Now.AddDays(-2)

for user in dtUsers.Rows:
    print ('updating:'+user['alias'])
    Helpers.StartNewCommand('csp_greetingmodify')
    Helpers.AddCommandParam('pCallHandlerObjectid',ProcDataType.Char,user['CallHandlerObjectId'])
    Helpers.AddCommandParam('pGreetingType',ProcDataType.VarChar,'Alternate')
    Helpers.AddCommandParam('ptimeexpires',ProcDataType.DateTime,"NULL")
    Helpers.ExecutProc_NoRet()

Change Conversation For All Users In COS

import clr 
clr.AddReference('System.Data')
clr.AddReference('Cisco.Unity.Connection.PythonScriptingHost')
from System.Data import DataTable
from Cisco.Unity.Connection.PythonScriptingHost import ProcDataType

Helpers.SetActiveDatabase("UnityDirDb")

cosQuery="SELECT vw_subscriber.Alias, vw_subscriber.ObjectId FROM vw_subscriber, vw_cos WHERE vw_cos.ObjectId = vw_subscriber.CosObjectId AND fn_tolower(vw_cos.DisplayName)=?"
dtUsers = Helpers.CreateDataTable(cosQuery,"jeffs test cos")

assert dtUsers.Rows.Count>0,"No users found in that COS"

for user in dtUsers.Rows:
    print(user['alias'])
    Helpers.StartNewCommand("csp_SubscriberModify")
    Helpers.AddCommandParam("pObjectId",ProcDataType.Char,user['objectId'])
    Helpers.AddCommandParam("pConversationTui",ProcDataType.VarChar,"SubMenu_Alternate_Custom")

    try:
        Helpers.ExecutProc_NoRet()
    except(Exception, ex):
        print("Error editing user:"+str(ex))
        continue
    print("user updated!")

Create 100 Call Handlers

import clr 
clr.AddReference('System.Data') 
from System.Data import DataTable 
clr.AddReference('Cisco.Unity.Connection.PythonScriptingHost')
from Cisco.Unity.Connection.PythonScriptingHost import ProcDataType

Helpers.SetActiveDatabase("UnityDirDb")

#grab the first call handler template defined to create new handlers with
query="SELECT FIRST 1 ObjectID from vw_CallHandlerTemplate"
templateObjectId=Helpers.GetSingleValue(query)

assert len(templateObjectId)>0,"No template objectID found"

basename="LoadTestHandler"

queryCount="SELECT COUNT(*) FROM vw_CallHandler WHERE displayname=?"
for x in range(1,100):
    print(basename+str(x))
    count = Helpers.GetCount(queryCount,basename+str(x))
    if (count>0):
        print("Duplicate name!")
        continue
    Helpers.StartNewCommand('csp_CallHandlerCreate')
    Helpers.AddCommandParam('pDisplayName',ProcDataType.VarChar,basename+str(x))
    Helpers.AddCommandParam('pTemplateObjectId',ProcDataType.VarChar,templateObjectId)
    try:
        newObjectId = Helpers.ExecutProc()
    except Exception, e:
        print ('failed creating new handler:%s' % e)
        continue
    print ('    New objectId ='+newObjectId)
print ('Done!')

Set "2" Menu Entry Key for All Users with a Mobile Phone to Transfer to it.

import clr 
clr.AddReference('System.Data')
clr.AddReference('Cisco.Unity.Connection.PythonScriptingHost')
from System.Data import DataTable
from System import DateTime
from Cisco.Unity.Connection.PythonScriptingHost import ProcDataType

Helpers.SetActiveDatabase('UnityDirDb')

#get all users that have a mobile phone alternate extension defined
query = "SELECT vw_subscriber.alias, vw_subscriber.CallHandlerObjectId, vw_dtmfaccessid.DtmfAccessId, vw_menuentry.objectid as menuentryobjectid FROM vw_dtmfaccessid, vw_subscriber, vw_menuentry WHERE vw_dtmfaccessid.Parent_SubscriberObjectId =vw_subscriber.ObjectId AND vw_dtmfaccessid.IdIndex=5 AND vw_menuentry.CallHandlerObjectId = vw_subscriber.CallHandlerObjectId AND vw_menuentry.TouchtoneKey='2'"
dtDevices = Helpers.CreateDataTable(query)

assert dtDevices.Rows.Count>0,"No mobile phone devices found!"
print("Mobile Phone Devices ="+ str(dtDevices.Rows.Count))

for device in dtDevices.Rows:
    print("User="+device['Alias'])

    #set 2 menu entry key to use alternate transfer number
    Helpers.StartNewCommand('csp_menuentrymodify')
    Helpers.AddCommandParam('pCallHandlerObjectId',ProcDataType.Char,device['CallHandlerObjectId'])
    Helpers.AddCommandParam('pTouchToneKey',ProcDataType.Char,'2')

    #action type of 7 is transfer to alternate contact number
    Helpers.AddCommandParam('pAction',ProcDataType.SmallInt,7)
    Helpers.ExecuteProc_NoRet()
    print(" 2 key updated to alternate transfer number")

    #setting the key action to alternate transfer should create an entry for us
    query = 'select objectid from vw_alternatecontactnumber where menuentryobjectid=?'
    strContactNumberObjectId = Helpers.GetSingleValue(query,device['menuentryobjectid'])
    if (strContactNumberObjectId == ""):
        print(" No alternate contact number slot found for this menu entry")
    else: 
        #Update existing alternate transfer number to the mobile phone number
        print(" Updating existing alternate contact number:"+device["dtmfaccessid"])
	Helpers.StartNewCommand('csp_alternatecontactnumbermodify')
	Helpers.AddCommandParam('pObjectId',ProcDataType.Char,strContactNumberObjectId)
	Helpers.AddCommandParam('pTransferNumber',ProcDataType.VarChar,device['dtmfaccessid'])
	Helpers.ExecuteProc_NoRet()
print("done");

Set SMTP Notification Device Text Body for all Users

import clr 
clr.AddReference('System.Data')
clr.AddReference('Cisco.Unity.Connection.PythonScriptingHost')
from System.Data import DataTable
from System import DateTime
from Cisco.Unity.Connection.PythonScriptingHost import ProcDataType

Helpers.SetActiveDatabase('UnityDirDb')

#get all SMTP notification devices that have a non null target email address
#this is stored in the "phonenumber" field for legacy reasons
query = 'SELECT ObjectId FROM vw_Notificationdevicesmtp WHERE phonenumber is not NULL'
dtDevices = Helpers.CreateDataTable(query)

assert dtDevices.Rows.Count>0,"No notification devices found!"
print("Devices ="+ str(dtDevices.Rows.Count))

for device in dtDevices.Rows:
    print("Notification device id="+device['ObjectId'])
    Helpers.StartNewCommand('csp_NotificationDeviceSMTPModify')
    Helpers.AddCommandParam('pObjectId',ProcDataType.Char,device['ObjectId'])
    Helpers.AddCommandParam('pStaticText',ProcDataType.LVarChar,'New Static Text')
    Helpers.ExecuteProc_NoRet()
print("done");