Solved: CDR-SQL Consult

david-lima · ‎11-28-2007

Hi friends, does somebody knows how to convert the values of the fields like DATETIMEORIGINATION or DATETIMECONNECT in date format in CDR database, using queries?

Thanks for your help

Best Regards

David

Rob Huffman · ‎12-04-2007

Hi David,

No worries my friend :) This doc actually is based on CCM 3.3 so you should be good to go. Have a look at Cause 1 and 2 instead of Cause 3 (which is specifically for CCM 4.1(3)

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a008056bd4e.shtml

Hope this helps! Let us know.

Rob

Please remember to rate helpful posts.....

View solution in original post

Rob Huffman · ‎11-28-2007

Hi David,

Hope all is well with you! I beleive this is referred to as "Universal Time". Here is some related info;

Date Format in the CDR Database

In order to select all calls after a certain date, you need to convert the date you want into a value in universal time and in seconds since January 1st, 1970.

For example, 973995954 translates to 11/12/00 2:25 AM. Complete these steps in order to decipher the time stamp.

Go to Microsoft Excel.

In cell A1 type the number that is found in the last record for dateTimeOrigination.

In cell A2, paste the formula =A1/86400+DATE(1970,1,1).

Right-click on cell A2 and select format cells.

Under the Number tab select Time where the format is 3/14/98 130 PM.

The result is the actual time in readable format.

From this excellent doc;

Using SQL Queries to Search the Call Detail Record with Cisco CallManager

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a00801d71c5.shtml#understand

Hope this helps!

Rob

PS: I think these tools work as well :)

http://www.iwebtool.com/unix_time_converter

david-lima · ‎12-04-2007

Thanks Rob for you help. If isn't a problem, I have one more question about this matter, when I try to run CAR/ART reports, it show me: "30023:Data is not Available for the range selected", I read the Document ID: 68140 - DATA IS ABSENT IN REPORTS...- that explain this cause but for CCM 4.1(3) and later and I have CCM 3.3(4), I try to apply this solution but the COMMENT FIELD/CDR DB doesn't exist. Is there another procedure to fix this problem?

Thanks

David

Rob Huffman · ‎12-04-2007

Hi David,

No worries my friend :) This doc actually is based on CCM 3.3 so you should be good to go. Have a look at Cause 1 and 2 instead of Cause 3 (which is specifically for CCM 4.1(3)

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a008056bd4e.shtml

Hope this helps! Let us know.

Rob

Please remember to rate helpful posts.....

david-lima · ‎12-28-2007

Thanks a lot Rob for your help and suggestions.

Best regards

David

Rob Huffman · ‎12-28-2007

Hi David,

You are most welcome my friend!

Take care,

Rob