OCS 2007 R2 Monitoring Server Custom Query

A common question I always get is around custom queries for the Monitoring Server. The most requested is for used voice minutes by users. I thought I would post the query below in hope of saving it some where handy and giving others the chance to try it out. The query should produce a report that looks like this: Callee,Caller,StartTime,EndTime,Length

select

    convert (varchar(10), a.SessionIdTime - 0.125,101)  as 'Start Date',

    convert (varchar(10), a.SessionIdTime - 0.125,108)  as 'Start Time',

    convert (varchar(10), d.SessionEndTime - 0.125,101)  as 'End Date',

    convert (varchar(10), d.SessionEndTime - 0.125,108)  as 'End Time',

   

    convert(varchar(10),d.SessionEndTime -      a.SessionIdTime,108) as 'Time',

       b.PhoneUri as 'From N.',

      f.UserUri  as 'From Uri',

       c.PhoneUri as 'To N.',

      g.UserUri  as 'To Uri'

from

   VoipDetails a inner join

      SessionDetails d on d.SessionIdTime = a.SessionIdTime inner join

      Phones b on a.FromNumberId = b.PhoneId inner join

      Phones c on a.ConnectedNumberId = c.PhoneId left join

    Users f on d.User1Id = f.UserId left join

     Users g on d.User2Id = g.UserId

                                  

WHERE

   d.SessionEndTime - a.SessionIdTime is not null

      ORDER BY

      a.SessionIdTime DESC

 

Let me know if you have any other custom queries you would like to share. I am only to happy to post or talk about them. Thanks to Mike D for the info above.

VoIPNorm

Extending Communicator 14 with Bing Translator

When I first heard about this I was surprised at how easy it was for the developers at Microsoft to throw this together. This is a great way to show how an on-premise deployment can benefit from using web services as well as showing how easy and effective it is to extend the functionality of Communicator in wave 14.

Let’s do a screenshot demo of what this is and then we will look at how it works.

The screen shot below shows me in a conversation with myself from Communicator 14 to my Live account. As you can see I can access the conversation translator through the menu option.

clip_image002

The next shot shows me replying to my original “hi” in French (with the help of Bing) from my Live account. AS you can see the inbound text is translated for me back to English. This application currently supports 32 languages.

clip_image004

Next is my reply which I typed in English then hit the translation button.

clip_image006

clip_image007

Finally, I then send the translated text to myself on Live.

clip_image009

How does it work?

It works via a combination of three things:

• The Conversation Window Extension – an area to the right of the conversation window where developers can host and run their own Silverlight or browser applications.

• Silverlight applications in the CWE have access to the conversation that is ongoing between the two parties via the new managed Communicator “14” API. This is how the Translator gets access to the IM text to translate.

• Bing translation via the public APIs.

http://sdk.microsofttranslator.com/SOAP/SOAPDemo2.aspx

http://sdk.microsofttranslator.com/SOAP/GettingStarted.aspx

The screen shot below shows the SOAP request and replies to and from the Bing translation service from Communicator.

clip_image011

A SDK will be available at time of CS 14 release so everyone can deploy this cool application. This is a great example of cloud services being used in conjunction with on-premise and I am sure this is just the start of these types of applications.

VoIPNorm

VoIPNorm Email Subscription and Twitter Changes

If you haven’t visited VoIPNorm recently I have added some new features to make it easier to share content. First off is the email subscription. It’s a pretty painless method to get updates from VoIPNorm in your email via Feedburner. Secondly is the Retweet button on each post which you will notice in the right hand corner. Lastly I have also made some changes to get all posts automatically added to Twitter which is available at http://twitter.com/voipnorm if you’re interested in following and getting post updates via Twitter.

VoIPNorm

Communications Server 14 and Live Messenger Beta

Some of you may have heard by now that CS14 will have the capability to do video and audio with Windows Live Federated contacts. Well the proof is in the pudding so they always say. Here is a screen shot of a HD video session between a fellow MS employee and myself.

Before I took the screen shot, I had Ben on full screen on Messenger. The quality was amazingly clear in full screen. The Live team has done a great job of enabling this interoperability.

image

