Thursday, July 5, 2012

Office Communicator Server 2007 Simple Log - SQL

We currently use Office Communicator Server 2007 (OCS) and my boss asked me for a simple log of all the activity for the past month.  I thought an SQL Script would be easy to find on the internet but it wasn't so wrote my own and am posting it here:


OCS 2007 SQL - Simple Log:
SELECT MessageIdTime, uf.UserUri as [From], ut.UserUri as [To], m.Body
from LcsLog.dbo.Messages m
join LcsLog.dbo.Users uf on m.FromId = uf.UserId
join LcsLog.dbo.Users ut on m.ToId = ut.UserId
where 
m.MessageIdTime between '06/01/2012 00:00:00' AND '06/30/2012 23:59:59'

To strip out the HTML tags in Excel you start Excel's Replace (Edit/Replace or Ctrl+H) and put <*> (that is 3 characters, a less-than symbol, an asterisk, and a greater-than symbol) for your "Find what" condition and leave the "Replace with" field empty.

No comments:

Post a Comment

Phoenix

I am resurrecting this tech blog for notes related to Azure Logic Apps with SAP.