How to list all PSTN calls in LYNC/ OCS 2007R2

UPDATE 2011-10-20

Revised the queries and confirmed that it also works for Lync.

Problem

I needed a list of all calls leaving and entering the organization via the Mediation servers IP trunk for invoice control. We suddenly detected a large increase in the volume of external calls through our OCS, and wanted to make sure the calls were actual calls and not misconfigured call redirection or something else. We had already deployed the monitoring role, but it was not logging any calls. This was due to an MSMQ error, which meant that the monitoring server could not receive any log messages. I fixed that and waited a couple of days for the data to accumulate. Then I had to find a report to give me the data I wanted. The default OCS reporting services reports allows me to list all the calls for a specific user, but I wanted to list all PSTN calls for all users.

Solution

First I tried to modify the included reports, but they were closely linked to several stored procedures, who again called other stored procedures and gave me far to much data anyhow. So I gave up that approach and started looking at the tables in the database. At http://technet.microsoft.com/en-us/library/dd819961(office.13).aspx I found a nice reference of all the QOE database tables. This enabled me to construct the following query:

USE [QoEMetrics]
GO

select us.URI as CallerURI, us2.URI as CalleeURI, StartTime, EndTime, 
datediff(Second, StartTime, EndTime) as DurSeconds ,
CONVERT(varchar(6), datediff(Second, StartTime, EndTime)/3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (datediff(Second, StartTime, EndTime) % 3600) / 60), 2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), datediff(Second, StartTime, EndTime) % 60), 2) as DurHMS, CallerEndpoint, CalleeEndpoint

from Session se
join [User] us on se.CallerURI = us.UserKey
join [User] us2 on se.CalleeURI = us2.UserKey
where (CalleeEndpoint = 1 or CalleeEndpoint = Null or CallerEndpoint = 1 or CallerEndpoint = Null) AND DialogCategory = 0 AND StartTime Between '2011-03-08' and '2011-03-09'

The where-statement is where the magic happens. You have to tailor this to your needs.

First it checks the endpoints, which identifies the caller and callee type. The endpoint ID’s are found in the Endpoint table. In my case, ID 1 translates to the mediation server. If you have more than one mediation server, you will have to add entries for all of them. I have also found the mediation server to have more than one ID, probably caused by changes made to the topology. A PSTN call will have the mediation server as an endpoint if the number is registered in Active Directory. Otherwise, the endpoint will be null. If the endpoint has a value other than null or the mediation server’s id, it refers to another computer running the OCS client. The endpoint table lists the computer names.

The second part of the where statement defines the timeframe from 00:00 on the first date to 00:00 on the second date. StartTime Between ‘2011-03-08’ and ‘2011-03-09’ will return all calls made between 2011.03.08 00:00:00 and 2011.03.09 00:00:00.

Running this query will list the calls made in the specified timeframe where one of the parties is on the PSTN. If you want to list outbound calls only, use this version:

USE [QoEMetrics]
GO

select us.URI as CallerURI, us2.URI as CalleeURI, StartTime, EndTime, 
datediff(Second, StartTime, EndTime) as DurSeconds ,
CONVERT(varchar(6), datediff(Second, StartTime, EndTime)/3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (datediff(Second, StartTime, EndTime) % 3600) / 60), 2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), datediff(Second, StartTime, EndTime) % 60), 2) as DurHMS, CallerEndpoint, CalleeEndpoint

from Session se
join [User] us on se.CallerURI = us.UserKey
join [User] us2 on se.CalleeURI = us2.UserKey
where (CalleeEndpoint = 1 or CalleeEndpoint = Null) AND DialogCategory = 0  and StartTime Between '2011-03-08' and '2011-03-11'

List users in OU not enabled for Lync

get-csaduser -filter {Enabled -ne $True} -OU "ou=orgunit,dc=domain,dc=topleveldomain"| sort SamAccountName |ft SamAccountName, UserPrincipalName

Lists users contained in the specified organizational unit that are not enabled for Lync in a table ordered by username (ascending).

