Long Distance Codes and Billing

This one is one of those Lync can do it, it’s pretty hack-ish but it works and the customer is happy so we just go with it type of articles.

So I again ran into a customer who had billing codes for long distant calls.  Typically when we break down the use of these codes we discover that either most people don’t use them or they don’t actually get billed back to the client.  However, this was one of those unique ones where it was a small law firm and they used them all the time.

Now there are some third party solutions out there that allow you to do automatic billing or it’s done at the carrier level but I wanted to see if I could do it in-the-box.  And like so many things in the world of Lync and Enterprise Voice there isn’t a “report” for this information.  So I decided it was time to play around with voice routing and see if I could create something “similar” to what they had in the past.

So the requirements are pretty simple:

1. Users are required to dial some sort of “code + billing number” before the call goes out.
2. Calls to the PSTN obviously can’t have that billing code.
3. CDR information must contain the billing code information.
4. There has to be no cost to this.

So with those out of the way, let’s play around with Voice Routing rules and SQL and accomplish the task.

Billing Codes

So the first thing we need to do is account for billing codes.  I’m doing this in my lab so in my case I have a very small business and will never have more than 500 clients we need to bill.  So our codes will be as simple as:

xxx

But I want to have some sort of easy to recognize escape code that I might use later on for by SQL query so I’m going to tell all of my users they must dial a 999 as the billing code requirement.  So my user would then dial:

999xxx17635551234

I picked 999 as there is no country code that starts with those three digits at this time.  Of course, if a new country comes along that fits this, I might need to change some things.  The first thing we do is create a normalization rule for that.

Rule is straight forward.  We simply add a + to the front of the string I’ve created before.  You will notice I’m a nice guy and typically allows my users to dial an option 9 because they are used to it.

Routing

Now that we have secured our normalization rule, we need to route it out of the system.  So I’ll take my existing route and modify it for this new normalization pattern.

This rule is again pretty straight forward.  We are looking for an optional 999xxx followed by the traditional North America dialing.  If we were going to support international dialing I would update my international dialing route as well.

Trunk Configuration

I don’t want to have to do any work on my gateway (because they should be set them and forget them devices) so I’m going to use trunk configuration to strip out my 999xxx (and my +) before I send it to the gateway.  So again, nothing amazing in terms of rules:

This rule simply finds any number that starts with +999xxx and removes all of that and sends out just the remaining digits.

Call Someone

In my Lync client, I can simply dial 99912317635551234 and I find it will normalize to this:

And the call is successful to my cell phone.  So I have accomplished what my goal is to get outbound dialing.  However, now I need to actually get this data to the billing person.  As you can tell, this was the easy part of my task, but what does this information look like in the reporting server?

So we can clearly see we have the data we need but make this easier for our billing person.  I mean, we could give her the SRS reporting page and export this to CSV from it but if you have ever done that you know it’s a mess.  So instead, let’s go to SQL and see if I can put together a better query.

Spending a little time in SQL you can find this query will most likely get you everything you want:

SELECT SessionDetailsView.SessionIdTime, SessionDetailsView.SessionIdSeq, SessionDetailsView.FromUri, SessionDetailsView.ToUri, 
SessionDetailsView.TargetUri, SessionDetailsView.OnBehalfOfUri, SessionDetailsView.InviteTime, SessionDetailsView.ResponseTime, SessionDetailsView.EndTime, DATEDIFF(MINUTE, SessionDetailsView.InviteTime, SessionDetailsView.EndTime) AS TimeToBillMin, SUBSTRING(TargetUri, 5, 3) AS CustNumber ,MediaList.Media
FROM  SessionDetailsView INNER JOIN Media ON SessionDetailsView.SessionIdTime = Media.SessionIdTime INNER JOIN MediaList ON Media.MediaId = MediaList.MediaId
WHERE (SessionDetailsView.SessionIdTime >= '2013-07-01') AND (SessionDetailsView.SessionIdTime <= '2013-07-31') AND (MediaList.Media = 'Audio') AND (TargetUri LIKE '+999%')

In my SQL query there are a few things you will need to modify depending on your environment.

First, if your “client code” is different than three characters then you will need to modify the SUBSTRING(TargetUri, 5, 3) to the correct length.

Second, if you decide to use something different than 999 as your code, you need to update that in two different places in the query.

Third, obviously, you would modify the SessionIdTime based on when your calls actually happened.

And what this will return in SQL is this:

And there you go.  I’ll play around with Excel later this weekend and maybe create an excel spreadsheet that has the query defined in it and all you need to do is enter the needed information – but I’m not an Excel wiz so it might be a bit of work.

Lastly, there are a few items of note:

First, I have not done extensive testing on this process yet.  I will continue to refine it based on feedback I get the client and if anyone else happens to note anything.

Second, this has only been tested against Lync Server 2013 CU1 at this time.  I’ll do more testing and validate no views have changed from RTC to CU1 to CU2.

Third, I did some hunting of the inner-webs before typing this up and didn’t find any articles that were similar.  However, if someone else has a better way to accomplish this by all means let me know.  As I said, this was a little hack-ish in nature.