CDR and rash of 911 calls / ran report and found them but....

[rossporubski]

Hi all,

I ran the report on all subscribers bills. Everything looks clean but no specific "911" notations. Instead I'm finding what I believe to be the 911. In Excel it is formated as "Scientific formula". [1.14999E+12]and says it's an international call. It appears as if CDR reads the raw data and doesn't know how to convert it. Is there any way to convert this to a ledgible number? For instance this is what comes up for the number 1900 as scientific 1.90E+03. Any held is greatly appreciated.

[rossporubski]

Well, it appears the number 1.14999E+12 translates to a phone number in Mexico. I re ran the report as .pdf and it recognizes the number properly. However, we still continue to have a rash of 911's and it doesn't look like they collect. Does 911 translate to something different in CAR?

[Matthew Bryant · Accepted solution]

I will usually run a DeviceReports>Gateway>Detail report and export it as a csv and search in excel. I usually find them in there no problem, they present as 911 so you should be able to see them.

You can also go directly to the SQL database as well.

start > programs > Microsoft SQL server > Query Analyzer > if you are using callmanager version 4.x, select "Windows authentication" and click on OK; if you are using callmanager version 3.x, select "SQL server authentication" and enter the SQL Administrator (SA) password > on the new displayed window, at the top of the page, select the CDR database > then paste the following query:

select callingPartyNumber,dateTimeOrigination from CallDetailRecord where finalcalledpartynumber='911'

The "callingPartyNumber" column will provide the dialing extension.

The "dateTimeOrigination" column will provide the time of call.

To find out the time of the call, obtain the information from the "dateTimeOrigination" column. The value is a coordinated universal time (UTC) value, and represents the number of seconds since Midnight (000000) January 1, 1970.

To decipher the time stamp, you can use the "CiscoCDRTimeConverter" tool. Just copy the integer from the "dateTimeOrigination" column displayed in the query result and paste it on the "Integer From CDR Record" field in the Cisco tool.

[rossporubski]

I'll try that. I appreciate your help. In fact after running and re-running with the query I decided to contact the 911 call center in this particular area in Texas. It appears this may be an issue with a neighbor of ours. 911 has their address associated with our masked outward dial number. Here is a link to a page which converts date and time to several different useful types.

http://www.minelinks.com/calendar_converter.html