You might think so what, he was in the room next to you. Well, not quite. Ben was working at home in New Mexico on his Comcast connection and I was working at home in Seattle. This was using about 1.5mb of bandwidth so a pretty intensive workout for our Comcast connections but other than Ben moving to Miami to increase the distance it’s a pretty good test of the quality on an uncontrolled network. This of course was possible because of the use of RT Video codec. When you think about how many consumers have Live accounts and access to Messenger this becomes a very attractive feature to have in consumer facing organizations that upgrade to CS 14 in the near future.

One thing to keep in mind is that your media session will not be encrypted. It is an RTP session and not SRTP. Just something to be aware of. I think this won’t bother most organizations as having the capability will be a big advantage over not having it regardless of encryption.

Even without this feature the new Windows Messenger experience is a major change from the previous version. With social networking features it has the ability to aggregate a large percentage of online activity. It is an awesome free client far exceeding my expectations.

Get the Windows Live Essentials Beta here http://explore.live.com/windows-live-essentials-beta.

Comments welcomed.

VoIPNorm

1800flowers

This seems like a crazy simple tip but people just don’t realize you can do it. Alphanumeric numbers get automatically converted in Communicator to numerical numbers. No need to remember which digit is what; just type it in and you are done.



With CS 14 it is also available via the dial pad but still the easiest method is to type the vanity phone number into the address bar as is.



See, crazy simple.

Comments welcomed.

VoIPNorm

Cisco and Microsoft Joint Interoperability Statements

In case you have ever wondered what is supported for interoperability between these two companies here are the links to both companies’ websites on their joint interoperability statements.

Cisco
http://www.cisco.com/web/partners/pr67/pr41/solutions/uci_microsoft_cisco_jointstatement.html

Microsoft

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=78814f28-2df5-4cff-a166-73622c7830bb

Both statements are very similar although you will notice a slight difference with Cisco statement around RCC.

From the Cisco document (updated May 26th):

“Remote Call Control is supported by Microsoft in Office Communications Server 2007 and Office Communications Server 2007 R2 and will continue to be supported for customers upgrading their Remote Call Control deployments to the next release of Office Communications Server.”


They forgot to mention new customers will be supported for RCC.

From the Microsoft document (released May 22nd ):

“Remote Call Control is supported by Microsoft in Office Communications Server 2007 and Office Communications Server 2007 R2 and will continue to be supported for both new customers and customers upgrading their Remote Call Control deployments to the next release of Office Communications Server. “


Even though there is an update date of May 26 after the release of the Microsoft document it seems as though that point slipped their minds.......hmmm.

Comments welcomed.

VoIPNorm

Microsoft Product Support Dates

Ever wondered what the life support dates of a Microsoft product are? There is an easy way to find out. The link below allows you to search across all of Microsoft products to see the support and release dates.

http://support.microsoft.com/lifecycle/search/

Moving to E.164 in CS 14

This is a tricky subject and I certainly want to treat this with the right kind of subjective thought that it deserves. At my previous employer I made the decision early on to strictly adhere to E.164 or RFC3699 and make changes on an IP-IP GW before routing to CUCM 4.2.3 for our OCS deployment. It made sense and clearly enabled the deployment to move away from a complex legacy dial plan that supported multiple dialing options, multiple access codes and abbreviated dialing. Some of this behavior I was able to overcome with normalizations rules and others through translation rules on the IP-IP GW. Even though this would be considered painful by some I looked at it as the right move forward.

Early on in testing OCS R1 I did discover that I could route numbers without the + but with R2 this was problematic at times and the best way forward was to strictly follow RFC3699/E.164. I worked for a global company and even though our pilot was only going to be in the US I had to think bigger as this was going to be deployed globally. Lucky there were some common issues people run into that I didn’t have to deal with. Just to name a couple:

-The company was already receiving all DID’s in ten digit format from the service provider in the US.

-The service provider would accept 11 digits in the US and 10 digit routing was in place.

- I was able to simplify call routing in OCS since I was interfacing into CUCM which already had a complex routing plan (no need to recreate the wheel). Routing though CUCM was not my optimal choice but it was my only choice.

-The company used the same service provider for long distance and local calls so there was no need for steering digits.

-Ten digit dial plan was the direction the company was headed anyway so there was no need to do abbreviated dialing or support the abbreviated dial plans of other PBX’s (actually that was more me saying I wasn’t going to do it rather than a team consensus).


