Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

CDR Search for 911 calls with datatime

Two Issues:

1. I created a query based up the information in http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a00801d71c5.shtml to query all 911 calls at certain time and date. The query appears to be working - I was able to convert the universal datetime into readable time and date. Now the problem is, the time after the computed conversion is not accurate.

For Example:

1081952000 translates to 4/14/04 2:13 PM for me, but in fact CDR Search in CCM reports it as Apr 14, 2004 10:13:31 - which is correct. What I am doing wrong? Time Zone on both server and client are the same.

2. To assist our safety dept with 911 calls - I want to create a .asp page with search field for time and date. Is there anyone out there already doing this and willing to share the code?

Here my query: works good in access - need help incorporating thin in a web page

SELECT dbo_CallDetailRecord.dateTimeOrigination/86400+CDate("1/1/1970") AS Called_At, dbo_CallDetailRecord.callingPartyNumber AS Extension, dbo_CallDetailRecord.originalCalledPartyNumber, dbo_CallDetailRecord.finalCalledPartyNumber, dbo_CallDetailRecord.dateTimeConnect/86400+CDate("1/1/1970 ") AS TimeConnect, dbo_CallDetailRecord.dateTimeDisconnect/86400+CDate("1/1/1970 ") AS Time_Disconnect, dbo_CallDetailRecord.duration, dbo_CallDetailRecord.callingPartyNumberPartition, dbo_CallDetailRecord.originalCalledPartyNumberPartition

FROM dbo_CallDetailRecord

WHERE (((dbo_CallDetailRecord.originalCalledPartyNumber)="911"));

6 REPLIES
Silver

Re: CDR Search for 911 calls with datatime

Are you aware that CDR date/time values are stored in CallManager in UTC/GMT time? You have to apply your local timezone offset from GMT to get the date/time of the call from the callers perspective (assuming both the caller and your time zone are the same).

So while 1081952000 translates to 4/14/04 2:13 PM UTC/GMT, you need to apply the timezone offset to get a more locally comparable time. CDR search is probably doing this for you, which is why you note the discrepancy in times. SQL Server 2000 has some date/time conversion functions to convert to/from UTC that might be of use to you.

One possible suggestion on your where clause: while 911 would be the most commonly dialed string, since you are querying originalCalledPartyNumber vs. finalCalledPartyNumber you may want to consider also comparing to "9911" (assuming 9 is the outside access code) in case some level-headed person uses the outside access code before dialing 911...

New Member

Re: CDR Search for 911 calls with datatime

That was my problem. It worked as soon as I translated UTC/GMT to EST.

Thanks for your help.

sa

New Member

Re: CDR Search for 911 calls with datatime

Hi there,

Could you tell me please how you were able to do that?

Thank

Walid

Silver

Re: CDR Search for 911 calls with datatime

Since the datetime fields are stored as integers containing second values, you just need to subtract the number of seconds your timezone is offset from UTC from each datetime field.

Something like this can convert datetime fields on the fly if you are running CallManager 3.3 or later:

select (datetimeorigination - DATEDIFF(s,0,getutcdate() - getdate()))) as datetimeorigination_tz from calldetailrecord

The above sql snippet uses the timezone defined on the SQL Server (in this case the CallManager) to calculate the timezone offset. The issue here is that if you are retrieving CDR from the CallManager that spans a change into or out of daylight savings time, it will produce inaccurate results. This may not be a problem, but you should know about it.

The best way is to convert the UTC time from the CallManager into a datetime format in your code, and then use your programming environment's datetime conversion functions to convert each datetime to your current timezone.

New Member

Re: CDR Search for 911 calls with datatime

Thanks a lot..

Walid

New Member

Re: CDR Search for 911 calls with datatime

Anyone know how this can be done in CCM 5.0(X)?

575
Views
0
Helpful
6
Replies
CreatePlease login to create content