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?