So as you can see I was pretty fortunate to not have these obstacles in my way to E.164 heaven. Probably the biggest hurdle in all this was abbreviated dialing. I was trying to keep the dail plan dead simple and keep voice dial plan management at basically zero. Funny thing is not once did I ever hear a complaint of not being able to abbreviate dial another PBX user. I think Telecom can be paralyzed by the thought of people not being able to do abbreviated dialing. But with all the complexities of overlapping dial plans, site codes and everything else, when it is replaced with a better end user experience in my opinion it’s soon forgotten by users.

This was my journey but what if you run into everything I am describing here that I got to avoid. Doug Lawty talks about common dial plan issues here in his blog post.

Although I agree with Doug’s opinion of always sticking to E.164 OCS 2007 R2 doesn’t always make it so easy when you are trying to do direct SIP with non-RFC3699 compliant PBX dial plans. Stripping the + is only part of the puzzle. I avoided just saying PBX’s there because more and more PBX’s are supporting E.164. It just never gets implemented into the dial plan. I am not about to cry foul on you because you have allowed a 9 steering digit into your OCS dial plan because the thought of building 100’s of translation patterns into CUCM fills you with uncertainly. If you’re faced with all these issues sticking with E.164 is challenging and could possibly mean significant changes to otherwise stable enterprise dial plan.

If you were fortunate you may have been able to circumvent the E.164 headache by having a gateway between your OCS and legacy environments but of course if you were trying to do direct SIP this means programing changes into your PBX. The point here is, it isn’t happening in OCS. Well with CS 14 this has changed.

CS 14 Translation Rules

Translation patterns on outbound SIP trunks means that any enterprise should be able to implement E.164 with a lot less pain and all dial plan configuration will be centralized in CS. You also no longer need to add additional routing logic into gatewaysfor E.164.



So now with CS 14 you will be able to deliver a consistent end user experience across your entire enterprise with E.164. Direct SIP trunking to a non-compliant RFC3966 PBX will be easier and will require a lot less changes on the PBX. This means a central place to enable:

-Stripping the +

-Adding 011 to international calls or any other international access code

-Adding steering digits to outbound Long Distance calls to reach legacy trunking

-Adding External access codes


Its not always a internal dial plan that needs these types of requirements. Some service providers may also require removal of the 1 for local dialing in the US.



Tools available for OCS 2007 R2

If you are currently working at implementing OCS 2007 R2 there is a great free online tool available to help with creating the rules for implementation with a gateway for US dial plans. OCS Optimizer is an automated script generator which will generate very compact set of rules you can copy and paste into your OCS Dial Plan in Enterprise Route Helper, and also your PSTN Gateway config file.

This OCS optimizer looks at area code and exchange and generates the appropriate routes using data from http://www.localcallingguide.com/. By adding the external access number it will make building those steering digit legacy rules pretty easy and when there is a change to the free calling area code you entered, it will send you an email to let you know.



Here is an example for the area code 206766 which is here in Seattle I entered a 9 as the external gateway access. For OCS it produced a set of rules for local free calling that looks like this:

^(\+1206(([23456789])))|(\+1425((21[3468])|(22[13678])|(23[3457])|(24[123568])|(25[01456])|(28[12345689])|(29[1568])|(2[07])|(26[049])|(30[1267])|(32[3459])|(35[1248])|(37[2368])|(39[012458])|(3(10|13|18|61|68|69|81|83))|(40[12689])|(41[235679])|(42[0147])|(43[01237])|(44[023459])|(49[125678])|(4[5678])|(58[02467])|(5[2679])|(5(02|03|07|16|18|19|31|33|38|56|57|58))|(60[23568])|(63[5678])|(64[012346789])|(6[5789])|(66[03])|(73[2689])|(74[123456789])|(78[05678])|(79[0359])|(7[027])|(7(53|55|57|61|65|66))|(83[0567])|(88[012359])|(8[012469])|(87[137])|(92[0128])|(94[0134579])|(95[124567])|(97[0347])|(9[1689])|(9(02|06|08|30|36|39))|((336|614|629|712))))|(\+1253((21[4678])|(23[4679])|(24[3569])|(2(61|66|69|70|75|77))|(33[23456])|(39[4578])|(3(50|51|72|73))|(4(78|79|80|86|87))|(56[139])|(65[2367])|(73[3567])|(7[04])|(7(65|66|93|96|97))|(83[3589])|(85[02469])|(8[01])|(8(72|74|76|80|86|87))|(9(31|39|41|45|46))|((205|220|288|293|315|326|347|437|449|458|499|508|518|642|661|670|681|773|785|867|893|929|951|981))|((52|63))))

