Skip to the main content.

1 min read

OCS 2007 Archiving SQL Queries

These are copied from the ArchivingCDRReporter read me…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 Archiving/CDR Database. The general usage queries are already added as part of the tool.

1. Find IMs containing specific word.

SELECT * FROM DatabaseName.Dbo.messages where body like N’%your-search-word%’

2. Find all IMs sent by a user.

SELECT * from DatabaseName.Dbo.messages, DatabaseName.Dbo.users where DatabaseName.Dbo.users.userid = 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.userid = DatabaseName.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 DatabaseName.Dbo.messages where date (greater than symbol) ‘Start time’ and date (less than symbol) ‘end time’

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

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’ and date (greater than symbol) ‘Start time’ and date (less than symbol) ‘end time’

7. General usage report data

SELECT r.dialogid as “Dialog ID”, u1.useruri as “From user”, u2.useruri as “To user”, media.starttime as “Start Time”, datediff(mi,starttime, endtime) as “Time in minutes”, medialist.media as “Session type”

from users u1

inner join requestresponsecount r on r.fromid = u1.userid

inner join users u2 on r.toid = u2.userid

inner join media on media.dialogid = r.dialogid

inner join medialist on medialist.id = media.mediaid

Note: Change mi to s if you want the length of session in seconds

Important 2024 Microsoft Licensing Updates

Important 2024 Microsoft Licensing Updates

There is some big news in the world of Microsoft licensing this month! In the summer of 2023, Microsoft modified the licensing for Microsoft 365,...

Transforming TCRG's Legacy Systems into a Secure Cloud Future with CloudServus

Transforming TCRG's Legacy Systems into a Secure Cloud Future with CloudServus

TCRG (The Consolidated Rehab Group), specializing in vocational rehabilitation for military personnel and veterans, partnered CloudServus, a leader...

The Power of Microsoft Copilot for Security: Insights from a Recent Whitepaper

The Power of Microsoft Copilot for Security: Insights from a Recent Whitepaper

At CloudServus, we continuously explore innovative solutions to enhance cybersecurity effectiveness and efficiency. Our team recently came across an ...