In the CDR Excel report. I am trying to do format in the data and time. But after selecting the correct format in excel also its not giving the data and time format.
Do i need to change anything setting?
Message was edited by: selva
The time format in the UCM CDRs is in the universal Unix format which expresses time in "seconds since jan 1st 1970 UTC" Excel has for my knowledge not a default tool to calculate this as a cell format. For debugging purposes I wrote me a litlle tool to read CDRs quick and dirty. It is compatible with v7+ of UCM. Here is a link to download it:
use the CDR exported file and read it from the program. The rest should be prety much self explaining. For me it was a troubleshooting tool to find records based on calling and called party (filter by high order digits) and to see which device was involved in the call (phone/gateway) You need to have the dotnet libraries installed to make it work.
Thanks for your respons Patrick.
UCM CDR tool is only giving only the below details.
When i open the CDR Dumb file in excell i am able see the dateTimeConnect & dateTimeOrigination in geneal format. when i try to change into data and time format i am getting only ###########. i am not getting the proper date and time format. how do get these details.
Do i need to change anything in CUCM.
i have updated the sample text file of CDR dump, i am not able to chane the date and time.
Thanks in advanse.
I have expanded the column width, but still i am not getting.
I took your CDR file and imported it into Excel. If you format the column as "number" you will see the seconds since 1-1-1970. If you format it as "time" or "date" then you will get the "#####" which indicates that excel cannot deal with such a number as a time representation. To see how this number needs to be converted look for the "unix epoch time format" like here: http://www.epochconverter.com/ or for more general information here: http://en.wikipedia.org/wiki/Unix_time
to solve this with excel have a look here: http://www.mrexcel.com/forum/excel-questions/49217-converting-normal-time-epoch-time-format-using-excel-2.html but don't fall for one of the entries which base the number on milliseconds since 1/1/1970. UCM uses seconds since 1/1/1970.
if iwant to do it for 1000 number;s. manually it's difficult.
I was able to convert tate and time format in excell. and the same able to do tht in other cluster CUCM. only this CUCM i am not able to do.
Is there any other way is there do in excell?
That is odd, I don't see a difference in the CSV file you attached to any other time/date in Unix epoch format. Can you specify what the differences are between the UCM versions you have? Which ones work, which don't? I was able to use your attached CSV file and read it in excel and my little tool as well. I attached a screenshot showing the incoming and outgoing time, duration, and participating devices. Nothing odd visible here.
Thanks for the usefull information Patrick.
Dude, this is absolutey a great timesaver, I takes 5 minutes per to go through the records