Category: Knowledge Base

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'