show pri average number of concurrent channels used

[jasonrpell20]

I'm still new to managing of the VOIP network, but am able to do what's needed to get around Call Manager to find reports and do simple configuration tasks.  What I'm looking for is a report that will show me the average number of channels being used concurrently on a PRI.  We have 2 PRIs.  We need to provide some statistics that shows how many of the 23 channels we're using up at any given time during a typical working day.  Is there any way to do this through CDR Analysis and Reporting or another part of Call Manager?  Past that I would want an average of each day.  We're running version 8.6.1.20000-1

I do realize that this changes as much as every second where one call will drop and another one comes in, but is there anything that will come close to getting me this information?  I know I can show average number of calls for a day but that may not be specific enough.  Thanks to anyone who can help.

Jason

[Andrew Skelly]

First, you can monitor real time each PRI by using the Real Time Monitor Tool.  This will show you real time what is going on on each PRI.  Just go to the Performance Monitoring, select Cisco MGCP Pri Device - CallsActive, then open up a window for each PRI.  It only shows you the past 3 minutes, but gives you a good idea in the middle of the day what you are up to volume wise.

You can then also pull a CDR report and filter that out for previous day(s).  You would need to filer/sort based upon the origDeviceName (this will be the name of the PRI device), followed by sorting on time of day.  It's not 100% clean, but it will get you what you are looking for

[jasonrpell20]

Thanks for the reply.  I did find the graph in the RTMT, but of course that's just real time and not an average over the course of a day, week, or month.  Can a CDR report actually report this same information?  If so, which specific report, parameters need chosen.  I haven't found what would give this information yet.  Thanks.

[Andrew Skelly · Accepted solution]

Yes, a regular CDR report from Call Manager will show this information.  You don't need to choose any specific parameters, other than the date.

For instance, just pull a CDR report for yesterday.  Save the report to your desktop and then open it in Excel (you'll probably have to open in Coma delimited).  When the report comes up, look for the column heading "origdevicename" (I think it's around column BE).  That will show you what device a call came in on.  You'll want to filter by the names of your PRI devices.

One you have only those devices, then you can sort by date/time.

[Andrew Skelly]

Here is a screen shot of what the field looks like.  It is column BE (at least in version 7.0 it is).  We have 12 PRI spread across 2 gateways, and each PRI device begins with name S1/DS1.  I would filter for those names, then sort by date/time.

[jasonrpell20]

I definitely see this.  I did the export and found the gateway names and sorted.  Past this, which column(s) should I be looking at to show me the number of PRI channels being used?  Column names aren't the most clear!  Thanks!

[Andrew Skelly · Accepted solution]

This is where the work comes in, as there is no easy way to get the info you are looking for, as you are trying to figure out how many calls were up and active at the same time.  You are basically looking for calls that match within a specific start time and end time.  You can try using the formula in this forum below, as they were trying to find the same thing you are.  However I was unable to get it to work properly.

https://supportforums.cisco.com/message/3262532#3262532

If you cannot get the formula to work (it worked for me, just the values didn't seem to fit logically), you'll have to sort by start time, then sort by end time.  Those are columns "dateTimeConnect" and "dateTimeDisconnect" (should be columns AV and AW).  You'll then need to correlate how many calls were up during any given time period.

It's a pain, I know.  But I have yet to find any clean way of getting this info from the Cisco.

[Andrew Skelly]

I played around a bit with the CDR report and was able to trim it down a bit, but still requires some work.

After you filtered out to just show the PRI calls, add a column after the "dateTimeDisconnect" column.  Call it something like "matches".

In that new column, starting at the 3rd cell (i.e. AV3), put in this formula:

=(AX3>AX2) & (AX3

This formula basically says "If my start time is greater than the start time above me, then true" & "if my stop time is less than the stop time above me, then true".  You will then want to filter to find only those that return a value of "truetrue" as these will be all the calls that took place during the same time as the previous call.  You'll then have to sort by the start times to find what you are looking for.

HTH.

[jasonrpell20]

All this is really good.  I don't mind playing around with Excel stuff.  In your last example, are you saying you sorted it by the Start time?  That's what makes sense.  Thanks!

[Andrew Skelly]

Yes.  I sorted by the start time first.  Then I added in the formula and took only those results that came back "truetrue".

[jasonrpell20]

I'm so close to getting this but my one problem is the  dateTimeConnect and dateTimeDisconnect column.  When it exports, it  doesn't have the actual date and time in the column, but just a huge  number like 1329985213 and 1330001196 and so on.  I've seen this before and changed formatting and everything,  also dropping off several digits, but nothing puts in the correct date  and time.  Did you have this issue?  Thanks.

[Andrew Skelly · Accepted solution]

Cisco exports the date and time in Unix format.  You'll need to convert it.  It's very simple to do, just takes a formula and then a format change.

Let's say the column you want to format is colum A (just for example).  Insert a new column next to it (in column B).  You would then enter in the following formula in cell B2 (assuming you have a header in row 1).

=A2 / 86400 + 25569 - 5 / 24

The "- 5" is timezone related to GMT.  I'm on the East Coast, so we are GMT - 5.

Once you put the formula in, you will get a new, smaller number.  Format the cell.  Right click the cell, choose Format Cells.  In the pop up box, under the Number tab, go to the Time option on the left.  Choose the next to last option on the right (should look like "3/14/01 1:30PM").  Once you have the cell formatted, autocopy all the way down.

http://excel.tips.net/T002051_Converting_UNIX_Date_Time_Stamps.html

[jasonrpell20]

Thanks for all the answers.  This ended up working great!  Was able to get both formulas to work and the average matched so it gives me a good idea of our usaged.  Cisco TAC didn't begin to try to do this, but of course they're only going to suggest ways to do things with the options that are already available.  Appreciate the help!

[Andrew Skelly]

No problem, glad to help.