Solved: CDR Report with MCR Key

MikeTomasko · ‎03-28-2007

Is there a way to tell which phone is picking up the calls on an MCR key? We have 4 phones with the same line appearance on them and are looking for a way to run a report to see which phones are taking the most calls.

adhernan · ‎03-28-2007

I just did the same for our desk the other day. With the following SQL query you can export the results to a spreadsheet. All CDR times are in GMT and you need to convert the timeframe you want into epoch time before running query against CDR database. In this query you would adjust the dateTimeConnect ranges and finalcalledpartynumber at end of string to your timeframe and share DN (in my example begin jan01.2007- march 19 and DN 6700). Hope this helps! -Adam

select callingpartynumber,finalcalledpartynumber,destDeviceName,finalCalledPartyLoginUserID,(select dateadd(second,datetimeconnect,'19700101') as datetimedisconnect),(select dateadd(second,datetimedisconnect,'19700101') as datetimedisconnect) from CallDetailRecord where dateTimeConnect > '1167631200' and datetimeconnect < '1174366800' and finalcalledpartynumber= '6700'

View solution in original post

adhernan · ‎03-28-2007

I just did the same for our desk the other day. With the following SQL query you can export the results to a spreadsheet. All CDR times are in GMT and you need to convert the timeframe you want into epoch time before running query against CDR database. In this query you would adjust the dateTimeConnect ranges and finalcalledpartynumber at end of string to your timeframe and share DN (in my example begin jan01.2007- march 19 and DN 6700). Hope this helps! -Adam

select callingpartynumber,finalcalledpartynumber,destDeviceName,finalCalledPartyLoginUserID,(select dateadd(second,datetimeconnect,'19700101') as datetimedisconnect),(select dateadd(second,datetimedisconnect,'19700101') as datetimedisconnect) from CallDetailRecord where dateTimeConnect > '1167631200' and datetimeconnect < '1174366800' and finalcalledpartynumber= '6700'

MikeTomasko · ‎03-28-2007

Thanks for the quick reply! It looks like you're getting me on the right track, but could you explain the format of the date and times? What would I use for the date and time if I just wanted to see the traffic on that DN for all of yesterday? Thanks!

adhernan · ‎03-28-2007

Here is a URL with a converter. Remeber that time is UTC so you have to offset for localtime. Central timezone 03/27 midnight would be 1980784800

http://www.onlineconversion.com/unix_time.htm

adhernan · ‎03-28-2007

If the users had extension mobility you would get that otherwise I got around by using GREP to associate the phone NAME (MAC) with the user assigned to phone. -basically replace device name with user name .. crude but works...

MikeTomasko · ‎03-28-2007

I think I'm catching on! GREAT info! Now for what's probably an easy question...how do I get the data into Excel?

Steffen.Baier · ‎03-28-2007

just copy and paste into a excel sheet via right click