Welcome to Community Server Sign in | Join | Help
Search
BlogsVideosPhotosFiles

Syndication

This Blog

Post Categories

Archives

Powershell Script for Retriving IM from Archving Database

When customers deploy Office Communications Server 2007 Archiving they quickly request tools or sample queries to retrieve their data. The Office Communications Server 2007 Resource Kit includes the ArchivingCDR Reporter tool. This reporting tool has built-in SQL queries to retrieve and view information from the Archiving and Call Detail Records (CDR) Backend. The tool enables the user to view Office Communications Server 2007 usage reports based on the Archiving and CDR tables.

 

A member of our team worked on a sample* using PowerShell technology to gather all IMs for a given user or all IMs between two users. The result is output to console and a local im.xml file. The script will support the following message types:

  • text/plain
  • text/x-msmsgsinvite
  • text/rtf

To use the tool, you will need to download the zip file (updated to handle special characters on April 17, 2008 (Updated and tested by our Austrian peer Thomas Binder) and extract all files to one common directory and run ps1script. Please note the tool has the following dependencies:

  1. PowerShell v1
  2. NET 2.0 Framework 
  3. To make XLS to HTML transformation you would need MSXML 4.0 Service Pack 2 (Microsoft XML Core Services) MSXML.exe is currently part of the ZIP but it should be the same as  Command Line Transformation Utility (msxsl.exe)  

The first two items are mandatory; the third is nice to have as it will create an HTML page for the XML output.

 

July 2008 update from Thomas Binder:

Details: As recommended in one of the commentary postings, I replaced the line

Add-Content -Path $LocalPath -Value '<?xml version="1.0" encoding="UTF-16"?>'

With:

Add-Content -Path $LocalPath -Value '<?xml version="1.0" encoding="ISO-8859-2"?>'

 

- Thomas Laciano

  Sr. Program Manager, Unified Communications Group

- Sasa Juratovic

  Program Manager II, Unified Communications Group

 

* This is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of Use (http://www.microsoft.com/info/cpyright.htm)

Published Monday, January 14, 2008 1:05 PM by ocsteam
Filed Under: ,
Attachment(s): Get-ArchivingData-July-2008.zip

Comments

 

kostas said:

I tried your script and works fine. But when I sent messages in greek I got this error in powershell :

Error occurred while parsing document.

Code:   0xc00ce508
URL:    file:///C:/ocs/IM.xml
Line:   63
Column: 16
An invalid character was found in text content.
The term '.\IM.htm' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try
again.
At C:\ocs\get-ArchivingData.ps1:114 char:8
+ .\IM.htm <<<<


When I open the xml file I get The XML page cannot be displayed instead greek messages. Is anything that I can do for this error?

Thanks.
January 19, 2008 11:42 AM
 

pachox said:

it works great.
will you make a script for voip too?
March 4, 2008 5:00 AM
 

tcm said:

Are there any other tools out there? Any 3rd party vendors that you can point us to?
April 8, 2008 11:20 AM
 

elmerick said:

Do you have to install Powershell on the OCS servers or can you run the utilities remotely?
April 17, 2008 3:29 PM
 

elmerick said:

I cannot get it to work.  Do you have to know the instance name?  I just passed it the SQL Server name and it just comes up with a blank screen and does not list anything.
April 17, 2008 3:55 PM
 

johnnyq said:

works great ...I get the information on the console and in the IM.xml file - I have problems with creating the html file, the error generated is
"switch from current encoding to specified encoding not supported"
April 22, 2008 5:05 PM
 

rhlavienka said:

Wher could I find IMTransformation.xslt ?
April 25, 2008 5:52 AM
 

jluser said:

Elmerick:
You should be able to run this script from any system with PowerShell installed.  It establishes a connection directly to the SQL server, so it should not need to be run from the OCS server.  The format is:

.\get-ArchivingData.ps1 (SqlServerName)\(OCS SQL Instance) username@sipdomain.com (optional:) username2@sipdomain.com

So, my OCS SQL instance is on SQL3, and it's named OCS.  I'm looking for chats between xpuser1@sipdomain.com and xpuser2@sipdomain.com.  My string is:

.\get-ArchivingData.ps1 SQL3\OCS xpuser1@sipdomain.com xpuser2@sipdomain.com

You DON'T have to enter a second user.  So if i wanted EVERYTHING xpuser1@sipdomain.com has ever sent/received, I just leave OUT xpuser2@sipdomain.

Rhlavienka:
I'm missing IMTransformation.xslt too.  Without the XML stylesheet, the conversion from XML to HTM won't take place.

SASA AND THOMAS ***
Can we get a complete copy of the zip file uploaded, INCLUDING the XML Stylesheet that's referenced in the ps1 file, so that everything works as intended?  Thanks!
April 25, 2008 5:30 PM
 

sreyes said:

I'm getting the same error.
.\IM.htm' is not recognized as a cmdlet, function, operable program, or script file , do you have a solution for this? Thanks.
April 27, 2008 11:04 PM
 

rhlavienka said:

modify right enoding in script

my modifcation is
<?xml version="1.0" encoding="ISO-8859-2"?>'

but still I don't have XML stylesheet IMTransformation.xslt
April 29, 2008 9:27 AM
 

sreyes said:

@ rhlavienka
thanks , but same result if you I try deleting the first line in IM.htm,

anyone got it to work?
May 7, 2008 11:54 PM
 

Bigcahoona said:

Should this work with SQL 2000?
May 21, 2008 11:22 AM
 

msternin said:

I liked the idea of this script a lot, but decided I wanted to add a bit more functionality and put a WinForm wrapper around it.  While I’m not the best GUI designer, what I’ve done certainly serves my needs.  As far as added functionality, I added the ability to select date ranges for those who want an easy way of narrowing the scope of an IM conversation.  I’m not sure if I can post the source to this forum, but I’m certainly open to sharing it with however wants it.  Of course credit for the base script and SQL queries should absolutely go to authors of the article “Powershell Script for Retrieving IM from Archiving Database” – (the article title on the blog site has a couple of typos in it.  This is how the correct spelling of certain words should be.)
M.
May 25, 2008 6:27 PM
 

Rogers said:

@Bigcahoona

Just delete IM.xml and open get-ArchivingData.ps1 in notepad. Replace the following line.  

Add-Content -Path $LocalPath -Value '<?xml version="1.0" encoding="UTF-16"?>'
By:
Add-Content -Path $LocalPath -Value '<?xml version="1.0" encoding="ISO-8859-2"?>'
May 26, 2008 7:48 AM
 

jl_badong said:

Hi,

We have a working OCS server and we're able to use the archiving services properly. The problem is with im conversations of more than 2 users (conference), the messages are archived in multipart mime format. We've created a custom component to retrieve rtf and plain text archives but we don't know how to process this mime messages.
Have you experienced creating any custom components for retrieving conference archives?

Thanks!
May 27, 2008 1:05 AM
 

msternin said:

What's the ContentTypeId on those messages?
May 27, 2008 3:09 PM
 

Bigcahoona said:

@Rogers,,   Many thanks im getting the following unable to connect error does SQLSERVER\DBASE work when remote connecting to SQL 2000?

Exception calling "Fill" with "1" argument(s): "An error has occurred while establishing a connection to the server.
hen connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server
es not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specif
d)"
May 30, 2008 8:26 AM
 

Bigcahoona said:

@msternin  im interested in your revisions anywhere i can get a copy?
May 30, 2008 8:28 AM
 

msternin said:

 
Bigcahoon, Send your email addy to me @ msternin@yahoo.com
June 4, 2008 8:11 PM
 

Akhnot said:

Bigcahoona, I am receiving the same error you were, and am also using SQL 2000. Did you find a resolution to that issue? Thanks for any info.
June 25, 2008 1:00 PM
 

brunoestrozi said:

Anyone know How can I change the default GMT 0 to GMT -3:00 in Archive Server ?
July 16, 2008 11:43 PM
 

Bigcahoona said:

@Akhnot
Yes the Database name is in the script i put this in line with our db and the run line is "script" SQLSERVER sip.address@domain.here
July 21, 2008 9:43 AM
 

gokemen said:

-------I'm getting these type of errors, so I couldnt find what the reason is. Can anybody help me in this group? I will appreciate...-------

Invalid assignment expression. The left hand side of an assignment operator needs to be something that can be assigned
to like a variable or a property.
At line:3 char:21
+     [string]$User1 =  <<<< $(throw "gokmen@etitest2.local"),


Unable to index into an object of type System.Data.DataTableCollection.
At line:1 char:17
+ $dataset.Tables[0 <<<< ].TableName = "ContentTypes"

Exception calling "Fill" with "1" argument(s): "An error has occurred while establishing a connection to the server.  W
hen connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server do
es not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
)"
At line:1 char:9
+ $da.Fill( <<<< $dataset) | out-null
August 28, 2008 1:47 AM
 

ITLash said:

Hey there - thanks for the script!  Unfortunately, I'm getting same errors as gokemen.  Anybody have any assistance?
October 28, 2008 10:44 AM
 

ITLash said:

Changed syntax from "sqlserver\instance" to just "sqlserver" - fixed my error.
October 28, 2008 11:34 AM
 

rodrigo_sebben said:

Hello folks, I would like to share another way that I found to avoid these RTF format in the archiving database...

Instead of using powershell to convert RTF format, I used a client option to prevent that...

Using office communicator policy adm file, enabling "Prevent Rich Text in Instant Messages"

or creating this registry key, HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Communicator\DisableRtfIM = 1 (DWORD)

After enable this key or policy, you should restart your communicator...

Regards
January 19, 2009 7:28 AM
 

yergg said:

I would like to specify at least a date in which to extract the data from. Anyone know how I would build that into the query to only pull the converstations with a specified timestamp or date or even a range of dates?
June 10, 2009 5:36 PM
Anonymous comments are disabled

News

This is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of Use.

Poll:

MSFT Product Team Blogs

MVP Blogs, Forums &amp; Websites

Other UC Blogs

Microsoft UC Sites

Community
Powered by Community Server, by Telligent Systems