Although directly editing the SCOM Databases isn’t something that Microsoft really support, we all know that accessing the SCOM Databases directly can be easier than mucking around in the console or trying to use or find a suitable PowerShell cmdlet. As much as I love using PowerShell wherever possible, I know that there are times that I just go to SQL Management Studio to directly query the OperationsManager Database because it can often be a lot quicker to get at the desired information.
So if you, like me would rather use PowerShell wherever possible, then ditch the SQL Management Studio and use PowerShell to query the database instead.
(Now even though this exact script will allow you to query SCOM’s database, this is really a very ‘generic script’ that will work on pretty much any SQL database. Just replace the Orange SQL code with your own SQL and don’t forget to change the Database info on the second line to match your own SQL Server details).
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server=scom-db01;Database=OperationsManager;Integrated Security=True” # Change this line to match your own SQL Server
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = ‘SELECT AlertStringName, AlertStringDescription, AlertParams, Name, SUM(1) AS
AlertCount, SUM(RepeatCount+1) AS AlertCountWithRepeatCount
FROM Alertview WITH (NOLOCK)
group BY AlertStringName, AlertStringDescription, AlertParams, Name
ORDER BY AlertCount DESC‘
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
So let’s dissect this script so you understand what it is doing.
We create a new connection object and point it to my SQL Server called “scom-db01”. The database I want to query is the “OperationsManager” database. I’ll be using integrated security, meaning my user account I am logged on with right now. So that account will need permission to this database.
We create a new command object and then enter in a command that we want to execute on the SQL Server. This is basically the equivalent of the Query window in the SQL Management Studio. So in my script, I’ve used a select statement that will find the Most common Alerts in Operations Manager and list them by Alert Count. So if you have a different select statement in mind, just paste that statement in here between the quotes.
We create the connection to the Database and run the command (execute the select statement).
We return the results and close the connection to the Database Server.
Obviously this is a basic script that only connects to the database and runs a quick select statement and returns the results. Of course this could easily be expanded to update the database as well if needed, the only limit is your imagination and your ability to write some nifty SQL Statements.
I’d be interested in hearing from any of you guys that have some cool Operations Manager SQL Statements you use and I’d be happy to post them here for others to share and learn from.