SQL Query from IP Phone

[eg183100]

Hello,

Does anyone know how to get an sql query from the ip phone.

I want to get information about calls out of the CDR database of the Callmanager

Greetings,

Erik

[vishesha]

You will have to write a ASP (VBscript ) code to call a SQL database store procedure.

If you can specify what type of calls information you want to query ,then i will send you the SP for that with VBScript code.

Thanks

Sandeep.

[eg183100]

Hello,

Thanks for your response.

I want to get the information about calls with duration 0 and the get the calling number for the callednumber i specify.

I want to make a button on the ip phone to get this list. so the customer can look which missedcalls he got.

Regards,

Erik

[stephan.steiner]

Uh, to get the missed calls, just press the directory button and press 4 - that'll give you dialed numbers, missed calls and accepted calls.

[eg183100]

Hello,

Yes i know that, but i only want to see the numbers coming in on the primary line., with your method. yoe see a list of all incoming numbers on all configured DN's (shared and normal) and you cannot see which numbers came in on what line.

Erik

[bankofbenton]

Hi Sandeep,

I am looking for a way to query CDR database as well. I need the code to allow me to search for a specific mm/dd/yy range (for an entire month) to pull records for a specific extension. I do not know how to convert the date/time that CM records into a mm/dd/yy, Central Standard Time format either. The CDR reports are messy and truncates the data after 100 records. We need to have an entire month of detail call activity in order to reconcile our phone bills - some extensions have to be pulled out and billed separately. Our old PBX system did this for us. Can you send me the VBscript code to do this?

Thanks for your help.

[aaronw.ca]

In writing or modifying your SQL query, the CDR documentation on Cisco Developer Support will be very useful, so I would suggest you take a look (requires CCO account):

http://www.cisco.com/pcgi-bin/dev_support/access_level/product_support

The three date/time fields in CDR (dateTimeOrigination, dateTimeConnect, dateTimeDisconnect) are stored as integers representing the number of seconds since midnight January 1, 1970, in UTC time. Since CDT is currently 5 hours behind UTC, you'll need to subtract 18000 seconds (5 hours multiplied by 3600 seconds per hour) from these fields to get an integer representing your timezone.

If you use MS Excel, a formula such as

=A1/86400+DATE(70,1,1)

will display a readable date/time value, which might be useful while analyzing the CDR. Format the cell containing that formula for date/time format, and put the integer date/time value from CallManager's datetime fields (or the value you modified above) in cell A1.

There are a number of other issues that you will be facing in analyzing the CDR, such as the fact that in a transfer, the transferred party always becomes the calling party. This means that an outgoing call can be made to look like an incoming call after a transfer, and you have to know what to look for in the CDR in order to follow the call sequence and fix this and other similar issues (this is one of the issues Call Accounting packages resolve). This may cause you difficulties in reconciling with your phone bill - just want to warn you up front - check the documentation from Cisco Developer Support for details...