UCCX and SQL Integration for Caller ID Information

[adusepeney79]

I have been tasked to retrieve caller ID from internal callers which was a sucess for all extension thanks to Mike who helped me. Now an add on is required because they want to be able to distinguish between callers based on caller's Mobile/Extension and their Title due to some internal preferences.

SQL has been Database has been built with the below tables.

FirstName

LastName

Title

Extension

MobileNumber

from the script, I am able to get the Name of the caller, Title, Extension, and Mobile but somehow it is only the first caller information showing for all callers whose information has been populated into the database.

Somehow also, when the Select * from Database where function is used, the script would skip reading the database all together. When set to just Select * from database, it reads the available information in the database.

I tried using the Java callingNumber info set step in conjunction but the Java version overwrites the SQL database query.

When the names are also able to be retrieved, it is only showing the FirstName due to the length of characters set (50) for each Data Type.

Has anyone successfully been able to connect SQL with UCCX to perform caller ID just by the script collecting the callingnumber and querying it against the SQL without the caller having to provide any input first? More like when you call most companies today and they already know who you are by referencing you calling number with their database.

My customer does not want any caller entering any information at all for this to work.

[Anthony Holloway · Accepted solution]

It sounds like your DB integration is good, but maybe your query is returning more (if not all) results than you expected.

Sometimes, it's not possible to prevent the DB from returning more than one record, because, well, there's more than one match on your query. You could use the LIMIT clause to force it to return just the top result, but then that may not be the result you want.

If you are needing to handle multiple records in the result set, you will need to manually loop over the DB Get step, parsing each row of the result set.

https://developer.cisco.com/docs/contact-center-express/#!db-get-step

And finally, if you are receiving the wrong result set in the first place, hence, you are receiving the wrong record as you mentioned, then you may not be send the query to the DB correctly.

Can you share two things?

1) Can you share the full SQL query from the DB Read step

2) Can you share a screenshot of a Reactive Debug just at the DB Read step, so we can see the value of the variable holding the calling party number

[adusepeney79]

Hello Anthony,

Thank you for your quick on this. Please see below and the attached screen captures.

Selecting from the Database and Table using the variables with "where" and "and" but the script would retrieve nothing from the database.

Result

when the selection is made only straight from the Database and the table as shown,

Result in Finesse

Result in Debugging

DB source

whenever I do:

jdbc:jtds:sqlserver//{serverIP}:1433/instanceName=INSTANCE2;databseName=UCCX_OJP_Customer, the connection fails.

when I do as below, it connects because the SQL dba admin told me they are using high ports.

DBA Screen capture ( the current column Data Types have all been changed to char)

[Anthony Holloway]

Your SQL query is incorrect. You cannot have multiple WHERE clauses.

E.g.,
BAD = select * from table where field1 = value1 where field2 = value2
GOOD = select * from table where field1 = value1 and field2 = value2

[adusepeney79]

Hello Anthony,

I have tried the Query string you provided and whenever that is applied, the Database not to being read in during the Debugging state or just a simple test call. But when I only apply the SELECT* from table, the first only information is retried as usual for all calls. I even tried with and without the "$" but same result. Nothing was retrieved from the table.

Does enabling the Named Pipes on the Protocols for Instance makes any difference? It is not enabled on the SQL server instance my database is on.

I would like to apply account number in the Database as either the extension mobile number to see if that makes any difference. I have a feeling that might help.

[Anthony Holloway]

First, I don't think you meant to use a select on all of those fields in the where clause. The only thing you'll actually know about the caller is their caller ID (calling number) correct? Or am I missing something, and you do know all of that info of the caller before the DB read step?

In other words, just use:

select * from ojp_customer where extension = $cus_Extension

Also, I don't think you'll see a result returned by clicking the Test button with your variables in there. Try temporarily replacing the $cus_Extension with a real value in double quotes for testing, then put it back to the variable when you're done.

E.g.,
select * from ojp_customers where extension = "6125551212"

[adusepeney79]

