ok thanks. The Powershell monitoring MP looks good. Any chance someone can provide a powershell script to connect to sql and pull out the error messages from the table ?
Used the below script and then created a SCOM alert rule based on powershell script. I do not get any data back with details of error message. any ideas ?
$ScomAPI = New-Object -comObject “MOM.ScriptAPI”
$propertybag = “”
$PropertyBag = $ScomAPI.CreatePropertyBag()
$errorhash = “”
$errorhash = Invoke-Sqlcmd -Query “select top 1 * from [dbo].[ACTIVITY_TRACE] where Status = ‘failed’ order by ID desc” -ServerInstance “SCOMServer” -Database “PDS”
I do get an alert in SCOM but the description part of it is blank. Have used $Data/Context/DataItem/Property[@Name=‘MessageText’]$ and also tried $Data/Context/Property[@Name=‘MessageText’]$.
I know my sql command works as I have tested this on my scom server where sql is also installed.
Do you get any alert at all? Does the account that runs the powershellscript have access to the database?
On the alerting tab have you included the following line:
$Data/Context/DataItem/Property[@Name=‘MessageText’]$
Run the powershellscript as your user and see if you get a result back.