This in itself is priceless and takes a great deal of work out of doing your dial plan routes for local calls. It also produces a gateway file In this example I selected the generic option.

+1206[2,3,4,5,6,7,8,9]xxxxxx
+142522[0,2,4,5]xxxx
+142523[1,2,8,9]xxxx
+142525[2,7,8,9]xxxx
+142526[1,2,3,5,6,7,8]xxxx
+142529[0,2,3,4,7,9]xxxx
+14252[10,12,44,49]xxxx
+142531[2,4,5,6,7,9]xxxx
+142532[0,1,2,7,8]xxxx
+142533[0,2,3,4,5,7,8,9]xxxx
+142535[0,3,5,6,7,9]xxxx
+14253[03,04,08,74,77,79,85,87,88,96,97,99]xxxx
+14254[05,07,22,23,34,38,41,46]xxxx
+14255[01,08,12,13,14,83,85]xxxx
+14257[10,17,50,54,83,89]xxxx
+14258[70,76,79,86,88]xxxx
+142590[3,5]xxxx
+1425[280,367,418,493,530,551,609,610,622,631,645,669,737,740,760,791,831,923,931,948,953,971]xxxx
…………………..etc.etc.etc

This could also be a AudioCode or Dialogic gateway which produces a easily importable text file in the right format for those gateways. As you can see it does a great job of consolidating the various DID free call ranges.

I had the pleasure of meeting Ken the creator at a recent ignite event in Phoenix. This is a great tool and Ken will be updating this for CS 14 and PowerShell soon so keep an eye out for that. If you have any feedback for their OCS Optimizer tool let Ken know I am sure he would like to hear it.

Preparing for the future


Although this post was mainly based on a new feature in CS 14 I just wanted to talk a little about working with a dial plan to allow a seamless transition form non-compliant to compliant E.164 dial plan in OCS 2007 R2. The main concern with integrating to an existing non-compliant E.164 dial plan is usually outbound dialing. So to prepare for future success building a dial plan that has client side features in E.164 format is probably the most critical.

What does this mean? Putting the Tel URI associated with the user in E.164 format. This will also allow you to place inbound normalization rules on the mediation server that will comply with E.164. The address book on the other hand may be a little different because it will be a lot harder to build rules to suit your desired outbound dialing behavior. The only thing left out is the outbound dialing routes and client normalization rules. I would recommend that you still use the + regardless of what you do outbound just to get users used to it and make dial plan changes easier when you finally jump to E.164. You can strip the + at the Mediation server in OCS R2 if required. If you must pass access codes etc. this is okay just keep in mind of what other areas you are affecting by using these dialing habits and in CS 14 plan to create new translation patterns that will eventaully remove the need to have users use access codes altogether.

I mention the address book because if you have an external access code of 9 as an example now you need to build this logic into your address book as well for phone numbers that aren’t in your organization. Depending on the size of your organization this could be a pretty time consuming task to allow the proper outbound routing to work in conjunction with your address book. There is no real work around for this so be prepared for trial and error.

Last point that is worthy of a mention is getting ready for SIP trunking. Not having a compliant E.164 dial plan could limit your choices or possibly mean a lot of rework so having a plan to progress to E.164 if you think SIP trunking is in your future will be important. Hopefully with CS 14 the decision to do E.164 will now be a no brainer and enterprises will have no issues adopting to a global dial plan.

Comments welcomed.

VoIPNorm

New York UC User Group

New Yorkers beware there is now a Microsoft User Group for you.

http://www.nymucug.com/

The inaugural kickoff is sure to be a big event . Details below:

Thursday, September 30th, 2010
Registration: 8:30-9:00AM
Event Time: 9:00-1:00PM

Location
1290 Avenue of the Americas
Floor 6
New York, New York 10104

Comments welcomed.

VoIPNorm