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):

SELECT RANK() OVER(PARTITION BY SessionIdTime ORDER BY SessionIdTime, MessageIdTime) AS 'Rank', `
	SessionIdTime, MessageIdTime, Body, ContentTypeId, [from], [to] `
FROM `
(SELECT SessionIdTime, MessageIdTime, Body, ContentTypeId, u1.UserUri AS [from], u2.UserUri AS [to] FROM Messages, `
	Users u1, Users u2 WHERE Messages.FromId = u1.UserId AND Messages.ToId = u2.UserId AND u1.UserUri = '$User1' `
	AND LcsLog.dbo.Messages.Toast IS NULL `
UNION ALL ` 
SELECT SessionIdTime, MessageIdTime, Body, ContentTypeId, u2.UserUri AS [from], u1.UserUri AS [to] FROM Messages, `
	Users u2, Users u1 WHERE Messages.FromId = u2.UserId AND Messages.ToId = u1.UserId AND u1.UserUri = '$User1' `
	AND LcsLog.dbo.Messages.Toast IS NULL `
)AS dConversation

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.

#Convert XML to HTML
$sourceXML = [xml](Get-Content $LocalPath)
Remove-Item -path IM.html -ea SilentlyContinue
pwd | % {[string]$LocalPath = $_.path}
$LocalPath = $LocalPath + "\IM.html"
Add-Content -path $LocalPath -Value '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"'
Add-Content -Path $LocalPath -Value '  "http://www.w3.org/TR/html4/loose.dtd">'
Add-Content -Path $LocalPath -Value "<html>"
Add-Content -Path $LocalPath -Value "<head>"
Add-Content -Path $LocalPath -Value "<title>IM Conversation Archive</title>"
Add-Content -Path $LocalPath -Value "</head>"
Add-Content -Path $LocalPath -Value "<body>"

Add-Content -Path $LocalPath -Value '<font size="2" face="Verdana">'
Add-Content -Path $LocalPath -Value "<h2 align=`"center`">$($sourceXML.IMConversation.Title)</h2>"
Add-Content -Path $LocalPath -Value "<h3 align=`"center`">$($sourceXML.IMConversation.Subtitle)</h2>"
Add-Content -Path $LocalPath -Value "</font>"
Add-Content -Path $LocalPath -Value '<table border="0" cellpadding="1" width="100%" style="FONT-SIZE:8pt;FONT-FAMILY:verdana">'
foreach($IM in $sourceXML.IMConversation.IM)
	{
	if ($IM.Rank -eq "1")
		{
		Add-Content -Path $LocalPath -Value "</tbody></table><br>"
		Add-Content -Path $LocalPath -Value '<table border="1" cellpadding="1" width="100%" style="FONT-SIZE:8pt;FONT-FAMILY:verdana">'
		Add-Content -Path $LocalPath -Value '<thead><tr bgcolor="#C0C0C0" align="center">'
		Add-Content -Path $LocalPath -Value	'<th><font color="#0000FF">Session Time (UTC):</font></th>'
		Add-Content -Path $LocalPath -Value	'<th><font color="#0000FF">Message Time (UTC):</font></th>'
		Add-Content -Path $LocalPath -Value	'<th><font color="#0000FF">From:</font></th>'
		Add-Content -Path $LocalPath -Value	'<th><font color="#0000FF">To:</font></th>'
		Add-Content -Path $LocalPath -Value '<th width="40%"><font color="#0000FF">Message:</font></th>'
		Add-Content -Path $LocalPath -Value	"</tr></thead>"
		Add-Content -Path $LocalPath -Value	"<tbody><tr>"
		Add-Content -Path $LocalPath -Value	"<td rowspan=`"100`" valign=`"top`">$($IM.SessionTime)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.DateTime)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.From)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.To)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.Body.get_FirstChild().get_Data())</td>"
		Add-Content -Path $LocalPath -Value	"</tr>"
		}
	else
		{
		Add-Content -Path $LocalPath -Value	"<tr>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.DateTime)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.From)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.To)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.Body.get_FirstChild().get_Data())</td>"
		Add-Content -Path $LocalPath -Value	"</tr>"
		}
	}

Add-Content -Path $LocalPath -Value "</table>"
Add-Content -Path $LocalPath -Value "</body>"
Add-Content -Path $LocalPath -Value "</html>"

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:

If ($start -ne '')
	{
	#Start date has been specified
	$startdate = $start
	$start = "and Messages.MessageIdTime >= '$start' "
	}
Else 
	{
	$startdate = "Oldest record in the database"
	}

If ($end -ne '')
	{
	#End date has been specified
	$enddate = $end
	$end = "and Messages.MessageIdTime <= '$end' "
	}
Else
	{
	$enddate = "Newest record in the database"
	}

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)

Leave a Reply

Your email address will not be published. Required fields are marked *

*