So how do I know what my xpath query should be to pull data from an event into a SCOM alert?

I've been developing a rule to monitor SQL Server events from System Center Operation Manger 2007 (SCOM) and have been having great difficulty finding the appropriate xpath query to the data. The xpath query is often different for each datasource an alert is created for and documentation is very difficult to find. The most helpful source of answers to questions for me has been the SCOM newsgroups at microsoft (microsoft.public.opsmgr.authoring).

I recently asked a question about the propery xpath query for information from a WMI Event-based rule and a very helpful person, Mike Eisenstein, pointed me the right direction. I wanted a way to determine this structure myself without having to resort to the newsgroup every time I turned around. I decided to create a tool to capture alerts and write the alert context to a file for reference.

I chose to write my tool in PowerShell and created it as an Output Connector to SCOM. Now this version is a bit of a hack, but it works for me so far :)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.EnterpriseManagement")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.EnterpriseManagement.ConnectorFramework")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.EnterpriseManagement.Common")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.EnterpriseManagement.Monitoring")
[System.Reflection.Assembly]::LoadWithPartialName("System.Threading")

$mg = new-object Microsoft.EnterpriseManagement.ManagementGroup "yourRMSHere"
$cfAdmin = $mg.GetConnectorFrameworkAdministration()
$guid = New-Object System.Guid "{01386f22-fbec-4c85-9ce5-da706786e69e}"

$info = New-Object Microsoft.EnterpriseManagement.ConnectorFramework.ConnectorInfo
$info.Description = "Test Connector"
$info.DisplayName = "Test Connector"
$info.Name = "Test Connector"
$connector = $cfAdmin.Setup($info,$guid)
$connector.Initialize()

#
# Subscribe to the Custom Category
$any = [Microsoft.EnterpriseManagement.Administration.AlertSubscriptionConfigurationType]::Any
$config = New-Object Microsoft.EnterpriseManagement.Administration.AlertChangedSubscriptionConfiguration($any)
$config.ExpirationStartTime = [DateTime]::Now
$config.Criteria = '<And>
<Expression>
<SimpleExpression>
<ValueExpression>
<Property>Severity</Property>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value>2</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<Or>
<Expression>
<SimpleExpression>
<ValueExpression>
<Property>Priority</Property>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value>2</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<Property>Priority</Property>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value>1</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</Or>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<Property>Category</Property>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value>Custom</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<Or>
<Expression>
<SimpleExpression>
<ValueExpression>
<Property>ResolutionState</Property>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value>0</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<Property>ResolutionState</Property>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value>255</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</Or>
</Expression>
</And>'
$subscription = New-Object Microsoft.EnterpriseManagement.ConnectorFramework.MonitoringConnectorSubscription 'AlertSubscription',$connector.Id, $config
$subscription.DisplayName = 'Alerts Subscription'
$subscription.Description = 'Subscription that only gathers alerts from the Custom Category'
trap {
Write-output $("TRAPPED-1: " + $_.Exception.GetType().FullName);
Write-output $("TRAPPED-1: " + $_.Exception.Message);

}
$cfAdmin.InsertConnectorSubscription($subscription)

$one = 1
$fileCounter = 0
while ($one -eq 1) { # yup, loop forever
$alerts = $connector.GetMonitoringAlerts()
if ($alerts.Count -gt 0) {
$connector.AcknowledgeMonitoringAlerts($alerts)
}

foreach ($alert in $alerts) {
[xml]$context = $alert.Context
$context.Save("c:\temp\Alert_Context_" + $fileCounter + ".xml")
$fileCounter = $fileCounter + 1
}

# Sleep for a minute
[System.Threading.Thread]::Sleep(60000)
}

Modify yourRMSHere to a value appropriate to your install. Note that the script doesn't volunteer to terminate and does not clean up after itself, yet, in SCOM. To clean up a few things need to be done:

$connector.Uninitialize()
In your Scom console, delete the subscription from the Product Connector
$cfAdmin.Cleanup($connector)

Note: This is not for the faint at heart, but you probably wouldn't be be here if you are.

I'll address the analysis of the resultant file after I am more certain I have the interpretation down myself. I'm currently working with a sample-size of a single rule.