Answered by:
any front-end tools for analysing archive logs?

Question
-
Woudl like to get some reports from our Archive Server's logs.
For example who (table Users) is using what features (MediaList) of OCS and when (guess from SessionDetails). Have tried to create a query to do this but not having much luck.
Are there any tools or queries alrealdy to serve this kind of funtionality?
I have tried using the ArchivingCdrReporter.exe tool with its default settings but this only gives the totals.
Also can't find any documentation on the tables or examples of scripts to use.
Any help here would be much appreciated
Tuesday, July 31, 2007 1:03 AM
Answers
-
The ArchivingCDRReporter tools is mainly for generic usage information queries.
The ArchivingCDRReporter readme has these sample 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 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 > ‘Start time’ and date < ‘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 > ‘Start time’ and date < ‘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.
Thursday, August 2, 2007 6:03 AM -
I made the below query on LcsLog table to list who is using what features of OCS and when:
SELECT dbo.MediaList.Media, dbo.Users.UserUri, dbo.Media.SessionIdTime, dbo.SessionDetails.SessionEndTime
FROM dbo.Media INNER JOIN
dbo.MediaList ON dbo.Media.MediaId = dbo.MediaList.MediaId INNER JOIN
dbo.SessionDetails ON dbo.Media.SessionIdTime = dbo.SessionDetails.SessionIdTime AND
dbo.Media.SessionIdSeq = dbo.SessionDetails.SessionIdSeq INNER JOIN
dbo.Users ON dbo.SessionDetails.User1Id = dbo.Users.UserId OR dbo.SessionDetails.User2Id = dbo.Users.UserId OR
dbo.SessionDetails.SessionStartedById = dbo.Users.UserId
ORDER BY dbo.Users.UserUriSunday, September 23, 2007 9:26 PM
All replies
-
The ArchivingCDRReporter tools is mainly for generic usage information queries.
The ArchivingCDRReporter readme has these sample 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 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 > ‘Start time’ and date < ‘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 > ‘Start time’ and date < ‘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.
Thursday, August 2, 2007 6:03 AM -
Hi!
Maybe I am doing something wrong, but the samples above I wanted to try and insert into the XML, make the archiving reporter tool crash immediately.Friday, September 21, 2007 1:24 PM -
I made the below query on LcsLog table to list who is using what features of OCS and when:
SELECT dbo.MediaList.Media, dbo.Users.UserUri, dbo.Media.SessionIdTime, dbo.SessionDetails.SessionEndTime
FROM dbo.Media INNER JOIN
dbo.MediaList ON dbo.Media.MediaId = dbo.MediaList.MediaId INNER JOIN
dbo.SessionDetails ON dbo.Media.SessionIdTime = dbo.SessionDetails.SessionIdTime AND
dbo.Media.SessionIdSeq = dbo.SessionDetails.SessionIdSeq INNER JOIN
dbo.Users ON dbo.SessionDetails.User1Id = dbo.Users.UserId OR dbo.SessionDetails.User2Id = dbo.Users.UserId OR
dbo.SessionDetails.SessionStartedById = dbo.Users.UserId
ORDER BY dbo.Users.UserUriSunday, September 23, 2007 9:26 PM -
Can this simple be put into that XML file, or by running it through a SQL query? If the answer is the 2nd, how can I run a query against sql express?Monday, September 24, 2007 9:59 AM
-
Hello,
How can we determine UserUri of the user who initiated a Voip session (Peer to peer and multiparty session)?
Regards,
Vivek
Monday, September 24, 2007 10:38 AM -
I'm having a weird problem, with OCS 2007 installed, archiving service installed and verified, data is being logged to the database for IM archiving, but when I query the datbase, I get a lot of data back that looks like this:
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Shell Dlg 2;}}
{\colortbl ;\red0\green0\blue0;}
{\*\generator Msftedit 5.41.15.1507;}\viewkind4\uc1\pard\tx720\cf1\f0\fs20 1234\par
}It looks like formatting data, but the problem is that when I send an IM message, sometimes I see the message body, and sometimes I don't, and a lot of times I see the above formatting code. I am missing lots of conversation data in the archiving database. I can do a live test, send an IM and refresh my SQL query, and its hit and miss, sometimes I see my messages go to the database and sometimes I don't.
Any ideas? We can't deploy this until archiving works 100%
Tuesday, November 6, 2007 9:49 PM -
Hi,
OCS stores messages in RTF format and plain text depending upon the client (Communicator desktop client or Web Client) used by users. Administrators can also disable RTF formatted messages using GPO.
Regards,
Vivek
Wednesday, November 14, 2007 6:45 PM -
Wednesday, November 14, 2007 6:52 PM
-
Hi,
Download link for ADM file and documentation is:
http://www.microsoft.com/downloads/details.aspx?FamilyID=dd3cae08-3153-4c6a-a314-daa79d616248&displaylang=en
Regards,
Vivek Garg
http://imwire.eventure.bizThursday, November 15, 2007 5:30 AM -
Hi
Does anyone know how to get these working on OCSR2?Wednesday, November 18, 2009 5:58 PM