Public/Get-SCOMDWPerformanceDataSignature.ps1
|
Function Get-SCOMDWPerformanceDataSignature { <# .SYNOPSIS Retrieves performance counter signatures and metadata from the SCOM Data Warehouse for a specified computer or monitoring object. .DESCRIPTION The Get-SCOMDWPerformanceDataSignature function queries the SCOM (System Center Operations Manager) Data Warehouse to retrieve detailed information about performance counters that are being collected for a specific computer or monitoring object. This includes counter names, object names, instance names, collection rules, and sample counts. The function is particularly useful for: - Discovering what performance counters are available for a specific system - Auditing performance collection rules and their associated metadata - Troubleshooting performance data collection issues - Planning performance monitoring strategies - Identifying the internal rule GUIDs for specific performance counters The function automatically discovers related monitoring objects recursively, ensuring that performance data from all related components (services, processes, disk drives, etc.) are included in the results. .PARAMETER DataWarehouseServerName Specifies the name of the SCOM Data Warehouse server. If not provided, the function will attempt to auto-discover the server name from the registry or SCOM PowerShell cmdlets. Type: String Required: False Default: Auto-discovered from SCOM configuration .PARAMETER DataWarehouseDatabaseName Specifies the name of the SCOM Data Warehouse database. Type: String Required: False Default: "OperationsManagerDW" .PARAMETER QueryTimeout Specifies the time in seconds to wait for the SQL command to execute before timing out. Type: Integer Required: False Default: 30 seconds Range: 1-3600 seconds .PARAMETER ConnectionTimeout Specifies the time in seconds to wait for a database connection to open before timing out. Type: Integer Required: False Default: 15 seconds Range: 1-300 seconds .PARAMETER ComputerName Specifies the name of the computer for which to retrieve performance counter signatures. This parameter is mandatory when using the 'ByComputerName' parameter set. Type: String Required: True (when using ByComputerName parameter set) .PARAMETER Id Specifies the GUID of the monitoring object for which to retrieve performance counter signatures. This parameter is mandatory when using the 'ById' parameter set and must be a valid GUID format. Type: Object (GUID) Required: True (when using ById parameter set) Aliases: ObjectId Validation: Must be a valid GUID format .PARAMETER RuleId Specifies one or more rule GUIDs to filter the results to specific performance collection rules. When provided, only performance counters collected by the specified rule(s) will be returned. Accepts the following formats: - Single GUID as string: "12345678-1234-1234-1234-123456789012" - Multiple GUIDs as comma-separated string: "guid1,guid2,guid3" - Array of GUID strings: @("guid1", "guid2", "guid3") All GUID values must be in valid GUID format or the function will issue warnings and ignore invalid entries. Type: Object (String or Array) Required: False Default: $null (no filtering) .EXAMPLE Get-SCOMDWPerformanceDataSignature -ComputerName "SQL01.contoso.com" Description: Retrieves all performance counter signatures for the computer named "SQL01.contoso.com". This will return information about all performance counters being collected for this server, including counters from related monitoring objects like SQL Server instances, disk drives, etc. Output: Returns a collection of objects containing ManagedEntityGuid, FullName, ObjectName, CounterName, InstanceName, NumSamples, RuleName, RuleDisplayName, and RuleId for each performance counter. .EXAMPLE Get-SCOMDWPerformanceDataSignature -Id "68e53d0e-955f-1671-9dd4-ac1c0d80e8b8" Description: Retrieves performance counter signatures for the monitoring object with the specified GUID. This method is useful when you already know the exact GUID of the monitoring object and want to avoid the overhead of name resolution. Output: Returns detailed performance counter information for the specified monitoring object and all its related monitoring objects. .EXAMPLE $signatures = Get-SCOMDWPerformanceDataSignature -ComputerName "WEB01.contoso.com" -DataWarehouseServerName "SCOMDW01.contoso.com" $signatures | Where-Object {$_.ObjectName -eq "Processor"} | Format-Table ObjectName, CounterName, InstanceName, NumSamples -AutoSize Description: Retrieves performance signatures from a specific data warehouse server for WEB01, then filters and displays only processor-related performance counters in a formatted table. This demonstrates how to work with the results and filter for specific performance objects. Output: A formatted table showing processor performance counters with their names, instances, and sample counts. .EXAMPLE $cpuCounters = Get-SCOMDWPerformanceDataSignature -ComputerName "EXCH01.contoso.com" -RuleId "12345678-1234-1234-1234-123456789012" if ($cpuCounters.Count -gt 0) { Write-Host "Found $($cpuCounters.Count) performance counters for the specified rule" $cpuCounters | Select-Object RuleDisplayName, ObjectName, CounterName | Sort-Object ObjectName, CounterName } else { Write-Warning "No performance counters found for the specified rule ID" } Description: Demonstrates filtering results by a specific rule ID and handling the case where no results are found. This is useful when you want to audit what performance counters a specific collection rule is gathering. Output: Console output showing the count of found counters and a sorted list of rule display names, object names, and counter names, or a warning if no counters are found. .EXAMPLE $allServers = @("SQL01.contoso.com", "WEB01.contoso.com", "DC01.contoso.com") $allSignatures = @() foreach ($server in $allServers) { try { Write-Host "Processing $server..." $signatures = Get-SCOMDWPerformanceDataSignature -ComputerName $server -QueryTimeout 60 $signatures | ForEach-Object { $_.ComputerName = $server } $allSignatures += $signatures } catch { Write-Warning "Failed to process $server`: $($_.Exception.Message)" } } $allSignatures | Export-Csv -Path "C:\Reports\PerformanceSignatures.csv" -NoTypeInformation Description: Advanced example showing how to collect performance signatures from multiple servers and export the results to a CSV file. This demonstrates bulk processing, error handling, and report generation. The example also shows how to add additional properties to the results for tracking purposes. Output: Console progress messages and a CSV file containing performance counter signatures from all successfully processed servers. .EXAMPLE # Filter by multiple Rule IDs using array syntax $ruleIds = @("12345678-1234-1234-1234-123456789012", "87654321-4321-4321-4321-210987654321") $multiRuleCounters = Get-SCOMDWPerformanceDataSignature -ComputerName "DB01.contoso.com" -RuleId $ruleIds $multiRuleCounters | Group-Object RuleDisplayName | ForEach-Object { Write-Host "Rule: $($_.Name) - Counter Count: $($_.Count)" $_.Group | Select-Object ObjectName, CounterName | Sort-Object ObjectName, CounterName } Description: Demonstrates filtering by multiple rule IDs using array syntax. This is useful when you want to examine performance counters from several specific collection rules simultaneously. Output: Grouped output showing counters organized by rule display name with counts and detailed listings. .EXAMPLE # Filter by multiple Rule IDs using comma-separated string $signatures = Get-SCOMDWPerformanceDataSignature -ComputerName "WEB21.contoso.com" $signatures | Select-Object RuleDisplayName, ObjectName, CounterName, NumSamples | Sort-Object RuleDisplayName, ObjectName, CounterName Description: Shows how to use comma-separated string format for multiple rule IDs. This approach is convenient when you have rule IDs from external sources like CSV files or configuration strings. Output: Table output sorted by rule display name, object name, and counter name. .EXAMPLE $memoryCounters = Get-SCOMDWPerformanceDataSignature -ComputerName "APP01.contoso.com" | Where-Object {$_.ObjectName -like "*Memory*" -or $_.CounterName -like "*Memory*"} | Group-Object ObjectName | ForEach-Object { [PSCustomObject]@{ ObjectName = $_.Name CounterCount = $_.Count Counters = ($_.Group.CounterName | Sort-Object -Unique) -join ", " } } $memoryCounters | Format-Table -AutoSize Description: Complex example that retrieves performance signatures, filters for memory-related counters, groups them by object name, and creates a summary report showing how many counters exist for each memory-related performance object. This demonstrates advanced PowerShell pipeline usage. Output: A formatted table showing memory-related performance objects, the count of counters for each, and a comma-separated list of the actual counter names. .INPUTS None This function does not accept pipeline input. .OUTPUTS System.Collections.ArrayList Returns an ArrayList containing PSCustomObject instances with the following properties: - ManagedEntityGuid: The GUID of the monitoring object - FullName: The full path/name of the monitoring object - ObjectName: The performance object name (e.g., "Processor", "Memory", "LogicalDisk") - CounterName: The performance counter name (e.g., "% Processor Time", "Available MBytes") - InstanceName: The performance counter instance (e.g., "_Total", "C:", specific process names) - NumSamples: The number of performance samples collected - OldestSample: The timestamp of the oldest performance sample - NewestSample: The timestamp of the newest performance sample - RuleName: The internal system name of the collection rule - RuleDisplayName: The user-friendly display name of the collection rule - RuleId: The GUID of the performance collection rule - ManagementPackName: The name of the management pack containing the rule - ManagementPackId: The GUID of the management pack containing the rule .NOTES ==================================================================== Created with: Visual Studio Code Author: Tyson Paul Created: 2025.09.18 Last Modified: 2025.09.18 Version: 1.0 Blog: https://monitoringguys.com ==================================================================== Requirements: - System Center Operations Manager PowerShell module (OperationsManager) - Appropriate permissions to read from the SCOM Data Warehouse - Network connectivity to the SCOM Data Warehouse server - Active connection to a SCOM Management Group Performance Considerations: - Consider adjusting QueryTimeout for environments with large amounts of performance data Error Handling: - Validates GUID format for Id and RuleId parameters - Handles SQL connection and query execution errors gracefully - Provides verbose output for troubleshooting - Auto-discovery of Data Warehouse server with multiple fallback methods Authentication: - This function connects to the SCOM Data Warehouse using Windows Integrated Authentication (Trusted_Connection). SQL Server authentication (mixed mode) is not supported. - The connection runs under the identity of the current PowerShell process. If your account does not have read access to the Data Warehouse, use 'runas /netonly' to launch PowerShell under an authorized identity: runas /netonly /user:DOMAIN\DWReaderAccount powershell.exe The /netonly flag applies the alternate credential only to network resources (SQL, SCOM SDK) while keeping your local desktop session unchanged. - A PSCredential parameter is intentionally not provided because SCOM Data Warehouse instances are configured for Windows Integrated Authentication only. Related Functions: - Get-SCOMClassInstance: Retrieves SCOM monitoring objects - Get-SCOMRelatedMonitoringObjects: Gets related monitoring objects - Get-SCOMIdFromHostName: Converts computer names to SCOM object GUIDs - Verify-SCOMDWServername: Validates and discovers Data Warehouse server names .LINK https://docs.microsoft.com/en-us/powershell/module/operationsmanager/ .LINK https://monitoringguys.com .LINK https://docs.microsoft.com/en-us/system-center/scom/ #> [CmdletBinding(DefaultParameterSetName = "ByComputerName")] Param ( # Defaults to OperationsManagerDW # If not specified, it will be retrieved from the registry or SCOM settings. [Parameter(Mandatory = $false)] [string]$DataWarehouseServerName, # Defaults to OperationsManagerDW [Parameter(Mandatory = $false)] [string]$DataWarehouseDatabaseName = "OperationsManagerDW", # Defaults to 30 seconds # The time in seconds to wait for the command to execute. [Parameter(Mandatory = $false)] [int]$QueryTimeout = 30, # Defaults to 15 seconds # The time (in seconds) to wait for a connection to open. [Parameter(Mandatory = $false)] [int]$ConnectionTimeout = 15, [Parameter(Mandatory = $true, ParameterSetName = "ByComputerName")] [string]$ComputerName, [Parameter( Mandatory = $true, ParameterSetName = "ById", HelpMessage = "The GUID of the entity for which to retrieve performance counters")] [Alias('ObjectId')] [ValidateScript({ if ([Guid]::TryParse($_, [ref][Guid]::Empty)) { $true } else { throw "Invalid GUID format. Please provide a valid GUID string." } })] [Object]$Id, [Parameter(Mandatory = $false)] [object]$RuleId = $null ) Write-Verbose "Get-SCOMDWPerformanceDataSignature called with ParameterSet: $($PSCmdlet.ParameterSetName)" If ($ComputerName.Length -gt 0) { # If we are using the ComputerName parameter, we need to get the Id of the MonitoringObject try { # Only supports Microsoft.Windows.Computer class at this time. $Id = Get-SCOMIdFromHostName $ComputerName } Catch { Write-Error "Failed to get MonitoringObjectId for ComputerName '$ComputerName'. Error: $_" return $false } } Write-Verbose "Getting Performance Data signatures for MonitoringObjectId: $Id" $DataWarehouseServerName = Verify-SCOMDWServername -DataWarehouseServerName $DataWarehouseServerName -Verbose:($PSBoundParameters.ContainsKey('Verbose')) $Instance = Get-SCOMClassInstance -Id $Id Write-Verbose 'Get related monitoring objects recursively because, sadly, the standard method only returns the direct instances, not nested ones.' $allRMO = Get-SCOMRelatedMonitoringObjects -ClassInstance $Instance # Main query to get performance data $SQLCMD = @" -- GetPerfSignaturesFromDW.SQL -- Variable declaration is applicable for manual testing only; entire query can be run ad-hoc in SSMS with valid @InstanceId value hardcoded. -- The Invoke-CLSqlCmd function will pass in the parameters via the Parameters hashtable. DECLARE @InstanceId NVARCHAR(MAX); DECLARE @RuleIDFilter NVARCHAR(MAX); -- Assign defaults if not supplied, for manual SSMS testing only when params are not supplied. --IF @InstanceId IS NULL SET @InstanceId = 'ae44dcc5-5eb3-e3d9-af36-233902a6b753'; --IF @RuleIDFilter IS NULL SET @RuleIDFilter = 'f99d1c39-af5e-d285-a35f-ed9372e41208'; SELECT me.ManagedEntityGuid AS MonitoringObjectId, me.FullName, pr.ObjectName, pr.CounterName, pri.InstanceName, COUNT(*) AS NumSamples, MIN(p.[DateTime]) AS OldestSample, MAX(p.[DateTime]) AS NewestSample, R.RuleSystemName AS RuleName, R.RuleDefaultName AS RuleDisplayName, R.RuleGuid AS RuleId, mp.ManagementPackSystemName AS ManagementPackName, mp.ManagementPackVersionIndependentGuid AS ManagementPackId FROM Perf.vPerfRaw p INNER JOIN vManagedEntity me ON me.ManagedEntityRowID = p.ManagedEntityRowID INNER JOIN vPerformanceRuleInstance pri ON pri.PerformanceRuleInstanceRowId = p.PerformanceRuleInstanceRowId INNER JOIN vPerformanceRule pr ON pr.RuleRowId = pri.RuleRowId INNER JOIN [OperationsManagerDW].[dbo].[Rule] R ON R.RuleRowId = pri.RuleRowId INNER JOIN [OperationsManagerDW].[dbo].[vManagementPack] mp ON mp.ManagementPackRowId = R.ManagementPackRowId WHERE me.ManagedEntityGuid = @InstanceId AND ( @RuleIDFilter IS NULL OR R.RuleGuid IN (SELECT value FROM STRING_SPLIT(@RuleIDFilter, ',')) ) GROUP BY pr.ObjectName, pr.CounterName, pri.InstanceName, R.RuleSystemName, R.RuleGuid, R.RuleDefaultName, me.ManagedEntityGuid, me.FullName, mp.ManagementPackSystemName, mp.ManagementPackVersionIndependentGuid "@ $paramObj = @{ Server = $DataWarehouseServerName Database = $DataWarehouseDatabaseName Query = $SQLCMD #As = 'DataRow' Parameters = @{ InstanceId = '' } Verbose = $PSBoundParameters.ContainsKey('Verbose') } #region ProcessRuleIdParameter - handle arrays, comma-separated strings, or single values $processedRuleIds = @() If ($RuleId) { Write-Verbose "Processing RuleId parameter: $($RuleId.GetType().Name)" if ($RuleId -is [Array]) { # Handle array input Write-Verbose "RuleId provided as array with $($RuleId.Count) items" foreach ($ruleGuid in $RuleId) { $guidRef = [Guid]::Empty if ([Guid]::TryParse($ruleGuid, [ref]$guidRef)) { $processedRuleIds += $ruleGuid.ToString() Write-Verbose "Valid GUID added from array: $ruleGuid" } else { Write-Warning "Invalid GUID format in array: '$ruleGuid' - skipping this entry" } } } elseif ($RuleId -is [String] -and $RuleId.Contains(',')) { # Handle comma-separated string $ruleGuids = $RuleId -split ',' | ForEach-Object { $_.Trim() } Write-Verbose "RuleId provided as comma-separated string with $($ruleGuids.Count) items" foreach ($ruleGuid in $ruleGuids) { if ([string]::IsNullOrWhiteSpace($ruleGuid)) { continue } $guidRef = [Guid]::Empty if ([Guid]::TryParse($ruleGuid, [ref]$guidRef)) { $processedRuleIds += $ruleGuid Write-Verbose "Valid GUID added from comma-separated string: $ruleGuid" } else { Write-Warning "Invalid GUID format in comma-separated string: '$ruleGuid' - skipping this entry" } } } elseif ($RuleId -is [String]) { # Handle single string Write-Verbose "RuleId provided as single string" $guidRef = [Guid]::Empty if ([Guid]::TryParse($RuleId, [ref]$guidRef)) { $processedRuleIds += $RuleId Write-Verbose "Valid single GUID: $RuleId" } else { Write-Warning "Invalid GUID format: '$RuleId' - ignoring RuleId filter" } } else { Write-Warning "RuleId parameter type not supported: $($RuleId.GetType().Name) - ignoring RuleId filter" } # Add processed RuleIds to parameters if any valid ones were found if ($processedRuleIds.Count -gt 0) { $ruleIdFilter = $processedRuleIds -join ',' $paramObj.Parameters.RuleIDFilter = $ruleIdFilter Write-Verbose "RuleID filter applied with $($processedRuleIds.Count) GUID(s): $ruleIdFilter" } else { Write-Warning "No valid GUIDs found in RuleId parameter - proceeding without RuleId filter" } } #endregion ProcessRuleIdParameter [System.Collections.arraylist]$hSignatures = @() ForEach ($obj in $allRMO) { Write-Verbose "Related Monitoring Object: $($obj.DisplayName) ($($obj.Id.Guid))" # Set the InstanceId parameter, then execute the query $paramObj.Parameters.InstanceId = $obj.Id.Guid $Result = @() try { $Result = Invoke-CLSqlCmd @paramObj } catch { Write-Error "Failed to execute SQL query for InstanceId '$($paramObj.Parameters.InstanceId)': $($_.Exception.Message)" Write-Verbose "SQL Query Error Details: $($_.Exception.ToString())" } If ($Result -and $Result.Count -gt 0) { $Result #| ForEach-Object {$hSignatures.Add($_) | Out-Null} } Else { Write-Verbose "No performance signatures found for InstanceId: $($paramObj.Parameters.InstanceId)" } } #Return ,$hSignatures } |