ICM Script help

Unanswered Question
Feb 9th, 2009

we are using IPCC 7.1 with CVP 3.1, is there any way to check the caller ANI, if the caller called the call center for more that 5 times a day he should be blocked from calling more than this 5 times per day

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 4 (2 ratings)
david.macias Mon, 02/09/2009 - 09:42

Yes, it's certainly possible, however you'll have to have some sort of web service or database integration to do this. we've set up something similar on my latest project, where the client has a table with blocked numbers and every call is checked against this database. This is done through web services in the ivr.


luai@1978 Mon, 02/09/2009 - 14:34

thanks for reply. but i need to use termination call details record table to check the caller num.but i need an IF condition to check if the caller has called ffor more than 5 times with in one day because i want to block him per day not permenantly

david.macias Mon, 02/09/2009 - 17:25

This is not a good idea, as if you have a high volume call center your queries to the TCD table will impact your call center. the best route is to have an external data source.


luai@1978 Mon, 02/09/2009 - 23:32

ok, i will make an external table for the ANI but still I need the IF condition to check if the caller called for more than 5 times aday or not

david.macias Tue, 02/10/2009 - 03:36

Personally, I would ask a DBA in your company to help you achieve this. There's many ways to do it. You first will need to send every ANI to the database, then maybe have a store procedure which keeps a count of how many times this ANI has appeared in the last 24 hours. Then all you have to do is check this count, if > 5 block it.


baumannsw Wed, 02/11/2009 - 10:44

ICM scripts cannot write to a database, they can only read. I would utilize an IVR or VXML application for what you are describing.

luai@1978 Thu, 02/12/2009 - 12:20

I had made an sql insert trigger that gets the ANI and the datetime for every call that had came in the Termination call detail table and put them into another table, also I ahd create acounter for the number of times the call has came in the table during the day, the problem is that the trigger is not working with the Termination call detail table I mean it is not inserting and data in the junk tablle that I had created, but when I test the trigger with another table that I created by my self it works fine,I dont know what is the issue with Termination call detail table , please advice

Edward Umansky Thu, 02/12/2009 - 14:41

ICM uses SQL Server bulk copy operations to insert data into the TCD table. These operations do not fire triggers. Even if it did would work it would be unsupported and extremely risky, you could corrupt your entire HDS database.

The only TCD based solution that would work is having a custom process periodically reading TCD and moving necessary data to a separate table. You should not do any any joins or group bys directly on the TCD table as it could be severely performance impacting. Per Cisco recommendations, move any data you want to process from the TCD into a separate table using the DBDateTime column to keep track of which records are new. You can then perform any processing you need on that separate data server. This will allow you to see how many times a person called but it would be a polling solution, it wouldn't be counting in real-time.

The only real-time solution is to do what others have suggested and utilize CVP VXML scripting to insert this data into your database.

luai@1978 Fri, 02/13/2009 - 00:05

many thanks, but can you explain this process and how I can use it

geoff@hp.com Fri, 02/13/2009 - 02:09

I like to write custom Java elements to talk to a database for use in CVP VXML. There are other ways of adding Java code to Audium apps, but I prefer a custom action element that has a parallel test class.

Assuming you have Java coding skills, you create an action element that inserts the ANI into a datbase table. It's simple to pass the ANI to the VXML application.

While you are there in CVP VXML, call either some SQL code or a stored procedure to check if the caller has been there 5 times. Return a value to ICM to allow scripting to branch appropriately.

So youi don't need to use DBWorker - just do it all in CVP VXML.

You do need to be able to call database functions in Java. If you are talking to a Microsoft SQL Server, this used to be a little ugly with the JDBC/ODBC bridge; but a few years ago, Microsoft released a native SQL driver so it's a piece of cake. You supply all the credentials in your code.

I like to have the settings of the database component for server name, user name, password etc visible in the Studio application.

Note that you should run this database on a different server besides the CVP VXML server. Cisco don't like it otherwise, mainly because the way SQL grabs the RAM available. It's not so bad in CVP 3.1, with a split CVP and VXML server; but with co-resident setups you end up with two Tomcats and they like about 700MB each.



geoff@hp.com Fri, 02/13/2009 - 02:23

Good info there ed_umansky on the TCDs. Deserves some points.



luai@1978 Fri, 02/13/2009 - 06:18

first I donot have java skills. second we are not using VXML Studio. is there any way to do this with Sql or sql with cisco application gateway

Edward Umansky Fri, 02/13/2009 - 14:54

The Cisco application gateway interface is an API that allows integration of custom applications with ICM scripting. It is a very low level socket API, your application would need to be reading and interpreting the messages byte by byte, it is fairly involved. You would also need Cisco to provide you with the application gateway API specification document. Cisco only provides this to development contract holders. If you have one, you can open a case on that contract and request the application gateway api documentation. I believe Cisco also sells some pre-packaged app gateway solutions via their Advanced Services group.

Again, as long as you follow Cisco best practices, you could periodically "poll" data from the TCD into a staging table, process it, and move it into a table that can be read from an ICM DBlookup step. This will require either a custom developed application or some elaborate SQL jobs.

These are your only options that I can see if you do not have VXML Studio/Server. VXML studio has a built-in database step that would likely meet your needs, so you wouldn't necessarily need java skills.

geoff@hp.com Sat, 02/14/2009 - 09:10

ed_umansky writes:

>VXML studio has a built-in database step that would likely meet your needs, so you wouldn't necessarily need java skills.

Correct. I just don't like it much.



luai@1978 Fri, 02/20/2009 - 05:25

I need someone to help me on this, I had create aview from the tabe termination call detail record with two Colum (ANI,DbDate Time) so now I need to make on insert trigger on this view to insert any data that came in the view, I have done this as creating insert trigger directly is not permitted on Termination call detail table, which I think it is using bulk insert

platkeet Mon, 02/23/2009 - 06:39

Cisco PS offers a packaged App GW product that is geared toward shedding persistent callers by looking up the presented ANI in the TCD table. It is called Gateway*ANI...you can contact custom-application-request@cisco.com for more information (they'll send a datasheet on the application and what's involved).


This Discussion