locked
any front-end tools for analysing archive logs? RRS feed

  • 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.UserUri

    Sunday, 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.UserUri

    Sunday, 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

    http://imwire.eventure.biz

     

    Wednesday, November 14, 2007 6:45 PM
  •  

    Anyone have a link to the .adm files for GPO settings with OCS 2007 to control the format options in Communicator 2007?
    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.biz
    Thursday, November 15, 2007 5:30 AM
  • Hi

    Does anyone know how to get these working on OCSR2?

    Wednesday, November 18, 2009 5:58 PM