[Powershell] Get SCOM Groups members using a SQL Query

Here is a new powershell script that I had to use to develop MPs. Retieving SCOM Groups members (displayName in the exemple)by using the SCOM DB.

1. function GetSCOMGroupMembers ($SCOMGrp, $DBServer, $DBCatalog)
2. {
3. $SqlServer = $DBServer
4. $SqlCatalog = $DBCatalog
5. $SCOMGroup = $SCOMGrp
6. $SQLQuery = "select DisplayName from RelationShip r with (nolock)
7. inner join basemanagedentity bme  with (nolock) on bme.BaseManagedEntityId = R.TargetEntityId
8. Where SourceEntityId in (
9. select BaseManagedEntityId from basemanagedentity with (nolock)
10. where DisplayName LIKE '%$SCOMGroup%')"
11. $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
12. $sqlConnection.ConnectionString = "Server = $sqlserver; Database = $sqlcatalog; Integrated Security = True"
13. $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
14. $SqlCmd.CommandText = $SQLQuery
15. $SqlCmd.Connection = $SqlConnection
16. $SQlCmd.CommandTimeout = 120
17. $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
18. $SqlAdapter.SelectCommand = $SqlCmd
19. $DSet = New-Object System.Data.DataSet
20. $SqlAdapter.Fill($DSet) | out-null
21. $SqlConnection.Close()
22. $DSet.Tables[0]
23. }

How to use it :

1. # --------- Read the SCOMDB Server and Catalog
2. $SCOMServer = "MyServer"
3. $SCOMCatalog = "OperationsManager" 
4. # --------- Variable initialization
5. $SCOMGrpName = "MyGroupName" 
6.  # --------- Read SCOM group and get member count
7. $GroupMembersList = GetScomGroupMembers $SCOMGrpName $SCOMServer $SCOMCatalog

The script wil return a table $GroupMembersList that can also be re-used.

This posting is provided "AS IS" with no warranties.