[cisco-voip] Most Dialed Outgoing Calls

[Mike Armstrong]

Steve --

Once you're in the Query Analyzer, first make sure the CDR database is 
selected in the dropdown in the menu bar.  Then I usually select 
Window->Show Results Pane, so I can enter the query in the upper pane, and 
see the results in the lower.  Now you're ready to go -- start simple, use a 
simple SQL reference of some kind, and build on your knowledge.

The simplest query might be something like "SELECT dateTimeOrigination, 
callingPartyNumber, finalCalledPartyNumber, duration FROM CallDetailRecord". 
This will list all calls in the database.  You can narrow the results by 
adding a WHERE clause, order the results with ORDER BY, group, total, etc. 
A very useful clause is the TOP n clause, which allows you to only see the 
top "n" rows of the result -- i.e., SELECT TOP 20 ... FROM ... etc.

Click on the blue checkmark in the menu bar, and the query will be parsed --  
very elementary checking.  The result of the parse will show up in the 
Results pane.  Now fire for effect -- click on the green triangle to the 
right of the checkmark, and the query will be executed.  BE VERY CAREFUL not 
to run very long or complex queries on the production CallManager.

I fussed with this for a while, and came up with the following, which I 
think will do what you want:

SELECT TOP 10 originalCalledPartyNumber, Count (originalCalledPartyNumber) 
AS 'Count' from CallDetailRecord GROUP BY originalCalledPartyNumber ORDER BY 
Count DESC

Good luck, have fun.

Mike Armstrong
UF/IFAS CREC
Lake Alfred, FL


----- Original Message ----- 
From: "Miller, Steve" <MillerS at DicksteinShapiro.COM>
To: "Mike Armstrong" <mfa at crec.ifas.ufl.edu>
Sent: Tuesday, February 13, 2007 9:43 AM
Subject: RE: [cisco-voip] Most Dialed Outgoing Calls


Thanks.  Do you have an example of a query that could be used and brief
step-by-step instructions on what commands to use once you open the SQL
analyzer?


Steve Miller
Telecom Engineer
Dickstein Shapiro LLP
1825 Eye Street NW | Washington, DC 20006
Tel (202) 420-3370 Fax (202)-330-5607
millers at dicksteinshapiro.com


-----Original Message-----
From: Mike Armstrong [mailto:mfa at crec.ifas.ufl.edu]
Sent: Tuesday, February 13, 2007 9:40 AM
To: cisco-voip at puck.nether.net; Miller, Steve
Subject: Re: [cisco-voip] Most Dialed Outgoing Calls

However you define "most popular", the ways that come to mind are:

1) Capture the data from the CDR database into either an Access database
or Excel and have at it
2) Be or become sufficiently skilled in SQL query writing to do it in
the original

For complex or lengthy analyses, I use (1) for the most part, to avoid
tying up, and possibly destroying, the CDR database.  I maintain the
historic CDR data in monthly Access databases for those purposes, but
for simply queries of current data I use the Query Manager directly.

Mike Armstrong
UF/IFAS CREC
Lake Alfred, FL

> Date: Tue, 13 Feb 2007 07:23:55 -0500
> From: "Miller, Steve" <MillerS at DicksteinShapiro.COM>
> Subject: [cisco-voip] Most Dialed Outgoing Calls???
> To: cisco-voip at puck.nether.net
> Message-ID: <B9B5B75DF9145F44A21111DD92CC490004FFAB76 at DCEX2.DSMO.COM>
> Content-Type: text/plain; charset=us-ascii
>
> There must be a way to see the most popular OUTGOING calls on my
> system, but I can't find it in the CDR.  Please let me know what I am
missing.
> Thank you!
>
>
> Steve Miller
> Telecom Engineer
> Dickstein Shapiro LLP
> 1825 Eye Street NW | Washington, DC 20006 Tel (202) 420-3370 Fax
> (202)-330-5607 millers at dicksteinshapiro.com



--------------------------------------------------------
This e-mail message and any attached files are confidential and are intended 
solely for the use of the addressee(s) named above. This communication may 
contain material protected by attorney-client, work product, or other 
privileges. If you are not the intended recipient or person responsible for 
delivering this confidential communication to the intended recipient, you 
have received this communication in error, and any review, use, 
dissemination, forwarding, printing, copying, or other distribution of this 
e-mail message and any attached files is strictly prohibited. Dickstein 
Shapiro reserves the right to monitor any communication that is created, 
received, or sent on its network.  If you have received this confidential 
communication in error, please notify the sender immediately by reply e-mail 
message and permanently delete the original message.

To reply to our email administrator directly, send an email to 
postmaster at dicksteinshapiro.com

Dickstein Shapiro LLP
http://www.DicksteinShapiro.com
==============================================================================