Get-SCOMObjectAvailability.ps1
|
Function Get-SCOMObjectAvailability { <# .SYNOPSIS Retrieves availability data for SCOM monitoring objects from the Operations Manager Data Warehouse. .DESCRIPTION The Get-SCOMObjectAvailability function queries the SCOM Data Warehouse to retrieve detailed availability statistics for monitoring objects. It calculates availability percentages based on different health states (Green, Yellow, Red) and provides comprehensive reporting data including uptime, downtime, and maintenance windows. This function leverages the Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityReportDataGet stored procedure to obtain accurate availability data and presents it in an easily consumable PowerShell object format. The function supports both hourly and daily aggregation and can work with either monitoring object GUIDs or direct monitoring object references from SCOM PowerShell cmdlets. .PARAMETER DataWarehouseServerName The name of the SQL Server hosting the SCOM Data Warehouse database. If not specified, the function attempts to retrieve this information from the local SCOM registry or SCOM PowerShell settings. Example: "SCOM-DW01.contoso.com" or "SCOM-DW01" .PARAMETER DataWarehouseDatabaseName The name of the SCOM Data Warehouse database. Defaults to "OperationsManagerDW" which is the standard database name for SCOM installations. .PARAMETER StartDate The start date and time for the availability calculation period. Defaults to 24 hours ago from the current time. Must be earlier than EndDate. Accepts DateTime objects or strings that can be parsed as dates. .PARAMETER EndDate The end date and time for the availability calculation period. Defaults to the current date and time. Must be later than StartDate. Accepts DateTime objects or strings that can be parsed as dates. .PARAMETER AggregationType Specifies the level of data aggregation for the availability calculation. - "Hourly": Provides hour-by-hour availability data (more granular) - "Daily": Provides day-by-day availability data (summary level) Default is "Hourly". Daily aggregation is recommended for longer time periods (weeks/months). .PARAMETER Id The GUID of the monitoring object for which to retrieve availability data. This parameter is used when you have the specific GUID of a SCOM monitoring object. Must be a valid GUID format. Use this parameter when working with the 'ById' parameter set. Aliases: InstanceId, MonitoringObjectId .PARAMETER MonitoringObject A SCOM MonitoringObject instance obtained from SCOM PowerShell cmdlets like Get-SCOMClassInstance. This parameter is used when you already have a monitoring object reference. Use this parameter when working with the 'ByObject' parameter set. .PARAMETER QueryTimeout The timeout value in seconds for the SQL query execution. Defaults to 30 seconds. Increase this value for large datasets or slower database servers. Valid range: 1-3600 seconds. .PARAMETER ConnectionTimeout The timeout value in seconds for establishing the database connection. Defaults to 15 seconds. Increase this value for slower network connections or heavily loaded database servers. Valid range: 1-300 seconds. .INPUTS None. This function does not accept pipeline input. .OUTPUTS [PSCustomObject] Returns a custom PowerShell object with the following properties: Entity Information: - ManagedEntityRowId: Internal SCOM database row identifier - ManagedEntityGuid: GUID of the monitoring object - Id: Alias for ManagedEntityGuid (for compatibility) - ManagedEntityDefaultName: Default name from SCOM - Path: Full path of the object in SCOM hierarchy - DisplayName: Display name of the monitoring object Time Period Information: - StartTime: Beginning of the availability measurement period - EndTime: End of the availability measurement period - DurationHours: Total hours covered by the measurement (formatted string) - SampleCount: Number of data samples used in calculation - AggregationType: Type of aggregation used (Hourly/Daily) Availability Statistics: - GreenMilliseconds: Time in healthy state (milliseconds) - YellowMilliseconds: Time in warning state (milliseconds) - RedMilliseconds: Time in critical state (milliseconds) - GreenPct: Percentage of time in healthy state (formatted string) - YellowPct: Percentage of time in warning state (formatted string) - RedPct: Percentage of time in critical state (formatted string) - OverallAvailabilityPct: Overall availability percentage (formatted string) Additional State Information: - DisabledMilliseconds: Time in disabled state - PlannedMaintenanceMilliseconds: Time in planned maintenance - UnplannedMaintenanceMilliseconds: Time in unplanned maintenance - HealthServiceUnavailableMilliseconds: Time when health service was unavailable .EXAMPLE # Get availability for a specific monitoring object by GUID $objectId = "c5a9c43c-84d9-a3c2-6e84-c9d68bf79aa1" $availability = Get-SCOMObjectAvailability -Id $objectId -StartDate (Get-Date).AddDays(-7) -EndDate (Get-Date) Write-Host "Availability for last 7 days: $($availability.OverallAvailabilityPct)%" This example retrieves 7-day availability data for a specific monitoring object using its GUID. .EXAMPLE # Get availability using a monitoring object reference $webApp = Get-SCOMClass -Name 'Microsoft.SystemCenter.WebApplication.Perspective' | Get-SCOMClassInstance | Where-Object {$_.DisplayName -like "*Production*"} $availability = Get-SCOMObjectAvailability -MonitoringObject $webApp -AggregationType Daily -StartDate (Get-Date).AddDays(-30) $availability | Select-Object DisplayName, StartTime, EndTime, OverallAvailabilityPct, SampleCount | Format-Table This example gets 30-day availability data for a web application using daily aggregation. .EXAMPLE # Generate availability report for multiple objects $servers = Get-SCOMClass -Name 'Microsoft.Windows.Computer' | Get-SCOMClassInstance | Where-Object {$_.DisplayName -like "SQL*"} $report = @() foreach ($server in $servers) { $availability = Get-SCOMObjectAvailability -MonitoringObject $server -StartDate (Get-Date).AddDays(-14) -AggregationType Daily $report += [PSCustomObject]@{ ServerName = $server.DisplayName Availability = $availability.OverallAvailabilityPct GreenTime = $availability.GreenPct RedTime = $availability.RedPct Samples = $availability.SampleCount } } $report | Sort-Object Availability | Format-Table This example generates a availability report for multiple SQL servers over the last 14 days. .EXAMPLE # Use custom database server $availability = Get-SCOMObjectAvailability -Id "a1b2c3d4-e5f6-7890-abcd-ef1234567890" ` -DataWarehouseServerName "SCOM-DW02.contoso.com" ` -StartDate "2025-06-01" ` -EndDate "2025-06-30" ` -AggregationType Daily ` -Verbose Write-Host "June 2025 Availability Report:" -ForegroundColor Green Write-Host "Object: $($availability.DisplayName)" Write-Host "Period: $($availability.StartTime) to $($availability.EndTime)" Write-Host "Overall Availability: $($availability.OverallAvailabilityPct)%" Write-Host "Green Time: $($availability.GreenPct)%" Write-Host "Yellow Time: $($availability.YellowPct)%" Write-Host "Red Time: $($availability.RedPct)%" This example demonstrates using custom database settings and verbose output. .EXAMPLE # Export availability data to CSV for analysis $watmObjects = Get-SCOMClass -Name 'Microsoft.SystemCenter.WebApplication.Perspective' | Get-SCOMClassInstance $availabilityData = @() foreach ($watm in $watmObjects) { Write-Progress -Activity "Processing WATM Objects" -Status $watm.DisplayName -PercentComplete (($availabilityData.Count / $watmObjects.Count) * 100) try { $avail = Get-SCOMObjectAvailability -MonitoringObject $watm -StartDate (Get-Date).AddDays(-90) -AggregationType Daily $availabilityData += $avail } catch { Write-Warning "Failed to get availability for $($watm.DisplayName): $($_.Exception.Message)" } } $availabilityData | Export-Csv -Path "C:\Reports\WATM_Availability_90Days.csv" -NoTypeInformation Write-Host "Exported availability data for $($availabilityData.Count) objects to CSV" This example processes multiple web application monitoring objects and exports the results to CSV. .EXAMPLE # Compare availability between different time periods $objectId = "c5a9c43c-84d9-a3c2-6e84-c9d68bf79aa1" $lastWeek = Get-SCOMObjectAvailability -Id $objectId -StartDate (Get-Date).AddDays(-14) -EndDate (Get-Date).AddDays(-7) $thisWeek = Get-SCOMObjectAvailability -Id $objectId -StartDate (Get-Date).AddDays(-7) -EndDate (Get-Date) Write-Host "Availability Comparison for $($thisWeek.DisplayName):" -ForegroundColor Cyan Write-Host "Last Week: $($lastWeek.OverallAvailabilityPct)%" -ForegroundColor $(if([decimal]$lastWeek.OverallAvailabilityPct -gt 95) {'Green'} else {'Red'}) Write-Host "This Week: $($thisWeek.OverallAvailabilityPct)%" -ForegroundColor $(if([decimal]$thisWeek.OverallAvailabilityPct -gt 95) {'Green'} else {'Red'}) $trend = [decimal]$thisWeek.OverallAvailabilityPct - [decimal]$lastWeek.OverallAvailabilityPct Write-Host "Trend: $(if($trend -gt 0) {'+'})$([Math]::Round($trend, 2))%" -ForegroundColor $(if($trend -gt 0) {'Green'} else {'Red'}) This example compares availability between two different time periods to show trends. .NOTES Author: Tyson Paul (https://monitoringguys.com/2019/11/12/scomhelper/) Version History: 2025.07.07 - 1.0: Initial version with comprehensive availability reporting Requirements: - SCOM 2012 R2 or later with Data Warehouse configured - PowerShell 3.0 or later - SCOM PowerShell module loaded (for MonitoringObject parameter) - Appropriate permissions to query the SCOM Data Warehouse database - Network connectivity to the SCOM Data Warehouse server Performance Considerations: - Large date ranges with hourly aggregation can result in significant data processing - Daily aggregation is recommended for periods longer than 30 days - Consider using custom QueryTimeout for large datasets - The function queries the actual availability data, not cached summary tables Database Dependencies: - Requires access to the OperationsManagerDW database - Uses the Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityReportDataGet stored procedure - Queries the ManagedEntity table for object resolution Troubleshooting: - Ensure SCOM Data Warehouse is properly configured and operational - Verify that the monitoring object exists and has availability data in the specified time range - Check database permissions if authentication errors occur - Use -Verbose for detailed execution information - Validate that the GUID format is correct when using the -Id parameter Common Use Cases: - SLA reporting and compliance monitoring - Availability trending and analysis - Performance baseline establishment - Capacity planning based on historical availability - Troubleshooting recurring availability issues Related SCOM Cmdlets: - Get-SCOMClassInstance: Retrieve monitoring objects - Get-SCOMClass: Find monitoring classes - Get-SCOMDataWarehouseSetting: Get Data Warehouse configuration .LINK Get-SCOMClassInstance Get-SCOMClass Get-SCOMDataWarehouseSetting .FUNCTIONALITY SCOM Data Warehouse, Availability Reporting, System Center Operations Manager, SLA Monitoring #> 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 the last 24 hours [Parameter(Mandatory = $false)] [datetime]$StartDate = (Get-Date).AddDays(-1), # Defaults to the current time [Parameter(Mandatory = $false)] [datetime]$EndDate = (Get-Date), # Defaults to Hourly aggregation # Options are "Hourly" or "Daily" [Parameter(Mandatory = $false)] [ValidateSet("Hourly", "Daily")] [string]$AggregationType = "Hourly", # Id/Guid of a monitoring object [Parameter(Mandatory=$true, ParameterSetName="ById")] [ValidateNotNullOrEmpty()] [Alias("InstanceId","MonitoringObjectId")] [ValidateScript({ if ([Guid]::TryParse($_, [ref][Guid]::Empty)) { $true } else { throw "Invalid GUID format. Please provide a valid GUID string." } })] [System.Object]$Id, # The monitoring object itself (alternative to Id parameter) [Parameter(Mandatory=$true, ParameterSetName="ByObject")] [ValidateNotNull()] [Microsoft.EnterpriseManagement.Monitoring.MonitoringObject]$MonitoringObject, # 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 ) If ($PSCmdlet.ParameterSetName -eq "ByObject") { # If the object is provided, extract the Id $Id = $MonitoringObject.Id.Guid } # Create hash table for aggregation type mapping $AggregationMap = @{ "Hourly" = 0 "Daily" = 1 } # Retrieve the name of the Operational Database and Data WareHouse Servers from the registry. $reg = Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup\" #$OperationsManagerDBServer = $reg.DatabaseServerName $DataWarehouseServerName = $reg.DataWarehouseDBServerName # If the value is empty in this key, then we'll use the Get-SCOMDataWarehouseSetting cmdlet. If (!($DataWarehouseServerName)) { $DataWarehouseServerName = Get-SCOMDataWarehouseSetting | Select-Object -expandProperty DataWarehouseServerName } #$OperationsManagerDBServer = $OperationsManagerDBServer.ToUpper() $DataWarehouseServerName = $DataWarehouseServerName.ToUpper() $Query = @" -- Get MERowID by using InstanceID DECLARE @MEGuid NVARCHAR(50) = N'$($Id)'; DECLARE @MERowId INT; SELECT @MERowId = [ManagedEntityRowId] FROM [OperationsManagerDW].[dbo].[ManagedEntity] WHERE [ManagedEntityGuid] = @MEGuid; -- Build the XML object list string FIRST DECLARE @ObjectList NVARCHAR(MAX); SET @ObjectList = N'<Data><Objects><Object Use="Self">' + CAST(@MERowId AS NVARCHAR(50)) + '</Object></Objects></Data>'; EXEC Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityReportDataGet @ObjectList = @ObjectList, @MonitorName = N'System.Health.AvailabilityState', @StartDate = '$($StartDate.ToString("yyyy-MM-dd HH:mm:ss"))', @EndDate = '$($EndDate.ToString("yyyy-MM-dd HH:mm:ss"))', @DataAggregation = $($AggregationMap[$AggregationType]) --@LanguageCode = N'ENU'; "@ # Each element should be a PSObject with the SCOM SP's output columns [array]$DataRows = Invoke-CLSqlCmd -Server $DataWarehouseServerName -Database $DataWarehouseDatabaseName -Query $Query # Defensive: handle empty input if (-not $DataRows -or $DataRows.Count -eq 0) { Write-Warning "No data provided." return $null } # Sum up durations (all in milliseconds) $sum = @{ Green = 0 Yellow = 0 Red = 0 White = 0 Disabled = 0 PlannedMaintenance = 0 UnplannedMaintenance = 0 HealthServiceUnavailable = 0 Total = 0 } $sum.Green = $DataRows.InGreenStateMilliseconds | Measure-Object -Sum | Select-Object -ExpandProperty Sum $sum.Yellow = $DataRows.InYellowStateMilliseconds | Measure-Object -Sum | Select-Object -ExpandProperty Sum $sum.Red = $DataRows.InRedStateMilliseconds | Measure-Object -Sum | Select-Object -ExpandProperty Sum $sum.White = $DataRows.InWhiteStateMilliseconds | Measure-Object -Sum | Select-Object -ExpandProperty Sum $sum.Disabled = $DataRows.InDisabledStateMilliseconds | Measure-Object -Sum | Select-Object -ExpandProperty Sum $sum.PlannedMaintenance = $DataRows.InPlannedMaintenanceMilliseconds | Measure-Object -Sum | Select-Object -ExpandProperty Sum $sum.UnplannedMaintenance = $DataRows.InUnplannedMaintenanceMilliseconds | Measure-Object -Sum | Select-Object -ExpandProperty Sum $sum.HealthServiceUnavailable = $DataRows.HealthServiceUnavailableMilliseconds | Measure-Object -Sum | Select-Object -ExpandProperty Sum $sum.Total = $DataRows.IntervalDurationMilliseconds | Measure-Object -Sum | Select-Object -ExpandProperty Sum # Calculate percentages $availability = if ($sum.Total -gt 0) { 100 * $sum.Green / $sum.Total } else { 0 } $yellowPct = if ($sum.Total -gt 0) { 100 * $sum.Yellow / $sum.Total } else { 0 } $redPct = if ($sum.Total -gt 0) { 100 * $sum.Red / $sum.Total } else { 0 } # Grab the basic details from the first row for consistency (assuming same entity) $entityRow = $DataRows[0] [PSCustomObject]@{ ManagedEntityRowId = $entityRow.ManagedEntityRowId ManagedEntityGuid = [GUID]($entityRow.ManagedEntityGuid) Id = [GUID]($entityRow.ManagedEntityGuid) ManagedEntityDefaultName = $entityRow.ManagedEntityDefaultName Path = $entityRow.Path DisplayName = $entityRow.DisplayName StartTime = $StartDate EndTime = $EndDate DurationHours = "{0:N2}" -f (($EndDate - $StartDate).TotalHours) SampleCount = $DataRows.Count AggregationType = $AggregationType #TotalDurationMilli = $sum.Total #TotalDurationHours = "{0:N2}" -f ($sum.Total/3600000) GreenMilliseconds = $sum.Green YellowMilliseconds = $sum.Yellow RedMilliseconds = $sum.Red GreenPct = "{0:N2}" -f $availability YellowPct = "{0:N2}" -f $yellowPct RedPct = "{0:N2}" -f $redPct OverallAvailabilityPct = "{0:N2}" -f $availability DisabledMilliseconds = $sum.Disabled PlannedMaintenanceMilliseconds = $sum.PlannedMaintenance UnplannedMaintenanceMilliseconds = $sum.UnplannedMaintenance HealthServiceUnavailableMilliseconds = $sum.HealthServiceUnavailable } } #------------------------------------------------------------------------------------ $WATMs = Get-SCOMClass -Name 'Microsoft.SystemCenter.WebApplication.Perspective' | Get-SCOMClassInstance $WATM = $WATMs | Where-Object {$_.DisplayName -like "*My WATM Name*"} $result = Get-SCOMObjectAvailability -MonitoringObject $WATM -StartDate (Get-Date).AddDays(-35) -EndDate (Get-Date) -Verbose -AggregationType Daily $result |