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

Could you show the current XML and powershell code?

Answer 3

How do I extract is as XML?

                  ### Script  

Variables

$Query="

WITH MostRecentBackups
AS(
SELECT
database_name AS [Database],
MAX(bus.backup_finish_date) AS LastBackupTime,
CASE bus.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS Type
FROM msdb.dbo.backupset bus
WHERE bus.type <> 'F'
GROUP BY bus.database_name,bus.type
),
BackupsWithSize
AS(
SELECT mrb.*, (SELECT TOP 1 CONVERT(DECIMAL(10,4), b.backup_size/1024/1024/1024) AS backup_size FROM msdb.dbo.backupset b WHERE [Database] = b.database_name AND LastBackupTime = b.backup_finish_date) AS [Backup Size]
FROM MostRecentBackups mrb
)

SELECT
SERVERPROPERTY('ServerName') AS Instance,
d.name AS [Database],
d.state_desc AS State,
d.recovery_model_desc AS [Recovery Model],
bf.LastBackupTime AS [Last Full],
DATEDIFF(DAY,bf.LastBackupTime,GETDATE()) AS [Time Since Last Full (in Days)],
bf.[Backup Size] AS [Full Backup Size],
bd.LastBackupTime AS [Last Differential],
DATEDIFF(DAY,bd.LastBackupTime,GETDATE()) AS [Time Since Last Differential (in Days)],
bd.[Backup Size] AS [Differential Backup Size],
bt.LastBackupTime AS [Last Transaction Log],
DATEDIFF(MINUTE,bt.LastBackupTime,GETDATE()) AS [Time Since Last Transaction Log (in Minutes)],
bt.[Backup Size] AS [Transaction Log Backup Size]
FROM sys.databases d
LEFT JOIN BackupsWithSize bf ON (d.name = bf.[Database] AND (bf.Type = 'Full' OR bf.Type IS NULL))
LEFT JOIN BackupsWithSize bd ON (d.name = bd.[Database] AND (bd.Type = 'Differential' OR bd.Type IS NULL))
LEFT JOIN BackupsWithSize bt ON (d.name = bt.[Database] AND (bt.Type = 'Transaction Log' OR bt.Type IS NULL))

"  

Const

Main

If(Get-ChildItem c:\temp)
{
Start-Transcript -Path "C:\temp\transcript0.txt"
}

else
{
New-Item -ItemType Directory -Name Temp -Path c:\ -Force
}

If(-not(Get-Module sqlserver))
{

Import-Module Sqlserver  

}  

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

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

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

Foreach($line in $Result)
{

$PropertyBag = $ScomAPI.CreatePropertyBag()

$DB= $Line|select Database -ExpandProperty Database

$PropertyBag.AddValue("Databases",[String]$DB)

$PropertyBag

}

Alert context is all good:

Answer 4

event id 5400 on server:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

• <System>
  <Provider Name="HealthService" />
  <EventID Qualifiers="32768">5400</EventID>
  <Level>3</Level>
  <Task>1</Task>
  <Keywords>0x80000000000000</Keywords>
  <TimeCreated SystemTime="2021-08-31T13:07:54.000000000Z" />
  <EventRecordID>1011536</EventRecordID>
  <Channel>Operations Manager</Channel>
  <Computer>xxx</Computer>
  <Security />
  </System>
• <EventData>
  <Data>MG1</Data>
  <Data>MomUIGeneratedRulec27356b5e3d2404a9c71b70f711d35d6</Data>
  <Data>xxx</Data>
  <Data>{73B3C110-2720-3977-CDD4-9D4A072FE8FC}</Data>
  <Data>C73D83E4-3C03-A27B-1C8A-F492DC698C46</Data>
  <Data />
  <Data>$Data/Property[@DeezNutz ='DB']$</Data>
  </EventData>
  </Event>