Articles in the "OCS Archiving Reporter" series
- OCS Archiving Reporter: Group by conversation, filter dates
- 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)
Nice work… a great enhancement over the original. Thank you for publishing this it has helped out a couple of folks I know
Chas
Hi Scott,
I tried the script and it errors out on me. The methmod .getData() doesn’t appear to be working. Now when i use the .get_InnerText() method, it appears to get the body text. Any idea why this might be?
————————————————————
You cannot call a method on a null-valued expression.
At C:\Get-ArchivingData-August-2009\Get-ArchivingData\newmodscript\Get-ArchivingData.ps1:217 char:24
+ $IM.Body.get_FirstChild <<<< ().get_Data()
+ CategoryInfo : InvalidOperation: (get_FirstChild:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Attempted to divide by zero.
At C:\Get-ArchivingData-August-2009\Get-ArchivingData\newmodscript\Get-ArchivingData.ps1:220 char:20
+ $jProgress = ($j / <<<< $sourceXML.IMConversation.IM.Count) * 100
+ CategoryInfo : NotSpecified: (:) [], RuntimeException
+ FullyQualifiedErrorId : RuntimeException
Write-Progress : Cannot validate argument on parameter 'PercentComplete'. The argument is null, empty, or an element of the argument collection co
ntains a null value. Supply a collection that does not contain any null values and then try the command again.
At C:\Get-ArchivingData-August-2009\Get-ArchivingData\newmodscript\Get-ArchivingData.ps1:221 char:77
+ Write-Progress -Activity "Phase 2: Converting XML to HTML" -PercentComplete <<<< $jProgress -Status "Progress:" -CurrentOperation "$j of $(
$sourceXML.IMConversation.IM.Count) messages completed."
+ CategoryInfo : InvalidData: (:) [Write-Progress], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.WriteProgressCommand
IM.html created in local directory
We’re getting a similar thing to Kernel. Was working fine a couple of days ago, now we’re getting:
You cannot call a method on a null-valued expression.
At C:\Users\ben_b\Desktop\Get-ArchivingData.ps1:217 char:24
+ $IM.Body.get_FirstChild <<<< ().get_Data()
+ CategoryInfo : InvalidOperation: (get_FirstChild:String) [], Ru
ntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Attempted to divide by zero.
At C:\Users\ben_b\Desktop\Get-ArchivingData.ps1:220 char:20
+ $jProgress = ($j / <<<< $sourceXML.IMConversation.IM.Count) * 100
+ CategoryInfo : NotSpecified: (:) [], RuntimeException
+ FullyQualifiedErrorId : RuntimeException
Write-Progress : Cannot validate argument on parameter 'PercentComplete'. The a
rgument is null, empty, or an element of the argument collection contains a nul
l value. Supply a collection that does not contain any null values and then try
the command again.
At C:\Users\ben_b\Desktop\Get-ArchivingData.ps1:221 char:77
+ Write-Progress -Activity "Phase 2: Converting XML to HTML" -PercentComple
te <<<< $jProgress -Status "Progress:" -CurrentOperation "$j of $($sourceXML.I
MConversation.IM.Count) messages completed."
+ CategoryInfo : InvalidData: (:) [Write-Progress], ParameterBind
ingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.Power
Shell.Commands.WriteProgressCommand
Having the same problem. It appears that the query for the SQL database isn’t returning any results and that’s why it’s failing. I’ve verified the converstations exist by using an older version of this script. How do we fix this?
Hey guys, try changing lines 62, 66, 78, and 82 of the SQL queries where the toast messages are being filtered out to say ‘and LcsLog.dbo.Messages.Toast 1’ as opposed to ‘and LcsLog.dbo.Messages.Toast Is Null’ — without the apostrophes, of course.
The toast column is a bit value column that also accepts nulls. However, OCS 2007 R2 appears to log them as bit values only. This is why the queries were (correctly) coming back with no data.
PS
I’m no DBA, so forgive me if I my terminology is incorrect.
Guh. Looks like my code got mangled by the site removing HTML.
The correction to the query should be such that LcsLog.dbo.Messages.Toast DOES NOT EQUAL 1. In SQL this is a “less than angle bracket” and a “greater than angle bracket” with no space between.
Or you could change the query to LcsLog.dbo.Messages.Toast = 0, which would probably be better and more precise, in hidsight. Either way. Sorry for the confusion.
Thanks for the info. I have an R2 infrastructure where I work, but no archiving database to query against, so it has been difficult to test the issues that others have had since my post.