Pull Call Stats from Cisco UCCX to Servicenow through JDBC connection.

[Jeffrey Siegel]

I need to automate the retrieval of data from my Cisco UCCX server to ServiceNow.  Currently we are having the Cisco UCCX Server email reports to me, which we then manually manipulate and then import into ServiceNow via .xlsx and .csv file formats.  We then transform this to the proper tables.

I need to setup the same without the manual intervention portion.  I have successfully installed the Informix ODBC Driver and configured it to talk to the Cisco UCCX server.  Has anyone been able to have ServiceNow use an ODBC connection via the Midserver to access a database?  I've tried to use the JDBC method, and all 3 of the database types, but get an error like this when attempting to load 20 test records: "the driver received an unexpected pre-login response. Verify the connection properties and check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. This driver can be used only with SQL Server 2005 or later."

[Jeffrey Siegel · Accepted solution]

I was able to figure this out.  I will outline the steps i took for others to use if needed:

1. Download the JDBC Drivers (.jar) from: https://dbschema.com/jdbc-driver/Informix.html
   1.      note the Java Driver Class from page: com.informix.jdbc.IfxDriver
   2.      note the URL from page: jdbc:informix-sqli://HOST:PORT/DB:informixserver=PARAM
2. All - System Import Sets - Data Sources.
   1. Click New.
   2. Right Click on Format -> Show Choice List. (say no if prompted to save record)
   3. Click on New
   4. on new form:
      1.     give label as desired, i entered "Informix"
      2.     for Value, enter the Java Driver Class from step 4 above: com.informix.jdbc.lfxDriver
      3.     Dependant Value: JDBC
      4.     Press Update to save the record
3. in the SNOW instance,  goto Mid Servers - JAR Files
   1. create 2 new entries here:
      1. Name: Informix JDBC
      2. Version: 4.50.4.1 (from file name of jdbc-4.50.4.1.jar)
      3. Attach jdbc-4.50.4.1.jar to the record
      4. Save/Update (mid servers may restart to populate the driver across all the MidServers)
   2. New JAR File (again)
      1. Name: Informix BSON
      2. Version: 4.2.0 (from filename)
      3. attach bson-4.2.0.jar to record
      4. save/update (Mid Servers may restart to load this jar file)
4. All - System Import Sets - Data Sources.
   1. Click New (this time for real)
   2. Right Click on header - Configure - Form Layout
   3. Ensure "Connection URL" is added on the right. 
      1. Save once added or cancel if already there
   4. Name: Give name of type of Import set you are creating.
   5. Import set table label - give name of table for the temporary data to be housed in.
   6. type: JDBC
   7. Use Mid Server (if you have one and need one, select one from the reference field)
   8. Format: Informix
   9. Database Name: db_cra
   10. Database Port: 1504
   11. Username: uccxhruser
   12. password: <<PASSWORD FROM UCCX SERVER>>
   13. Server: server name (i used the same as the url i use to access the reports)
   14. Query (specific SQL)
   15. SQL Statement:  from the Cisco report server, i ran the report that contained the data i wanted to show,  this was so much easier than building the Informix language to select the fields and tables. a full list of the available reports can be found at: https://community.cisco.com/t5/collaboration-knowledge-base/cuic-uccx-stored-procedure-list-pulled-f...   I ran the report on the Cisco Unified Intelligence Center, and went to the tools icon, and selected SQL to get the SQL statement that it used to generate the report i wanted to bring over.
   16. Connection URL: adjust the information gathered in 1)b) above to match your Cisco Instance:
       jdbc:informix-sqli://<<insert host name here>>:<<insert port here typically 1504>>/db_cra:INFORMIXSERVER=<<insert Servername here>>
       (servername is typically the same as the hostname with all dashes converted to underscores and _uccx appended to the end, and if your host starts with a number, add an i to the beginning) 
   17. save and test load 20 records.
   18. Setup transform map. and you should be all done!

[SirishM]

Hi @Jeffrey Siegel , I followed the same steps but i am getting this error Cannot invoke "java.util.Map.keySet()" because "this.fImportSourceFields" is null

Do you ever had this issue?

Thanks,

[Jeffrey Siegel]

Not that i can recall.

[Kris Moncada]

Hi @SirishM and @Jeffrey Siegel ,

Was hoping to get this working as well, and ran into that same issue.  Did either of you find a solution to the above error message?