cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements

Community Helping Community

328
Views
40
Helpful
12
Replies
Beginner

UCCX and SQL Integration for Caller ID Information

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
VIP Advisor

Re: UCCX and SQL Integration for Caller ID Information

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

Anthony Holloway

If you found this post helpful, please consider clicking that helpful button!

And for a limited time, by marking helpful posts, you're also helping Doctors Without Borders

View solution in original post

Collaborator

Re: UCCX and SQL Integration for Caller ID Information

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:

 

Capture.PNG

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.

Capture.PNG

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

Capture.PNG

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

 

View solution in original post

12 REPLIES 12
VIP Advisor

Re: UCCX and SQL Integration for Caller ID Information

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

Anthony Holloway

If you found this post helpful, please consider clicking that helpful button!

And for a limited time, by marking helpful posts, you're also helping Doctors Without Borders

View solution in original post

Beginner

Re: UCCX and SQL Integration for Caller ID Information

Hello Anthony,

 

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

DB Get_General.PNG

 

 

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

With Where.PNG

 

Result

Nothing Captured From DB.PNG

 

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

Selecting from the DB and dbo Table Only.PNG

Result in Finesse

Finesse Out Put.PNG

 

Result in DebuggingNumber Not In the Database.PNG

 

DB sourceData Source.PNG

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.

JDBC URL.PNG

 

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

DBACapture.PNGScriptCapture.PNG

VIP Advisor

Re: UCCX and SQL Integration for Caller ID Information

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

Anthony Holloway

If you found this post helpful, please consider clicking that helpful button!

And for a limited time, by marking helpful posts, you're also helping Doctors Without Borders
Beginner

Re: UCCX and SQL Integration for Caller ID Information

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.

 

Capture.PNG

VIP Advisor

Re: UCCX and SQL Integration for Caller ID Information

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"

Anthony Holloway

If you found this post helpful, please consider clicking that helpful button!

And for a limited time, by marking helpful posts, you're also helping Doctors Without Borders
Beginner

Re: UCCX and SQL Integration for Caller ID Information

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.Possible Data Read Solution.PNG

 

 

Collaborator

Re: UCCX and SQL Integration for Caller ID Information

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:

 

Capture.PNG

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.

Capture.PNG

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

Capture.PNG

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

 

View solution in original post

Beginner

Re: UCCX and SQL Integration for Caller ID Information

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.

 

Capture.PNG

 

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

Collaborator

Re: UCCX and SQL Integration for Caller ID Information

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">
<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
 
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?
Collaborator

Re: UCCX and SQL Integration for Caller ID Information

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

Re: UCCX and SQL Integration for Caller ID Information

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.

Beginner

Re: UCCX and SQL Integration for Caller ID Information

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.

CreatePlease to create content
Content for Community-Ad
FusionCharts will render here