question

Hi Anu,

I wrote a blog post on how to integrate a Powershell script in SCOM, using CookDown's PowerShell MP. It has some detailed explainations and also screenshots:

Monitoring Active Directory User Account Expiration using SCOM and PowerShell (Step by Step Guide)
https://www.pohn.ch/monitor-active-directory-user-account-expiration-using-scom-and-powershell-step-by-step-guide/

One more example of how a Powershell script can be used to configure a monitor in SCOM is found in the video:

SCOM PowerShelll Monitoring MP
https://youtu.be/--wOzKU52JE

So, as already mentioned, the article will help you integrate your script. The interesting part is actually the script itself, which needs to make a SQL connection, run a qquery on the DB and get the results back. I can surely help you with the SQL part also. Some years ago I wrote an article on qerying SQL databases, using Powershell (in SMA) and it contains some nice example, which you can use to build your script:

Automating SQL operations with Service Management Automation (SMA) and Invoke-Sqlcmd – challenges and solutions
https://social.technet.microsoft.com/wiki/contents/articles/40091.automating-sql-operations-with-service-management-automation-sma-and-invoke-sqlcmd-challenges-and-solutions.aspx

Here an example of querying and exporting data from a sql table:

 #SQL parameters
 [string] $SQLServer= "Server1"
 [string] $SQLDatabase = "ExampleDB"
 [string] $SQLQuery= $("SELECT AssetStatus, AssetID, Model, Name, Site, ManufacturerName, Type FROM Assets WHERE AssetStatus='3' AND Type='Client' AND Category = 'Hardware'")
 [string]$CSVPath = "\\FileServer\FileShare\Export.csv"
      
 $Command = New-Object System.Data.SQLClient.SQLCommand
 $Command.Connection = $Connection
      
 $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
 $SQLConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDatabase; Integrated Security = True;"
      
 $SQLCmd = New-Object System.Data.SqlClient.SqlCommand
 $SQLCmd.CommandText = $SQLQuery
 $SQLCmd.Connection = $SQLConnection
 $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SQLAdapter.SelectCommand = $SQLCmd
 $SQLDataSet = New-Object System.Data.DataSet
 $SQLAdapter.Fill($SQLDataSet)
      
 $SQLDataSet.Tables[0] | Export-Csv -Delimiter ';' -NoTypeInformation -LiteralPath $CSVPath -Force

This should help you create your Powershell script.

---

(If the reply was helpful please don't forget to upvote and/or **accept as answe**r, thank you)

Regards
Stoyan Chalakov