Per-User Conference joins grouped by UriType SQL Query

I recently had a request from a customer to gather conference data group by individual user. They wanted to see this data in two different formats:

1. Per-User conference join with SessionIdTime, Conference type, and ConferenceUri

2. Total Per-User Conference joins grouped by Conference type

So with the help of a colleague Tommy Mhire, we (mostly him), came up with the two queries below. These queries should be run against your CDR DB. Also depending on how long you maintain your monitoring data that will dictate how much data you can view.

Per-User conference join with SessionIdTime, Conference type, and ConferenceUri in certain date range¹

Select Users.UserUri,ConferenceSessionDetails.SessionIdTime,UriTypes.UriType

, ConferenceSessionDetails.SessionEndTime, ConferenceUris.ConferenceUri

From ConferenceSessionDetails

left outer join ConferenceUris on ConferenceSessionDetails.McuConferenceUriId = ConferenceUris.ConferenceUriId

left outer join UriTypes on ConferenceUris.UriTypeId = UriTypes.UriTypeId

left outer join Users on ConferenceSessionDetails.UserId = Users.UserId

Where ConferenceSessionDetails.SessionIdTime >= '06-01-2013'

Order by Users.UserUri, ConferenceSessionDetails.SessionIdTime desc

¹The “NULL” value represents a conference join

Total Per-User Conference joins grouped by Conference type in certain date range¹

Select UserUri, UriType, COUNT(*)

From ConferenceSessionDetails

left outer join ConferenceUris on ConferenceSessionDetails.McuConferenceUriId = ConferenceUris.ConferenceUriId

left outer join UriTypes on ConferenceUris.UriTypeId = UriTypes.UriTypeId

left outer join Users on ConferenceSessionDetails.UserId = Users.UserId

Where ConferenceSessionDetails.SessionIdTime >= '01-01-2014'

Group by Users.UserUri, UriType

Order by Users.UserUri

¹Again the “NULL” will represent all conference joins regardless of the modality.