//
you're reading...
OCS, Technical, Windows Server

How to monitor and log, centrally, OCS 2007 R2 desktop sharing logs


First, install an OCS 2007 R2 monitoring server, on a seperate server from the OCS pool.  Once that is setup using the MS directions in the link at the beginning of this post.  Create a new SQL View on the LcsCDR database with the following code below.

USE [LcsCDR]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[UserMedia]
AS
SELECT     SD.SessionIdTime, SD.SessionIdSeq, SD.InviteTime, SD.ResponseTime, SD.SessionEndTime, SD.ResponseCode, ISNULL(SD.User1Id, 0) AS User1Id,
                      ISNULL(SD.User2Id, 0) AS User2Id, ISNULL(SD.TargetUserId, 0) AS TargetUserId, ISNULL(SD.SessionStartedById, 0) AS SessionStartedById,
                      ISNULL(SD.OnBehalfOfId, 0) AS OnBehalfOfId, ISNULL(SD.ReferredById, 0) AS ReferredById, ISNULL(U1.UserUri, ”) AS User1Uri, ISNULL(U2.UserUri,
                      ”) AS User2Uri, ISNULL(UT.UserUri, ”) AS TargetUserUri, ISNULL(US.UserUri, ”) AS SessionStartedByUri, ISNULL(UB.UserUri, ”) AS OnBehalfOfUri,
                      ISNULL(UR.UserUri, ”) AS ReferredByUri, dbo.MediaList.Media
FROM         dbo.SessionDetails AS SD INNER JOIN
                      dbo.Media ON SD.SessionIdTime = dbo.Media.SessionIdTime AND SD.SessionIdSeq = dbo.Media.SessionIdSeq INNER JOIN
                      dbo.MediaList ON dbo.Media.MediaId = dbo.MediaList.MediaId LEFT OUTER JOIN
                      dbo.Users AS U1 ON U1.UserId = SD.User1Id LEFT OUTER JOIN
                      dbo.Users AS U2 ON U2.UserId = SD.User2Id LEFT OUTER JOIN
                      dbo.Users AS UT ON UT.UserId = SD.TargetUserId LEFT OUTER JOIN
                      dbo.Users AS US ON US.UserId = SD.SessionStartedById LEFT OUTER JOIN
                      dbo.Users AS UB ON UB.UserId = SD.OnBehalfOfId LEFT OUTER JOIN
                      dbo.Users AS UR ON UR.UserId = SD.ReferredById

Advertisements

Discussion

2 thoughts on “How to monitor and log, centrally, OCS 2007 R2 desktop sharing logs

  1. can you help me? How can I create new SQL View on the LcsCDR database ? Thanks a lot!

    Posted by Anton26 | July 28, 2011, 11:07 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: