Cobras not capturing SMS notification device

[jeff.singh_2]

              hi, we are migrating from unity 7.0x to Unity connection 8.6x . Cobras is not capturing the smpp provider and sms notification (as per documentation). Is there anyway we can do a bulk update in Unity Connection to add the sms notification device. I have the user details and mobile numbers (exported from Unity using subscriber information dump) - just can't create the column headers in the csv to bulk update in Connection....Jeff     

[lindborg · Accepted solution]

There's nothing "off the shelf" for importing that data but you can construct something reasonably easily using the Python Scritping Service - I have some examples of reading data in via CSV and using it to call stored procedures in Unity Connection that would do what you needed it to - you can check out the service, training material and example scripts (all very short) here:

http://www.ciscounitytools.com/Applications/CxN/PythonScriptingHost/PythonScriptingHost.html

if you need help setting up the stored proc, let me know...

[jeff.singh_2]

Mr Lindborg,

thanks for reply , I will need some help with the stored proc - anything you have will be helpful.

[lindborg · Accepted solution]

Just sat down and wrote this script and tried it out - seems to work ok against 9.1 and 10.0 systems using the Python service noted above.

The CSV file I created was very simple, looked like this:

ALIAS, SMS_DEVICE_NAME, SMS_DEVICE_DESTINATION

jlindborg, TestSMS Device,  jlindborg@test.com

jsmith, TestSMS Device, jsmith@test.com

if you have different column header names in your CSV file you'll want to take that into account.  The script just opens that file, finds the user in Connection by alias and creates a new SMS device with the name, the destination, and makes it active.  If there's already a device by that name it'll fail of course - you'll see a "fk_constraint" error (Foreign Key constraint violation).

Anyway - this should get you pointed in the right direction anyway.

==============

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")

dtUsers = Helpers.ReadCsvFileIntoDataTable('c:\users.csv')

print("Users from Csv="+ str(dtUsers.Rows.Count))

#get the first SMS device defined in the system - if you only have one this works fine

strSmppObjectId=Helpers.GetSingleValue("SELECT ObjectId From vw_SmppProvider")

if len(strSmppObjectId)==0:

    print("No Smpp provider found! Exiting")

    raise SystemExit

print ("Smpp provider id=" + strSmppObjectId)

for user in dtUsers.Rows:

    print(user['alias'])

    #find the user

    strUserObjectId = Helpers.GetSingleValue("SELECT ObjectId FROM vw_Subscriber WHERE fn_tolower(alias)=?",user['alias'].lower())

    if len(strUserObjectId)==0:

        print('User not found in Connection by alias')

        continue

    print("user id="+strUserObjectId)

    Helpers.StartNewCommand('csp_NotificationDeviceSmsCreate')

    Helpers.AddCommandParam('pSubscriberObjectId',ProcDataType.Char,strUserObjectId)

    Helpers.AddCommandParam('pSmppProviderObjectId',ProcDataType.Char,strSmppObjectId)

    Helpers.AddCommandParam('pDisplayName',ProcDataType.VarChar,user['SMS_DEVICE_NAME'])

    Helpers.AddCommandParam('pActive',ProcDataType.Boolean, True)

    Helpers.AddCommandParam('pRecipientAddress',ProcDataType.LVarChar,user['SMS_DEVICE_DESTINATION'])

    try:

        newObjectId = Helpers.ExecutProc()

    except Exception, e:

        print ('failed creating new notification device:%s' % e)

        continue

    print ('    New device objectId ='+newObjectId)

[jeff.singh_2]

Hi Mr Lindborg,

this worked great thank you . is there a way I can get the from field and text field populated .

so from will be something like unity pilot number and text will be 'you have a new voicemail. dial  via number xxyyzz etc'

[lindborg]

sure - just add these lines to the stored procedure construction section:

    Helpers.AddCommandParam('pStaticText',ProcDataType.LVarChar,'Confidential voice mail message')

    Helpers.AddCommandParam('pSenderAddress',ProcDataType.LVarChar,user['USER_SMTP'])

