Update to OCS archiving script

Articles in the "OCS Archiving Reporter" series

  1. OCS Archiving Reporter: Group by conversation, filter dates
  2. Update to OCS archiving script [This article]

There are a couple of changes that have been made to improve the script.

First, after running a report for a user with thousands of messages and not having any idea of the progress while it was running, I added progress windows during the two phases (content conversion and HTML report creation). The progress window will tell you how many messages will be processed and how far along it is in that process.

Second, I discovered that hyperlinks in messages were not showing up in the report. Looking at the HTML (or XML) source, I could see the link was there, but the way it was formatted in the XML file (using element enclosures) meant the conversion to HTML was confusing the parser. Now I remove those characters prior to the link being written to the XML file.

I also optimized the message body conversion loop to remove redundant or obsolete code that was in the original script. There are also some minor cosmetic changes here and there.

Lastly, I realized last week that the script only reports on peer-to-peer (P2P) conversations, not multiparty. I spent some time trying to get multiparty conversations out of the database, but it is proving much more difficult than I anticipated. The format of multiparty IMs in the archiving database is very different than P2P conversations. I am still working on it, but I am not sure I will be able to make it work. In the meantime, I added a note in the report that states it is only for P2P conversations.

The original zip file with the script has been updated, but you can download it here:

  Get-ArchivingData.zip (3.1 KiB)

OCS Archiving Reporter: Group by conversation, filter dates

Articles in the "OCS Archiving Reporter" series

  1. OCS Archiving Reporter: Group by conversation, filter dates [This article]
  2. Update to OCS archiving script

The foundation for my script is from the OCS team at Microsoft, who created the original version.

The script from Microsoft grabs all the messages and outputs them all in one big table. I wanted it to be displayed more logically, grouping an entire conversation (session) together so it is easier to follow the context of the messages. This proved to be more daunting than I had anticipated.

All messages that occur in the same session have the same value for the SessionIdTime. With that I can group those messages together. But I needed a way to know when a new conversation started and ended. To get that I used the RANK function in SQL so that any rank of 1 indicates a new conversation. The resulting SQL query for a single user is the following (the ticks at the end of the lines are for PowerShell):

Now that I had the data, I needed to be able to use the XSLT to conditionally close a table the next time a rank of 1 is in the loop. I had the hardest time trying to make the xsl:if and xsl:choose statements work. Trying to include a </table> tag without the loop seeing the opening tag only caused errors. If I used HTML notation, &lt;/table&gt;, those characters were rendered as their literal ASCII representations rather than being parsed as HTML tags.

After trying for many, many hours, I decided to give up relying on the XML parser to convert the file to HTML and just do that parsing in the PowerShell script. This has the benefit of not requiring external files to run the script (the XSLT and msxsl.exe). The code below takes the XML output from the first part of the script and loops through each node (message). If the rank is 1, it closes the previous table, opens a new one, and writes the message to a row. If the rank is not 1, it means that a conversation has already been started and the message can simply being written in a new row.

Lastly, I wanted to be able to filter on a date range within the archiving database. So I added two arguments to the script after the users: -start and -end, both which are independently optional. If you don’t specify a second user or you only specify an end date with one or two users, you need to use the parameter names in the command. Otherwise, they are positional. The date format to use is “YYYY-MM-DD”. To account for the presence of the date filter, this code is added to the script:

The code is able to correctly parse the presence of a start date, end date, or both. Now the following can be added to the end of the code block in the SQL query that makes up the derived table (the part in the nested parentheses): + $start + $end +.

Finally, the output is a pretty page with the subtitle that indicates the date range used, the table headers are formatted to visually separate each conversation, and each table is an indicated of a single conversation, regardless of the number of messages shown.

I don’t expect you to copy and paste all the code above into the script and have it magically work (especially since I made other minor changes to account for the subtitle, additional arguments, etc.), so you can download the entire script below or on the Downloads page.

  Get-ArchivingData.zip (3.1 KiB)

OCS Web Components shares cannot use DFS

I tried mapping the location of the OCS presentation, metadata, and address book shares using DFS paths, only to get an error in the Create Pool Wizard.  The example in the wizard does show \\server\share, but I couldn’t imagine that it would be so archaic as to require hard-coded, specific servers in the UNC path.

The deployment guide doesn’t explicitly state that you can or cannot use DFS.  It does say that the wizard validates the path by attempting to set permissions, but it doesn’t say if it is trying to set share or NTFS permissions.  If it is trying to set share permissions, that isn’t going to work with DFS.  To top it off, there are no log files created during this test, so you can’t tell exactly what it is trying to do.

I resorted to calling Microsoft so I could ask them, and the confirmed that you have to go old-school and use \\server\share mapping.  I expressed my dismay at such an antiquated requirement and that it goes against best practices of hard-coding server names.  The engineer said that the product group has had several requests from customers to enable support for DFS, so I can only hope it will come in a service pack.