any front-end tools for analysing archive logs?

Az összes válasz

• 2007. augusztus 2. 6:03

   

   
  

  0

  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.

   
• 2007. szeptember 21. 13:24

   

   
  

  0
  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.
  
   
• 2007. szeptember 23. 21:26

   

   
  

  0

  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

   
• 2007. szeptember 24. 9:59

   

   
  

  0
  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?
  
   
• 2007. szeptember 24. 10:38

   

   
  

  0

  Hello,

   

  How can we determine UserUri of the user who initiated a Voip session (Peer to peer and multiparty session)?

   

  Regards,

  Vivek

   
• 2007. november 6. 21:49

   

   
  

  0

   

  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%

   
• 2007. november 14. 18:45

   

   
  

  0

  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

   

   
• 2007. november 14. 18:52

   

   
  

  0

   

  Anyone have a link to the .adm files for GPO settings with OCS 2007 to control the format options in Communicator 2007?
   
• 2007. november 15. 5:30

   

   
  

  0
  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
  
   
• 2009. november 18. 17:58

   

   
  

  0
  Hi
  
  Does anyone know how to get these working on OCSR2?