question

Michael-4753 avatar image
0 Votes"
Michael-4753 asked AlexZhu-MSFT commented

SQL query with several databases as output using a monitor

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?



msc-operations-managermsc-essentials
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CyrAz avatar image
0 Votes"
CyrAz answered AlexZhu-MSFT commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

you were right. thx

0 Votes 0 ·

Hi,

So, the problem is solved after we made the following changes, right?
Databases: $Data/Property[@Name='Databases']$

Glad to see the progress and the problem is solved finally. CyrAz's solid technical skills are really impressive. As always, if you have any questions in future, we warmly welcome you to post in this partner forum again.


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

0 Votes 0 ·
AlexZhu-MSFT avatar image
0 Votes"
AlexZhu-MSFT answered Michael-4753 commented

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Alex

The script works fine when I do only one loop:

127102-image.png



.. but when I do more than one loop scom does not receive data and create an alert.

Is there something to be done to fill out $propertybag variables and sent them to scom in the loop?

0 Votes 0 ·
image.png (7.7 KiB)
AlexZhu-MSFT avatar image
0 Votes"
AlexZhu-MSFT answered AlexZhu-MSFT edited

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

127713-image.png



image.png (54.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Michael-4753 avatar image
0 Votes"
Michael-4753 answered

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[@Name='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?


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CyrAz avatar image
0 Votes"
CyrAz answered Michael-4753 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have changed value so they match. I have even written value to eventviewer and its all good. Name of db if this example is: msdb

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

  • <System>
    <Provider Name="Health Service Script" />
    <EventID Qualifiers="0">9999</EventID>
    <Level>3</Level>
    <Task>0</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2021-08-31T06:49:44.000000000Z" />
    <EventRecordID>1010437</EventRecordID>
    <Channel>Operations Manager</Channel>
    <Computer></Computer>
    <Security />
    </System>

  • <EventData>
    <Data>test</Data>
    <Data>msdb</Data>
    </EventData>
    </Event>

0 Votes 0 ·
CyrAz avatar image
0 Votes"
CyrAz answered

Could you show the current XML and powershell code?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Michael-4753 avatar image
0 Votes"
Michael-4753 answered

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:

127963-image.png









image.png (19.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Michael-4753 avatar image
0 Votes"
Michael-4753 answered

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[@Name='DB']$</Data>
</EventData>
</Event>

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.