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 range1
 
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
 
 
1The “NULL” value represents a conference join
 
 
Total Per-User Conference joins grouped by Conference type in certain date range1
 
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
 
 
1Again the “NULL” will represent all conference joins regardless of the modality.