[Powershell] Get SCOM Agents using a SQL Query

Today, I'll add a new powershell script that I had to use for a developped MP. Retieving SCOM agents by using the SCOM DB instead of Get-Agent cmdlet.
The aim was to have a very fast answer of the script and using the Get-Agent cmdlet was really too long.

1. Function GetSCOMAgentsInSQL ($DBServer, $DBCatalog)
2. {
3. $SqlServer = $DBServer
4. $SqlCatalog = $DBCatalog
5. $SQLQuery =  "SELECT distinct(B.displayname)"
6. $SQLQuery += " FROM TypedManagedEntity T WITH(NOLOCK)"
7. $SQLQuery += " JOIN BaseManagedEntity B WITH(NOLOCK)    ON B.BaseManagedEntityId = T.BaseManagedEntityId"
8. $SQLQuery += " WHERE T.ManagedTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterAgent()"
9. $SQLQuery += " order by 1"
10. $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
11. $sqlConnection.ConnectionString = "Server = $sqlserver; Database = $sqlcatalog; Integrated Security = True"
12. $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
13. $SqlCmd.CommandText = $SqlQuery
14. $SqlCmd.Connection = $SqlConnection
15. $SQlCmd.CommandTimeout = 120
16. $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
17. $SqlAdapter.SelectCommand = $SqlCmd
18. $DSet = New-Object System.Data.DataSet
19. $SqlAdapter.Fill($DSet) | out-null
20. $SqlConnection.Close()
21. $DSet.Tables[0]
22. }

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. $AgentList = GetSCOMAgentsInSQL $SCOMServer $SCOMCatalog

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

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