that assumes you have a new column in your CSV file called "USER_SMTP" to act as the sender address - stick whatever static string you want into the static text field up to 768 characters...

[jeff.singh_2]

hi, this is what i have and it fails this time- must have syntax wrong where i have added the two additional lines.

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")

dtUsers = Helpers.ReadCsvFileIntoDataTable('c:\users.csv')

print("Users from Csv="+ str(dtUsers.Rows.Count))

#get the first SMS device defined in the system - if you only have one this works fine

strSmppObjectId=Helpers.GetSingleValue("SELECT ObjectId From vw_SmppProvider")

if len(strSmppObjectId)==0:

    print("No Smpp provider found! Exiting")

    raise SystemExit

print ("Smpp provider id=" + strSmppObjectId)

for user in dtUsers.Rows:

    print(user['alias'])

    #find the user

    strUserObjectId = Helpers.GetSingleValue("SELECT ObjectId FROM vw_Subscriber WHERE fn_tolower(alias)=?",user['alias'].lower())

    if len(strUserObjectId)==0:

        print('User not found in Connection by alias')

        continue

    print("user id="+strUserObjectId)

    Helpers.StartNewCommand('csp_NotificationDeviceSmsCreate')

    Helpers.AddCommandParam('pSubscriberObjectId',ProcDataType.Char,strUserObjectId)

    Helpers.AddCommandParam('pSmppProviderObjectId',ProcDataType.Char,strSmppObjectId)

    Helpers.AddCommandParam('pDisplayName',ProcDataType.VarChar,user['SMS_DEVICE_NAME'])

    Helpers.AddCommandParam('pActive',ProcDataType.Boolean, True)

    Helpers.AddCommandParam('pRecipientAddress',ProcDataType.LVarChar,user['SMS_DEVICE_DESTINATION'])

Helpers.AddCommandParam('pStaticText',ProcDataType.LVarChar,'Confidential voice mail message')

    Helpers.AddCommandParam('pSenderAddress',ProcDataType.LVarChar,user['USER_SMTP'])

    try:

        newObjectId = Helpers.ExecutProc()

    except Exception, e:

        print ('failed creating new notification device:%s' % e)

        continue

    print ('    New device objectId ='+newObjectId)

[lindborg]

what's the error it generates?

did you add a new column for 'USER_SMTP' in the CSV file it's reading from?

[jeff.singh_2]

will get the error to you tomorrow. yes i did add the column in the csv

[jeff.singh_2]

Error on line 2, System.AggregateException: One or more errors occurred. ---> IronPython.Runtime.UnboundNameException: name 'clr' is not defined

   at IronPython.Runtime.Operations.PythonOps.GetVariable(CodeContext context, String name, Boolean isGlobal, Boolean lightThrow)

   at IronPython.Compiler.LookupGlobalInstruction.Run(InterpretedFrame frame)

   at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)

   at Microsoft.Scripting.Interpreter.LightLambda.Run2[T0,T1,TRet](T0 arg0, T1 arg1)

   at IronPython.Compiler.PythonScriptCode.RunWorker(CodeContext ctx)

   at IronPython.Compiler.PythonScriptCode.Run(Scope scope)

Error on line 2, System.AggregateException: One or more errors occurred. ---> IronPython.Runtime.UnboundNameException: name 'clr' is not defined

   at IronPython.Runtime.Operations.PythonOps.GetVariable(CodeContext context, String name, Boolean isGlobal, Boolean lightThrow)

   at IronPython.Compiler.LookupGlobalInstruction.Run(InterpretedFrame frame)

   at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)

   at Microsoft.Scripting.Interpreter.LightLambda.Run2[T0,T1,TRet](T0 arg0, T1 arg1)

   at IronPython.Compiler.PythonScriptCode.RunWorker(CodeContext ctx)

   at IronPython.Compiler.PythonScriptCode.Run(Scope scope)