Customers sometimes ask how they can see archived instant messages after deploying the Microsoft Office Communication Server 2007 R2, Archiving Server. In response, we’ve developed a new tool called OCS IM Archive Viewer. Although there are some tools (recommended by support engineers at Microsoft) that can help customers retrieve instant messages from an archiving database, these tools don’t retrieve instant messages sent during a conference from an archiving database. You can download the OCS IM Archive Viewer tool from the Download Center.
Authors: Okan Gurer, Faruk Celik
Publication date: June 2011
Product versions: Microsoft Office Communications Server 2007 R2, Microsoft Office Communications Server 2007, Microsoft Lync Server 2010
The OCS IM Archive Viewer tool provides an easy way to retrieve peer-to-peer instant messages and instant messages that were sent during a conference from your archiving database. You can download the OCS IM Archive Viewer tool from the Download Center.
OCS IM Archive Viewer extends the functionality that is provided in the Updated—Windows PowerShell Script for Retrieving IM from an Archiving Database article.
Use the instructions in this section to set up and use OCS IM Archive Viewer.
1. Download OCS IM Archive Viewer.
2. Type the instance name of the SQL database in the OCS Archiving SQL Server Name field.
3. Type the database name in the OCS Archiving Database field. (The default Archiving Server database name is LcsLog.)
4. Click Connect to connect to the database. Notice that the Connect button has been replaced by the Disconnect button as shown in Figure 1.
Figure 1. OCS IM Archive Viewer v 1.0 dialog box
1. Select the 1:1 option as shown in Figure 2.
2. Type the SIP address of each user of interest—one per field—in the From/To User1 field and the From/To User2 field.
3. To view all the instant messages that have been sent to and from only one user, type the SIP address of the user in the From/To User1 field.
4. Select a time interval for the instant messages that you want to retrieve, if needed.
5. Click Retrieve Messages to display the instant messages in the data grid window.
Figure 2. Options for retrieving peer-to-peer instant messages
1. Select the Conference option as shown in Figure 3.
2. Specify the SIP address of a user in the From/To User1 field.
3. Click Retrieve Messages to display all instant messages that were sent during an instant messaging conference that included this user.
Figure 3. Options for retrieving instant messages that were sent during a conference
OCS IM Archive Viewer provides the option to view instant messages as HTML. Click the Render the output as HTML in IE button to display the output in your browser as shown in Figure 4 and Figure 5.
Figure 4. HTML output of retrieved peer-to-peer instant messages
Figure 5. HTML output of retrieved instant messages in a conference
Use OCS IM Archive Viewer to easily retrieve peer-to-peer instant messages and instant messages that were sent during a conference from your archiving database.
The Microsoft.NET Framework 2.x must be installed. The OCS IM Archive Viewer tool works with the archiving databases in both Office Communications Server 2007 R2 and Office Communications Server 2007. Currently, this tool has not been tested with the Microsoft Lync Server 2010 communications software Archiving database.
The following operating systems are supported:
The OCS IM Archive Viewer tool can retrieve and display information from the Archiving back-end database. This tool enables customers to view archived peer-to-peer instant messages and instant messages sent during in a conference.
To learn more, check out the following articles and tools:
· Updated—PowerShell Script for Retrieving IM from Archiving Database
· ArchivingCDR Reporter (ArchivingCDRReporter.exe, ArchivingCDRReporter_Config.xml)
· Office Communications Server 2007 R2 Resource Kit Tools
Is it working with Lync Archiving role?
As per as my tests, yes it is working on Lync Archiving database as well. Please share all your experiences if you face any issue. Thanks.
It is working perfectly with Lync 2010... you only need to run it with elevated privileges
A customer asks: "I am trying to use your tool for querying my OCS Archive database. However, it gives me a time out when attempting to do so. I have tried the OCS Archive viewer on both the same system that is hosting the OCS Archive database, and a separate system. I get an error message which appears to be a timeout." Any ideas here, folks?
This is an already reported issue to us (Faruk Celik and Okan Gurer) . Actually this is not a bug but “by design” issue. SqlException with “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding” error message thrown when the query issued by the tool takes more than the period defined in SqlCommand timeout. The default value for SqlCommand timeout in ADO.NET is 30 seconds.
High likely our customer is querying a long datetime period and it's taking lots of time inside OCS Arhciving database server. As a result OCSArchiveViewer stops saying "it took so long, I'd better cut".
This issue will not appear if you selects a small datetime range because it takes less time inside SQL Server Engine to gather the asked data.
The only workaround this is decreasing the datetime period that you’re querying and getting the results in more than one. Simply instead of querying 6 months period for a SIP, try to query for this for 2 months if it works fine for 2 months period, you can split this 6 months perioed into 3 pieces and you can query 3 times.
A few notes about this :
1)The very same query may not raise the error today with 30 seconds timeout but tomorrow it may work because of less load (Assuming that this SQL Server is also used for other purposes than OCS IM Archiving purposes)
2) It's always better to run the OCSArchiveViwere on top of the very same machine your SQL Server storing your OCS/Lync IM archive data. Because if you’re reaching the SQL Server through a VPN over WAN in another location and you’re running the OCSArchiveViewer on a remote machine, probability if getting similar errors like that much more due to network overhead. For such VPN scenarios ets, as a very easy workaround (if applicable, I mean if the SQL Server admin gives you remot oi ealcgcneper)i,s oinstall OCSArchiveViewer on your remote SQL Server machine and run OCSArchiveViewer from there by RDPing to SQL Server machine. As OCSArchiveViewer will be reaching SQL Server locally, it will work faster for sure
To “fix” this, we’re planning to add a “Command Timeout” textbox. By default the CommandTimeout value will be 30 seconds again. If the querying fails because of CommandTimeout with this information, we will pop up a message like “Please increase the command timeout value. The data that you’re querying is taking long time to retrieve from SQL server”) for such long running queries.
I'll announce from here when we've released the new/fixed version of OCS Archive Viewer very soon.
Hi Faruk: did you manage to fix this issue? "Very soon" has already expired a couple of months ago :)
Yes, it's already fixed and sorry about this delay. As we've received some other feedbacks than the one above, we experienced a bit delay.
Even though it's just a tool, every single build needs to pass some internal processes because this tool is published on Microsoft Download Center and it needs to pass the processes every single Microsoft "product" pass.
We'll do our best to finish the planned fixes, enhancements asap.
Faruk: you are suggesting that as the time of writing my post here, the downloadable version of the tool is still experiencing the issue?
Adding this reader comment (sent to NextHop feedback alias): "Is there an issue with this tool when trying to pull conversations using the 1:1 Messages button? I get all data instead of the data just between the two dates entered."
A reader asks (sent to NextHop feedback alias): "I am trying to use your tool for querying my OCS Archive database, however it gives me a time out when attempting to do so. I have tried the OCS Archive viewer on both the same system that is hosting the OCS Archive database, and a separate system. I get the below error message which appears to be a timeout."
When trying to use this tool (v1.0) to retrieve conference conversations I get an error 'failed to convert parameter value from a Guid to a Int32'
Retrieving a 1:1 conversation works fine only getting the error for the conference chat.
Any ideas on how to resolve?
Could you please share the exact error message ? If you get a stack trace appreciate if you can post here so we can take a look
Unhandled exception has occurred in your application. if you click continue, the application will ignore this error and attempt to continue. if you click quit, the application will close immediately.
Failed to convert parameter value from guid to a int32.
is this the rest of what you require:-
************** Exception Text **************
System.InvalidCastException: Failed to convert parameter value from a Guid to a Int32. ---> System.InvalidCastException: Object must implement IConvertible.
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at OCSArchiveViewer.Form1.btnGetMessages_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
Got this trying to connect to Microsoft Lync Server 2010 (4.0.7577.0)
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
System.NullReferenceException: Object reference not set to an instance of an object.
at OCSArchiveViewer.Form1.btnConnectToSQL_Click(Object sender, EventArgs e)
************** Loaded Assemblies **************
Assembly Version: 22.214.171.124
Win32 Version: 2.0.50727.5456 (Win7SP1GDR.050727-5400)
Assembly Version: 126.96.36.199
Win32 Version: 188.8.131.52
Win32 Version: 2.0.50727.5460 (Win7SP1GDR.050727-5400)
Win32 Version: 2.0.50727.5462 (Win7SP1GDR.050727-5400)
Win32 Version: 2.0.50727.5420 (Win7SP1.050727-5400)
Win32 Version: 2.0.50727.4927 (NetFXspW7.050727-4900)
I am trying to use this tool, and when I try to get it to even connect, I am getting the timeout error. Any thoughts?