[Powershell] Get SCOM Groups using a SQL Query

Today, I'll add a new powershell script that I had to use for a developped MP. Retieving SCOM Groups by using the SCOM DB.

1. Function GetSCOMGroupsInSQL ($DBServer, $DBCatalog)
2. {
3. $SqlServer = $DBServer
4. $SqlCatalog = $DBCatalog
5. $SQLQuery = "select distinct SCOMGroup = convert(varchar(100), upper(rtrim(ltrim(BME.Displayname))))"
6. $SQLQuery += " from BaseManagedEntity BME"
7. $SQLQuery += " left join dbo.ManagedTypeView T1 on (BME.BaseManagedEntityId = T1.Id)"
8. $SQLQuery += " Left join dbo.Relationship R1 on(BME.BaseManagedEntityId=R1.Sourceentityid)"
9. $SQLQuery += " left join BaseManagedEntity ME2 on (ME2.BaseManagedEntityId=R1.targetentityid)"
10. $SQLQuery += " left join dbo.ManagedTypeView T2 on(ME2.BaseManagedEntityId=T2.id)"
11. $SQLQuery += " where (T1.Name like 'UINameSpace%.Group' or T1.Name like '%MySpecificGroupName%')"
12. $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
13. $sqlConnection.ConnectionString = "Server = $sqlserver; Database = $sqlcatalog; Integrated Security = True"
14. $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
15. $SqlCmd.CommandText = $SqlQuery
16. $SqlCmd.Connection = $SqlConnection
17. $SQlCmd.CommandTimeout = 120
18. $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
19. $SqlAdapter.SelectCommand = $SqlCmd
20. $DSet = New-Object System.Data.DataSet
21. $SqlAdapter.Fill($DSet) | out-null
22. $SqlConnection.Close()
23. $DSet.Tables[0]
24. }

How to use it :

1. # --------- Read the SCOMDB Server and Catalog
2. $SCOMServer = $Config.Groups.SCOMDB.SCOMDBServerName
3. $SCOMCatalog = $Config.Groups.SCOMDB.SCOMDBName
4. # --------- Query SCOM DB to get Agent list and Group List
5. $GroupList = GetSCOMGroupsInSQL $SCOMServer $SCOMCatalog

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

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