How to Query the Service Manager CMDB with PowerShell

Author by Christopher Mank

When it comes to the task of automation within Service Manager, you're not without options.  You can use the SCSM built-in Cmdlets (found here), the SCSM Orchestrator Integration Pack (found here), and the SCSM SMLets (found here).  As you start to work with each one, you begin to discover where one excels over another in different scenarios. Although these options are great, we've left out my personal favorite, the actual database!  Many times I've found myself needing to pull some very specific or complex data sets and when trying to run it through one of the Cmdlet options, they just can't compare in speed and performance to a direct SQL query.  But how can we query the CMDB from a PowerShell script?  I have a sample script below that will show you how to do just that. NOTE:  This option assumes you are querying directly against your PROD CMDB database.  Be VERY careful to ensure your query does not have any negative impact on the performance of the system.  Alternatively, you could use this option to query the Data Warehouse. QueryCmdb Image Green Section In this code block, we declare a function that does the querying for us.  It takes in one argument, which is the actual SQL query.  The function then connects to the CMDB, runs the query and fills a Data Set with the results.  Then on line 21, we return the values back to the code block that called it.  In my example, we are only returning the first column from the first row.  You can configure this to return the data in whichever way you require (e.g. multiple rows, multiple columns, etc.). Green Section Image Pink Section In this code block, we call the QueryCmdb function from the previous section.  Here we are passing a query and setting the results to the $strQueryResult variable.  In my example, we are finding the RelationshipId between two specific objects in the CMDB. Pink Section Image Blue Section In this code block, we assign the query results to a variable called $strRelationshipId.  You will notice that we include [1] after the variable.  Since we have the potential to pass more than one row, we can indicate here which row we are referring to.  This example query is only passing one column and one row, but you could customize this to pass much more. Blue Section Image Here is the script in "Copy/Paste" mode: # Declare Functions function QueryCmdb { $SqlQuery = $args[0] $SqlConn = New-Object System.Data.SqlClient.SqlConnection $SqlConn.ConnectionString = "Data Source=CmdbServerNameHere;Initial Catalog=ServiceManager;Integrated Security=SSPI" $SqlConn.Open() $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.Connection = $SqlConn $SqlCmd.CommandText = $SqlQuery $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $SqlDataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($SqlDataSet) $SqlConn.Close() Return $SqlDataSet.Tables[0].Rows[0][0] } $strQueryResult = QueryCmdb "SELECT R.RelationshipId FROM Relationship R WHERE R.SourceEntityId = 'CE974188-DCDB-F014-D459-3EC6D307231C' AND R.TargetEntityId = '2ED5ABBB-C0B8-7622-DAB6-7D792C212F54'" $strRelationshipId = $strQueryResult[1] And that's it!  You can now use the data you've gathered directly from the CMDB to do whatever it is you require. Until the Whole World Hears, Christopher

Author

Christopher Mank

Systems Architect