Thursday, February 16, 2012

Tracking IM usage in OCS 2007 R2

I could not find something generic that comes out of the box from Microsoft to perform retrieve the usage of Instant Messaging by users in my infrastructure setup for some auditing purposes.
The below is a little SQL query which I have written for the purpose to share.



A sample screenshot of the output is as below



 Declare  @user varchar(50)
Declare @ startdate,@enddate datetime

'Replace usersip with your user's SIP address
Set @user = 'usersip'
'Replace mm/dd/yyyy with appropriate dates
set @startdate = 'mm/dd/yyyy'
set @enddate = 'mm/dd/yyyy'

SELECT [SessionIdTime] as 'StartDate'
        ,[SessionEndTime] as 'EndDate'
      ,user1.UserUri as User1
      ,user2.UserUri as User2
      ,[User1MessageCount]
      ,[User2MessageCount]
     
  FROM SessionDetails

  left join dbo.Users user1 on user1.UserId = User1Id
  left join dbo.Users user2 on user2.UserId = User2Id

Where (datediff(hh, convert(date,SessionIdTime),@enddate)< datediff(hh,@startdate,  @enddate)) and user1.UserUri in (@user)

No comments:

Post a Comment