Yes, you can, but you will need to get your powershell script to retrieve the information from the database first to populate the variable:
Here's a quick example:
$SQLServer = "<your DB server>"
$SQLDBName = "<your DB>"
$pwd = "<password>"
$nodename = hostname #This will work if the hostname is the same in Orion, else hard code
$SqlQuery = "SELECT Application.DBName FROM Nodes WHERE Caption = $nodename" #Guessing that Application.DBName is a custom property of the Nodes table?
#Connect, query and close
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$var1 = New-Object System.Data.DataSet
I configured a read only user which I use for such things, but there are plenty of other ways to encrypt your user creds so they are not in plain text.
You can also use the SwisSnapin (from the Orion SDK) if you'd prefer.
Thank you for your reply yaquaholic
"but you will need to get your powershell script to retrieve the information from the database first to populate the variable:" This is not exactly what I want as the DBName is the custom property I have created which will have one/more strings. (DB names but not from Nodes table.)
A custom SQL (say SQL1) will fetch list of these names and I have to apply same monitor (Windows PowerShell) to each of these DBNames. To be more specific another custom SQL (say SQL2) will be run for each of the DBNames returned by the SQL1.
What I am trying to do is, instead of running SQL1 from the monitor, get it's result for each node and add those (one or more) DBNames in the custom property. And finally run SQL2 for each DBName from monitor, so we can monitor each DBName individually.
My bad for not providing these details in original post.
1 of 1 people found this helpful
300 Have you seen Alex's post - How do you reference an application custom property in an alert.
Quite a few of us have struggled to get these CP variables to work, and have ended up creating SQL queries to solve the problem.
Finally, this worked for me:
Just so that if someone comes to this thread for same issue.