Hello Anthony,

While writing up the reply, it hit me. Could it be because there is no account number for the DB Read to really rely on so it keeps looking at the first role all the time for all calls?

I was thinking if I work with the DBA guy to create account numbers using the extension and the mobile number for at least the 3 accounts we are using for the testing, whenever the calling number hits, the DB Get will use that number to reference the account number for a match. if not match, then nothing should return from the DB but if there is a match, then it can read the all associated information in that row. The calling number will match against the extension and the mobile number as the account number. Please let me know that you think of this approach.

Like below. I would thin this is potential approach.

[James Hawkins · Accepted solution]

Hi,

Rewinding to the start of your query is this functionality required for internal user or external customers.

If the former then do you not have the details you are putting in the SQL database in some other location such as Active Directory?

If yes then it would be simpler to query that data source rather than go through the hassle of creating a separate database.

If you are going to use SQL then I do not think you should be using INT as the data type for your numbers as it will not support leading zeros or non integer characters such as + and I would recommend you store numbers in E.164 format.

My database would look like that shown below:

The script below would create the database table with unique constraints on Extension and Mobile and a GUID as the primary key.

CREATE TABLE [dbo].[OBJ_Customer](
[pkid] [uniqueidentifier] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[Lastname] [varchar](50) NOT NULL,
[Title] [varchar](50) NOT NULL,
[Extension] [varchar](50) NOT NULL,
[Mobile] [varchar](50) NULL,
CONSTRAINT [PK_OBJ_Customer] PRIMARY KEY CLUSTERED
(
[pkid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [U_Extension] UNIQUE NONCLUSTERED
(
[Extension] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [U_Mobile] UNIQUE NONCLUSTERED
(
[Mobile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OBJ_Customer] ADD CONSTRAINT [DF_OBJ_Customer_pkid] DEFAULT (newid()) FOR [pkid]
GO

I have inserted some data into the table.

Now if I run the SQL query below I get the result I desire.

Query

SELECT FirstName,LastName,Title,Extension,Mobile FROM OBJ_Customer WHERE Extension = '7297' OR Mobile = '7297'

Result

In the SQL query I would put whatever the calling number is as a variable where I show '7297'.

This will find the record that has 7297 in either the Extension of Mobile column. The database constraints prevent multiple records having the same values for these fields.

If I was doing this in my home country where mobile numbers are 11 digits and start with 07 I would probably not use an OR in my SQL statement. Instead I would examine the calling number using some IF substring statements in my CCX script and use separate SQL queries based upon the type of number.

Hope this helps.

James

[adusepeney79]

Hello James,

Thank you for the insight. So we are querying internal users but my environment would likely have me make this possible for many of their external VIPs.

With that said, it probably would be nice and more efficient to look into querying Active Directory as well.

I have used used the below script: 

Set= { java.lang.reflect.Field ciscoCallField = contact.getClass().getDeclaredField("ciscoCall"); ciscoCallField.setAccessible(true); com.cisco.jtapi.extensions.CiscoCall ciscoCall = (com.cisco.jtapi.extensions.CiscoCall) ciscoCallField.get(contact); return ciscoCall.getCurrentCallingPartyDisplayName(); }

to get caller names based on their extensions but they also want mobile numbers and Job tittles which I couldn't find any better solution or how to modify the script to pull all of the needed information than what you are describing. If you do have a solution, please do share as I would be very glad with it.

The Data Type you suggested was modified last week from int to nvarchar and still nothing. What we havent applied is the SQL script you have provided so I will work with the SQL team on on.

When I applied SELECT First_Name,Last_Name,Title,Extension,Mobile_Number FROM OJP_Customer WHERE Extension = '2222' OR Mobile = '2222' no data came up.

Thanks

[James Hawkins]

Hi,

I would concentrate on getting your SQL query working in SQL Server Management Studio before going anywhere near CCX. the table and query are simple so there should not be any issues doing this.

You may need to tell SMSS to use the particular database in the query Window by using the USE command as shown below

USE UCCX_OJP_Customer;

SELECT First_Name,Last_Name,Title,Extension,Mobile_Number FROM OJP_Customer WHERE Extension = '2222' OR Mobile = '2222' 

If you only want to query numbers for users in CUCM you could try using the CUCM UDS API. This is a REST based API which allows queries to be made for user data (amongst other things) and returns them as XML. 

The query below queries UDS for a user which has 1111 in Telephone Number, Home Number or Mobile Number in their CUCM user account.

https://10.1.49.1:8443/cucm-uds/users?number=1111

The query returns an XML object containing the user data:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<users uri="https://10.1.49.1:8443/cucm-uds/users" version="12.0.1" start="0" requestedCount="64" returnedCount="1" totalCount="1">

<user uri="https://10.1.49.1:8443/cucm-uds/user/resttest">

<id>737d26a7-d7cb-112f-06ba-acac65d5fc9d</id>

<userName>resttest</userName>

<firstName>Rest</firstName>

<lastName>Test</lastName>

<middleName></middleName>

<nickName></nickName>

<displayName></displayName>

<phoneNumber>1111</phoneNumber>

<homeNumber>2222</homeNumber>

<mobileNumber>3333</mobileNumber>

<email></email>

<directoryUri></directoryUri>

<msUri></msUri>

<department>Department of REST Testing</department>

<manager></manager>

<title>Rest Test User</title>

<pager>4444</pager>

</user>

</users>

You could use the CCX Make REST Call step to query UDS then parse the returned XML for user names, title, department etc.

Some links that may help

https://d1nmyq4gcgsfi5.cloudfront.net/site/user-data-services/learn/how-to/explore-uds-apis/# 

https://developer.cisco.com/docs/user-data-services-api-reference/#!user-data-services-uds-api-reference/users-request-format 

https://developer.cisco.com/docs/contact-center-express/#make-rest-call 

One annoyance is that the REST request chokes if an E164+ format number is used for the search - e.g. +447539841234 - it does not seem to like the leading + character - anyone have ideas how to get around that?

[James Hawkins]

To answer my own query the numberlast query type copes with E.164+ numbers just fine.

https://10.1.49.1:8443/cucm-uds/users?numberlast=+447539123456

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<users uri="https://10.1.49.1:8443/cucm-uds/users" version="12.0.1" start="0" requestedCount="64" returnedCount="1" totalCount="1">

<user uri="https://10.1.49.1:8443/cucm-uds/user/resttest">

<id>737d26a7-d7cb-112f-06ba-acac65d5fc9d</id>

<userName>resttest</userName>

<firstName>Rest</firstName>

<lastName>Test</lastName>

<middleName></middleName>

<nickName></nickName>

<displayName></displayName>

<phoneNumber>1111</phoneNumber>

<homeNumber>2222</homeNumber>

<mobileNumber>+447539123456</mobileNumber>

<email>rest@test.com</email>

<directoryUri></directoryUri>

<msUri></msUri>

<department>Department of REST Testing</department>

<manager></manager>

<title>Rest Test User</title>

<pager>4444</pager>

</user>

</users>

[adusepeney79]

Thank you James and Anthony for all of the information you've provided. Currently I have reached out to an SQL senior admin as it turns out the admin I have been working with only builds the servers, maintains, creates instances and a few other things. The senior SQL admin is the real deal because he has built many databases ans scripts for the environment. Not sure why I didn't even reach out to him in the first place. Tomorrow we should be able to figure something out and work things through.

I will post update(s) as they become available.

[adusepeney79]

After working with the SQL Snr Admin, I was referred back to the original admin I have been working with. He told me there was something he had to perform about the output "null" and once he did that, everything started to work exactly as expected.

the clauses used are the SELECT * FROM [Database_Name].[dbo].[Database_Table] WHERE Extension = $caller_Extension OR Mobile_Number = $caller_MobileNumber

I am thankful that both you two spent time and efforts in helping me to get a successful result.