Good afternoon all,

I have an interesting scenario in which I am trying to query the Lync SQL Database and get time, date, whether it was a conference call or a regular call, the incoming number, the outgoing number and whether the call originator was internal or night.

If I run this query below, I get a ton of records

SELECT DISTINCT TOP (10000) pf.PhoneUri AS NumberFrom, ft.PhoneUri AS NumberTo
FROM         dbo.Phones AS ft RIGHT OUTER JOIN
                      dbo.VoipDetails AS v ON ft.PhoneId = v.ConnectedNumberId LEFT OUTER JOIN
                      dbo.Phones AS pf ON v.FromNumberId = pf.PhoneId

If I run this query below, I get only 87

SELECT DISTINCT
                      TOP (10000) c.ConferenceUriId, c.ConferenceStartTime AS Start, c.ConferenceEndTime AS Finish, f.IsUserInternal, pf.PhoneUri AS NumberFrom,
                      ft.PhoneUri AS NumberTo, f.UserRole, f.UserJoinTime, f.UserLeaveTime, f.ClientVerId, dbo.ClientVersions.Version, dbo.ClientVersions.ClientType
FROM         dbo.ClientVersions INNER JOIN
                      dbo.FocusJoinsAndLeaves AS f ON dbo.ClientVersions.VersionId = f.ClientVerId RIGHT OUTER JOIN
                      dbo.Conferences AS c INNER JOIN
                      dbo.VoipDetails AS v ON c.SessionIdTime = v.SessionIdTime ON f.SessionIdTime = c.SessionIdTime AND
                      f.SessionIdSeq = c.SessionIdSeq LEFT OUTER JOIN
                      dbo.Phones AS ft ON v.ConnectedNumberId = ft.PhoneId LEFT OUTER JOIN
                      dbo.Phones AS pf ON v.FromNumberId = pf.PhoneId
ORDER BY Start DESC

I have looked at the schemas for Lync server as well as the views that come, but what I am trying to do just doesn't come out right and there definitely is not enough documentation out there for me to solve this myself (Oh, I have been working with SQL Server since 6.5, so I am up proper querying).

Any point in the right direction would be great

Hello,

Thank you for your post.

This is a quick note to let you know that we are performing research on this issue.

Thanks,

Try to break you query into 2 parts

1. Whether it was a conference call or a regular call.

2. Whether the call originator was internal or night.

And check if you are getting correct results for the first part.

-Santosh

Did you figure this out? We are looking to do something similar. We are trying to audit users calling into the bridge for chargeback purposes. The only thing I would need to add is who is the owner of the meeting.