[OpsMgr 2007R2] Get member of all SCOM groups and export result in CSV files - Powershell script

Here is a script that will help you to create a CSV file per SCOM group. Each CSV file will have the name of the SCOM group and will contain this information

• Name
• Path
• DisplayName
• FullName
• IsManaged
• LastModified
• HealthState
• StateLastModified
• IsAvailable
• AvailabilityLastModified
• InMaintenanceMode
• MaintenanceModeLastModified
• MonitoringClassIds
• LeastDerivedNonAbstractMonitoringClassId
• Id
• ManagementGroup
• ManagementGroupId

Empty group does not create a CSV file.

1. function GetDisplayName($object){
2.      $displayName = [System.String]::Empty
3.      if(($object.DisplayName -eq $null) -or ($object.DisplayName.Length -eq 0)){
4.            $displayName = $object.Name;
5.      }
6.      else {
7.            $displayName = $object.DisplayName;
8.      }
9.      $displayName;
10. }
12. $mg = (Get-ManagementGroupConnection).ManagementGroup
13. $groups = $mg.GetRootPartialMonitoringObjectGroups() | sort DisplayName
14. foreach($group in $groups) {
15.      Write-Host
16.      Write-Host $group.DisplayName
17.      $groupMembers = $group.GetRelatedPartialMonitoringObjects([Microsoft.EnterpriseManagement.Common.TraversalDepth]::OneLevel);
18.      if($groupMembers.Count -eq 0) {
19.            Write-Host "The group is empty"
20.      }
21.      else {
22.             $groupMembers | Select-Object DisplayName,Path,@{name="Type";expression={foreach-object {GetDisplayName $_.GetLeastDerivedNonAbstractMonitoringClass()}}} | sort DisplayName | ft
23.             $FileName = $group.DisplayName
24.             $FileName += ".csv"
25.             $OutPath = $FileName
26.             $groupMembers | Export-Csv -Path $OutPath -NoTypeInformation
27.      }
28. Write-Host
29. }

You can download the file >>>>>>>>>> Here <<<<<<<<<<

How to use it :
- First connect to you management group

1. add-pssnapin "Microsoft.EnterpriseManagement.OperationsManager.Client";
2. set-location "OperationsManagerMonitoring::";
3. new-managementGroupConnection -ConnectionString:MyRMS.MyDomain -Credential (get-credential "Domain\Account");

 - secondly, set your location where you want to store the created CVS files :

1. set-location c:\temp

- Then execute the script :

Result will be :

 And you will retrieve your files in the location you have set - in my case :

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

[SQL] Get SCOM Groups members using a SQL Query on OperationsManager DB

Here is a short query that is very usefull to list all member of a SCOM group. Use it on your OperationsManager DB :

1. select DisplayName from RelationShip r with (nolock)
2. inner join basemanagedentity bme with (nolock) on bme.BaseManagedEntityId = R.TargetEntityId
3. Where SourceEntityId in (
4.             select BaseManagedEntityId from basemanagedentity with (nolock)
5.             where DisplayName LIKE '%GroupName%')
6. Order by DisplayName

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

[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.