Custom SQL Management Pack Invoke-SqlCmd Exception

Posted by : on

Category : troubleshooting   guides   operationsManager


Introduction

The customer noticed the issue was occurring inside of one of their custom Management Packs (MP) for SCOM SQL Blocking monitoring. They deployed this custom MP to two SCOM Management Groups. The Agents where we were seeing the issue were dual (multi) homed SCOM Agents that were SQL Servers (due to the way the MP was designed).

Script Section

The command inside of the custom Management Pack was Invoke-SqlCmd, which is apart of the built in SQL Powershell Cmdlets:

...
$conn = "MSSQL-2019"
$ScriptName = "Custom.SQLBlocking.Timed.Monitor.DataSource.ps1"
[int]$WaitTimeMS = $WaitTime * 1000
$blocked_session = $null
$sql1 = "use master
                  SELECT r.session_id,
                  r.blocking_session_id,
                  s.login_name,
                  s.login_time,
                  s.program_name,
                  s.host_name,
                  s.memory_usage as Memory,
                  DB_NAME(r.database_id) AS DatabaseName,
                  r.wait_time,
                  r.command,
                  r.status,
                  r.cpu_time,
                  t.text as Query_Text
                  FROM sys.dm_exec_requests r
                  CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
                  INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
                  WHERE r.blocking_session_id <> 0 and wait_time > $WaitTimeMS"
          
$error.Clear
Try {
$blocked_session = Invoke-Sqlcmd -Query $sql1  -ServerInstance $conn
}
Catch {$momapi.LogScriptEvent($ScriptName, 9994, 0, $error)}
...

Invoke-SqlCmd in the above context should be able to successfully run with Local System (or a User Account) as the Action Account.

Catching the error

We noticed the following error after the script above ran as a part of the Management Pack:

Custom.SQLBlocking.Timed.Monitor.DataSource.ps1 : ManagedBatchParser.ParserException
    at ManagedBatchParser.Parser.Parse()
    at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)

Resolution

You will need to set the time intervals between the rules / monitors to run at a different interval than the other Management Group that is reporting to this same Agent / Group to cause it to be multi-homed.

OR

You can remove the other Management Group and allow only 1 Management Server in the Agent Management control panel.

Page Views


Share on:
About Blake Drumm
Blake Drumm

I like to collaborate and work on projects. My skills with Powershell allow me to quickly develop automated solutions to suite my customers, and my own needs.

Email : [email protected]

Website : https://blakedrumm.com

About Blake Drumm

This is the personal technical blog for Blake Drumm. Currently primarily focused towards Microsoft System Center Enterprise Management Products. I am an Microsoft Support Engineer on the System Center North America Support Team. I am new to blogging. July 6th, 2021 marks my first day as a Microsoft FTE converting from a contractor. I had been working as a contractor since January 2020. Continue to check back for new posts or tips. I like to continually update this page as time permits.

Follow @blakedrumm
Useful Links