Useful for finding users not listed in Lync Server Control Panel. The reason for this is most likely that the user has an AD primary email address (UserPrincipalName) in a domain that is not Sip enabled.

SNAGHTML295c7788

This can happen if your AD domain is not the same as your sip/e-mail domain. To correct this, you could either change the primary email address in AD(best option) and try again when AD is synced, or you could manually specify a sip address in the correct domain (Enable-CSUser –SipAddress “sip:x@y.tld” …)

Some clients unable to log on externally

Problem

Some users complain about not being able to log in to Lync externally. The domain logon is looping, even though they have entered the password correctly and a check confirms that their account is not locked out or disabled. Additionally, Event ID 14614 from LS Protocol Stack is logged on the FrontEnd server when the users try to log on:

SNAGHTML198fc660

Solution

The solution is mentioned further down in the event log message: “Ensure that the “Network security: Minimum session security for NTLM SSP based (including secure RPC) clients” policy settings on the computers from which users log on are the same as “Network security: Minimum session security for NTLM SSP based (including secure RPC) servers” policy settings on this server.” This translates to a configuration error in the policy mentioned. On Server 2008 R2 and Win7 the default setting is require 128-bit encryption, on earlier versions of windows both 128 bit encryption and NTLMv2 is off. To solve the problem, either turn it off on the server side (not recommended) or turn it on at the client side. This setting can be pushed as a computer GPO.

In my case the user experiencing the problem was actually running Win7, but said computer had joined a Windows 2003 server domain which had probably pushed the wrong setting. Changing the setting was all it took to fix it, no restart of either Lync nor Windows was required.

image

Listing enterprise voice enabled users and their assigned numbers

I wanted a list containing all OCS users enabled for enterprise voice, and found the answer at UCSpotting. Being difficult, I also wanted to list the pone numbers assigned as Line URIs. I changed the query from UCSpotting and ended up with this:

SELECT r.UserAtHost, p.PhoneNum FROM RTC.dbo.ResourceDirectory c
INNER JOIN RTC.dbo.Resource r ON c.ResourceId = r.ResourceId
INNER JOIN rtc.dbo.ResourcePhone p ON c.ResourceId = p.ResourceId
WHERE (c.OptionFlags & 128) = 128
ORDER BY r.UserAtHost

which returns all enterprise voice enabled users who has an assigned Line URI. But then I thought, what about users with no Line URI? The list containing phone numbers were shorter than the list of voice enabled users, ergo there had to be some users enabled for voice without an assigned Line URI. Experiments showed that such a user could dial out, so listing these users as well seemed pertinent. Changing the last join to a left join instead did the trick:

SELECT r.UserAtHost, p.PhoneNum FROM RTC.dbo.ResourceDirectory c
INNER JOIN RTC.dbo.Resource r ON c.ResourceId = r.ResourceId
LEFT JOIN rtc.dbo.ResourcePhone p ON c.ResourceId = p.ResourceId
WHERE (c.OptionFlags & 128) = 128
ORDER BY p.PhoneNum

How I finally upgraded the firmware on a Polycom CX700 running 1.0.522.73

SNAGHTML244d5f8bA long and sad story about trouble upgrading the firmware in Office Communication Server enabled phones, specifically the Polycom CX700. This particular problem manifests itself as follows: The phone logs on to the server successfully and gets almost to the end of the login process. Then it crashes and automatically reboots. This goes on in an endless loop, and is caused by the phone running software not compatible with OCS 2007 R2. I post this hoping that it will be of help to others on a similar quest.

The problem

For about a year now, since we started our OCS pilot, I’ve had a Polycom CX700 on my desk. Actually, it has spent the bulk of that time on a shelf at my desk. It was purchased as a test device along with a collection of headsets and other OCS enabled devices. Most of them worked fine, but this one I just couldn’t seem to get working. After a lot of fiddling about I was able to log in successfully with a test user that had never ever logged on to any other device or computer. I happily thought I had it fixed, but as soon as I switched to another user it went back to the same old pattern: logon, crash and reboot in an endless loop.

Continue reading “How I finally upgraded the firmware on a Polycom CX700 running 1.0.522.73”