04-06-2008 07:46 AM - edited 03-15-2019 09:53 AM
When we export CDR/CMR file and view it in excel some fields (date/time/ip address) is not showing properly
What could be the problem?
04-06-2008 10:46 AM
most of the timestamps are provided in unix epoch time, you need to convert it
Did you checked how the info is provided??
Cisco CallManager 4.1(3) Call Detail Record Definition
http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/cdr_defs/4_x/cdr413.html
also check the time on the server to be correct
HTH
javalenc
if this helps, please rate
04-07-2008 01:24 AM
As per document we are seeing date and Time in UTC format.
IP address also is showing negative.
As per document we need to convert it to hex and then to decimal.
How we will be able to see CDR table report in proper format(date,time and ip address).
Pls guide
Vaishali
04-07-2008 03:22 AM
just follow the instructions on the doc on how to transform the data.
a google on unity epoch time will give you the formula
If u mean that you want the exported data to have it all in a readable format thats not possible, that is the way the CDR dump is and there is no way to change that thru any parameters, thats why we provide the doc
HTH
javalenc
if this helps, please rate
04-07-2008 05:04 AM
hi
Thanks
We are able to convert date and time.
Is there any formula that can convert ip address as well ?
04-07-2008 05:15 AM
from the same doc i already sent:
IP Addresses
The system stores IP addresses as unsigned integers. The database displays them as signed integers. To convert the signed decimal value to an IP address, first convert the value to a hex number, taking into consideration that it is really an unsigned number. The 32-bit hex value represents four bytes in reverse order (Intel standard). To determine the IP address, reverse the order of the bytes and convert each byte to a decimal number. The resulting four bytes represent the four-byte fields of the IP address in dotted notation.
Note The database displays a negative number when the low byte of the IP address has the most significant bit set.
For example, the IP address 192.168.18.188 displays as -1139627840. To convert this IP address, perform the following procedure:
Procedure
Step 1 Convert the database display (-1139627840) to a hex value.
The hex value equals 0xBC12A8C0.
Step 2 Reverse the hex bytes, as shown below:
CO A8 12 BC
Step 3 Convert the bytes from hex to decimal, as shown below:
192 168 18 188
Step 4 The IP address displays in the following format:
192.168.18.188
HTH
javalenc
if this helps, please rate
03-03-2009 06:25 AM
I know this is old, but I spent forever looking for the function that converted the decimal IP address (how it appears right out of a CDR dump) to a dotted ip address. here's the Excel function:
=CONCATENATE(HEX2DEC(MID((DEC2HEX(ABS(I2),8)),7,2)),".",HEX2DEC(MID((DEC2HEX(ABS(I2),8)),5,2)),".",HEX2DEC(MID((DEC2HEX(ABS(I2),8)),3,2)),".",HEX2DEC(MID((DEC2HEX(ABS(I2),8)),1,2)))
Just replace the four "I2" with the cell you want to convert.
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide