Office Communicator Queries

The following are some user specific SQL query samples that are intended to guide you through extracting relevant reporting user or time range specific information from the Database.

1. Find IMs containing specific word.

SELECT * FROM DatabaseName.dbo.Messages where Body like '% search-word %'

2. Find all IMs sent by a user.

SELECT * from DatabaseName.dbo.Messages, DatabaseName.dbo.Users where DatabaseName.dbo.Users.UserId = DatabaseName.dbo.Messages.FromId and DatabaseName.dbo.Users.UserUri = ‘user-you-are-looking-for’

3. Find all IMs received by a user.

SELECT * from DatabaseName.dbo.Messages, DatabaseName.dbo.Users where = DatabaseName.dbo.Users.UserIdDatabaseName.dbo.Messages.ToId and DatabaseName.dbo.Users.UserUri = ‘user-you-are-looking-for’

4. Find IM body, sender and receiver information from all IMs exchanged between two users

SELECT body, u1.UserUri as [from], u2.UserUri as [to] from DatabaseName.dbo.Messages, DatabaseName.dbo.Users u1, DatabaseName.dbo.Users u2 where DatabaseName.dbo.Messages.FromId = u1.UserId and DatabaseName.dbo.Messages.ToId = u2.UserId and u1.UserUri = ‘first-user’ and u2.UserUri = ‘second user’

UNION ALL

SELECT body, u1.UserUri , u2.UserUri from DatabaseName.dbo.Messages, DatabaseName.dbo.Users u1, DatabaseName.dbo.Users u2 where DatabaseName.dbo.Messages.FromId = u2.UserId and DatabaseName.dbo.Messages.ToId = u1.UserId and u2.UserUri = ‘first-user’ and u1.UserUri = ‘second user’

5. Find all IMs sent between certain time range

SELECT * from dbo.Messages where  SessionIdTime > '2010-05-10' and SessionIdTime < '2010-05-17'

6. Find all IMs sent by a user within time range

SELECT * from dbo.Messages where  FromId ='46' and  SessionIdTime > '2010-01-28' and SessionIdTime < '2010-02-04' 



Comments