SQL query with several databases as output using a monitor

Michael 81 Reputation points
2021-08-27T04:59:04.033+00:00

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?

Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,435 questions
Microsoft System Center
Microsoft System Center
A suite of Microsoft systems management products that offer solutions for managing datacenter resources, private clouds, and client devices.
883 questions
0 comments No comments
{count} votes

Accepted answer
  1. CyrAz 5,181 Reputation points
    2021-08-31T18:19:31.997+00:00

    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.


7 additional answers

Sort by: Most helpful
  1. AlexZhu-MSFT 5,551 Reputation points Microsoft Vendor
    2021-08-27T07:09:06.67+00:00

    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
    126998-scom-powershell-sql.png

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


  2. AlexZhu-MSFT 5,551 Reputation points Microsoft Vendor
    2021-08-31T00:47:41.47+00:00

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

    127713-image.png

    0 comments No comments

  3. Michael 81 Reputation points
    2021-08-31T07:48:13.787+00:00

    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?

    0 comments No comments

  4. CyrAz 5,181 Reputation points
    2021-08-31T11:54:32.097+00:00

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