SQL query with several databases as output using a monitor

Question

I need to obtain information regarding instance name, database size etc. I have created a monitor that executes a powershell script.

The result of the query is good, but i can only get it to work when I select first row of queryresult

$Query=""

$ScomAPI = New-Object -comObject "MOM.ScriptAPI"

$PropertyBag = $ScomAPI.CreatePropertyBag()

$Instances=$env:COMPUTERNAME| Foreach-Object {Get-ChildItem -Path "SQLSERVER:\SQL\$_"}

$Result=Invoke-Sqlcmd -Query $Query -ServerInstance $Instances

$Result=$result | select -first 1

Foreach($line in $Result)
{

$Instance= $Line|select Instance -ExpandProperty Instance

$DB=$line| select Database -ExpandProperty Database

$State=$Line| select State -ExpandProperty State

$LastFull=$Line| select LastFull -ExpandProperty "Last full"

$LastFullInDays=$Line|select 'Time Since Last Full (in Days)' -ExpandProperty 'Time Since Last Full (in Days)'

$PropertyBag.AddValue("FullBackup","Error")

Start-Sleep -Seconds 4

$PropertyBag.AddValue("Instance",$Instance)

$PropertyBag.AddValue("Database",$DB)

$PropertyBag.AddValue("State",$State)

$PropertyBag.AddValue("LastFull",$LastFull)

$propertybag

}

Output is, correctly:

Date and Time: 8/26/2021 2:41:18 PM
Property Name Property Value
FullBackup Error
Instance xxx\xxx
Database xx
State ONLINE
LastFull 08/25/2021 22:16:55

When I change $Result=$result | select -first 2 propertybag will be fill with two rows nothing happens.

Is there a way to fill up $propertybag with result from a loop?

Answer 1

Soo... if I'm reading this correctly, now the property in the propertybag is called "Databases", but you try to reference it by using "DB" in the alert. So still not good.

Answer 2

Hi,

It seems this may relate to the powershell script and the real environment and we are unable to reproduce it in the lab environment. Just share some thoughts: I've done a quick test in my lab (see below). To narrow down the problem, it seems we may add some lines to output the variables to, for example, a SMB file share, so that we can know what happened in each step, for further debugging.

Import-Module "sqlps" -DisableNameChecking  
  
$Query="use OperationsManager;  
select top 5 sum(1) as alertcount, alertstringname, [name], Severity, ticketid   
from alertview with (nolock)  
where severity = '2' and timeraised > dateadd (day, -7, getdate())  
group by alertstringname, [name], severity, ticketid  
order by alertcount desc"  
  
#$ScomAPI = New-Object -comObject "MOM.ScriptAPI"  
#$PropertyBag = $ScomAPI.CreatePropertyBag()  
$logFile = "\\10.1.1.2\d\alex\08.27.txt"  
$Instances=$env:COMPUTERNAME| Foreach-Object {Get-ChildItem -Path "SQLSERVER:\SQL\$_"}  
$Instances=$Instances | select -first 1  
foreach($instance in $Instances)  
{  
	$Result=Invoke-Sqlcmd -Query $Query -ServerInstance $instance  
	#$Result=$Result | select -first 1  
	Foreach($line in $Result)  
	{  
		$alertcount = $line.alertcount  
		$alertcount  
		$alertcount | Out-File $logFile -Append  
		  
		  
		#$Instance= $Line|select Instance -ExpandProperty Instance  
		#$DB=$line| select Database -ExpandProperty Database  
		#$State=$Line| select State -ExpandProperty State  
		#$LastFull=$Line| select LastFull -ExpandProperty "Last full"  
		#$LastFullInDays=$Line|select 'Time Since Last Full (in Days)' -ExpandProperty 'Time Since Last Full (in Days)'  
  
		#$PropertyBag.AddValue("FullBackup","Error")  
		  
		#$PropertyBag.AddValue("AlertCount",$alertcount)  
		Start-Sleep -Seconds 4  
		  
		#$PropertyBag.AddValue("Instance",$Instance)  
		#$PropertyBag.AddValue("Database",$DB)  
		#$PropertyBag.AddValue("State",$State)  
		#$PropertyBag.AddValue("LastFull",$LastFull)  
		#$propertybag  
	}  
}  
  

lab screenshot

Alex
If the response is helpful, please click "Accept Answer" and upvote it.

Answer 3

for some reason, I am unable to reply this post, I'd like to post it as picture below

Answer 4

Hello Alex

I created a rule instead of a monitor and made it work.

But I have problems with one variable that does not get send to alert description:

Failed to replace parameter while creating the alert.
Alert:
Workflow: MomUIGeneratedRulec27356b5e3d2404a9c71b70f711d35d6
Instance:
Instance ID: {73B3C110-2720-3977-CDD4-9D4A072FE8FC}
Management Group: MG1
Failing replacement: $Data/Property[@DeezNutz ='DBName']$

Alert context is fine:

Dato og klokkeslæt: 31-08-2021 09:42:54
Egenskabsnavn Egenskabsværdi
FullBackup Error
Instance xx\OLDAX
Database model
State ONLINE
LastFull 08/30/2021 22:17:11
LastFullInDays 1

Script:

$DBName= $Line|select Database -ExpandProperty Database

$PropertyBag.AddValue("Database",[String]$DBName)

Can you help?

Answer 5

According to your powershell snippet, the name of that property in the propertybags is "Database", not "DBName"
$PropertyBag.AddValue("Database",[String]$DBName)