Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

New Member

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
New Member

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

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.

3 REPLIES
New Member

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

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?

New Member

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

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.

New Member

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

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

210
Views
0
Helpful
3
Replies