[cisco-voip] Tracking Down 911 Calls

[Matthew Saskin]

Slight modification to Ryan's query to have SQL server do the datetime 
conversion for you.  Yes, I realize that this puts a higher load on the 
server.  Remember that times are in GMT.


select
dateadd(s, convert(int, dateTimeOrigination), '01-01-1970') as 
dateTimeOriginationHumanReadable,
datetimeorigination,origDeviceName,callingPartyNumber,originalCalledPartyNumber,finalCalledPartyNumber 
from CallDetailRecord
where finalcalledpartynumber like '%911'

-matt

Ryan Ratliff wrote:
> Assuming you have CDRs enabled (on all your servers)...
> 
> select  
> datetimeorigination,origDeviceName,callingPartyNumber,originalCalledPart 
> yNumber,finalCalledPartyNumber from CallDetailRecord where  
> finalcalledpartynumber like '%911'
> 
> This SQL query (run against the CDR db) will get you all calls made  
> that ended with 911.  You can manipulate the search terms as  
> necessary to fit your dialplan.  You can use http:// 
> www.onlineconversion.com/unix_time.htm to convert the  
> dateTimeOrigination from unix time to a normal date/time.
> 
> -Ryan
> 
> On Nov 6, 2007, at 11:15 AM, Jason Aarons ((US)) wrote:
> 
> In CallManager 4.1, I had a SQL/ASP guru write a web page that showed  
> 911 calls, also you could have a stored procedure send you  
> emails ,etc.  None of this is Cisco supported, but solved our problems.
> 
> 
> 
> Billing products like InforTel can also alert on 911 calls based on  
> CDR logs file, and IPSession has a solution for alerting on 911  
> calls, as well as CER.
> 
> 
> 
> From: cisco-voip-bounces at puck.nether.net [mailto:cisco-voip- 
> bounces at puck.nether.net] On Behalf Of Terry McGhee
> Sent: Monday, November 05, 2007 10:16 PM
> To: cisco-voip at puck.nether.net
> Subject: [cisco-voip] Tracking Down 911 Calls
> 
> 
> 
> Over the past 90 days one of our CCM installation sites has generated  
> more than 15 unintended 911 calls.  (TAC call initiated and their  
> studying it)
> 
> 
> 
> In discussing this situation with the 911 call center their previous  
> historical experience in similar situations indicates that our cause  
> is probably a procedural/configuration problem rather than prank  
> calls. For example, we dial “9” for outside calls, and our geographic  
> location is adjacent to an area code that begins with 91X.
> 
> However, the installation is in a middle school and the principal  
> believes these are prank calls.
> 
> 
> 
> My question is, what tools are available to help us determine exactly  
> what’s going on here.  The number the 911 call center gets is always  
> the same main pilot number at the front switchboard.  We have  
> confirmed that on one occasion, this did happen at the front  
> switchboard but, there are at least 14 more instances when we know  
> that particular phone did NOT make a 911 call.
> 
> 
> 
> 
> 
> 
> 
> Terry
> R. Terry McGhee
> Director of Technology, Halifax County Public Schools
> Halifax VA 24558
> tmcghee at halifax.k12.va.us
> 434-572-5119 Office
> 434-222-8636 Mobile
> 
> "It is no use saying, 'We are doing our best.' You have got to  
> succeed in doing what is necessary." -- Winston Churchill
> 
> 
> 
> 
> 
> Disclaimer: This e-mail communication and any attachments may contain  
> confidential and privileged information and is for use by the  
> designated addressee(s) named above only. If you are not the intended  
> addressee, you are hereby notified that you have received this  
> communication in error and that any use or reproduction of this email  
> or its contents is strictly prohibited and may be unlawful. If you  
> have received this communication in error, please notify us  
> immediately by replying to this message and deleting it from your  
> computer. Thank you.
> 
> _______________________________________________
> cisco-voip mailing list
> cisco-voip at puck.nether.net
> https://puck.nether.net/mailman/listinfo/cisco-voip
> _______________________________________________
> cisco-voip mailing list
> cisco-voip at puck.nether.net
> https://puck.nether.net/mailman/listinfo/cisco-voip