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