Skip to main content

Find OCS Live Meeting Users with a SQL Query

Author by Michael Epping

During a recent OCS to Lync Server 2013 project I found that we needed to identify the OCS users who utilized Live Meetings the most heavily. Lync Server 2013 doesn't really handle the migration of Live Meeting content from OCS since it doesn't include a legacy MCU like Lync Server 2010 did, so it was necessary to identify the heaviest Live Meeting users so we could focus on training them to recreate their Live Meetings as Lync Meetings after migration. There are several tools in the OCS Reskit that can help identify the Live Meeting users, but they don't make it easy to figure out who is a heavy user, who has used Live Meeting recently, etc. Luckily the sort of information I was interested in was kept in the RTC backend database in OCS. The information on Live Meetings is stored in the Conference table in the RTC database, so I created a really basic SQL query that grabs some info from that table. The INNER JOIN portion is necessary to correlate the data from the Conferencing table with a SIP address stored in the Resource table since the OrganizerId section of the Conference table isn't very useful on it's own.
SELECT DISTINCT r.UserAtHost,OrganizerId,ExternalConfId,Title,Description,ProvisionTime,ExpiryTime,LastUpdateTime,LastActivationVersion,LastActivateTime FROM RTC.dbo.Conference c 
INNER JOIN RTC.dbo.Resource r ON c.OrganizerId = r.ResourceId
The output should look something like this: SQL Query to Find OCS Live Meeting Users I ended up exporting the output as an RPT file and opened it in Excel, where it's much easier to view and manipulate: [caption id="attachment_25040" align="alignnone" width="625"]Conference information correlated with SIP addresses, creation dates, update dates, etc. Conference information correlated with SIP addresses, creation dates, update dates, etc.[/caption]

Michael Epping

Systems Engineer