从 Azure 托管 Grafana 仪表板查询 Azure Monitor SCOM 托管实例数据

2024-11-05

本文介绍了如何从操作数据库查询监视数据，以及如何在 Azure 托管 Grafana 上创建仪表板。

从链接到 Azure Monitor SCOM 托管实例的 Azure 托管 Grafana，可以使用 SQL 查询从存储在 Azure 托管 SQL 托管实例上的操作数据库获取受监视的数据。

先决条件

在从 Azure 托管 Grafana 门户查询监视数据之前，请确保 SCOM 托管实例已链接到 Azure 托管 Grafana。有关详细信息，请参阅Azure 托管 Grafana 上的仪表板。

Azure/SCOM 托管实例/操作仪表板上提供少量现成的仪表板，可以根据 Azure 托管 Grafana 实例的要求导入和编辑这些仪表板。

在 Azure 托管 Grafana 上创建仪表板

要创建仪表板，请执行以下步骤：

导航到 Grafana，然后选择“添加可视化效果”。可以根据可用选项选择可视化效果的类型。
在“面板选项”下，输入“标题”和“说明”。
在“查询”下，选择“代码”并输入查询。
选择“运行查询”。

示例查询

下面是几个有用的示例查询和仪表板，可用于开始将 Azure 托管 Grafana 与 SCOM 托管实例配合使用：

工作负荷的运行状况
工作负荷运行状况及其新警报的数量
工作负荷上排名靠前的事件
工作负荷排名靠前的警报
工作负荷计数器的性能数据查询

以下查询可帮助从特定工作负荷/监视对象生成运行状况、警报和排名靠前的事件：

注意

将<管理包名称前缀>替换为实际的管理包名称，例如 SQL 工作负荷的Microsoft.SQL%。
将<监视对象类型>替换为组件类，例如，SQL Server 角色的%. DBEngine。

工作负荷的运行状况

SELECT HealthState =  

        CASE  

            WHEN MEV.HealthState = 1 THEN 'Healthy'  

            WHEN MEV.HealthState = 2 THEN 'Warning'  

            WHEN MEV.HealthState = 3 THEN 'Critical'  

            ELSE 'Uninitialized'  

        END,  

        CAST(COUNT(*)  AS DECIMAL(5, 2)) AS servers  

  FROM ${Database}.[dbo].[ManagedEntityGenericView] MEV  

  INNER JOIN ${Database}.[dbo].[ManagedTypeView] MTV ON MTV.Id = MEV.MonitoringClassId and MTV.name like '%.<Monitoring Object Type>'  

  INNER JOIN ${Database}.[dbo].[ManagementPackView] MPV ON MPV.Id = MTV.ManagementPackId and MPV.name like '<MP name Prefix>'  

  GROUP BY MEV.HealthState  

  ORDER BY MEV.HealthState

工作负荷运行状况及其新警报的数量

SELECT MEV.Name  

         ,HealthState =   

      CASE   

        WHEN MEV.HealthState = 1 THEN 'Healthy'  

        WHEN MEV.HealthState = 2 THEN 'Warning'  

        WHEN MEV.HealthState = 3 THEN 'Critical'  

        ELSE 'Uninitialized'  

      END  

      ,NewAlerts = COUNT(AV.ResolutionState)  

  FROM ${Database}.[dbo].[ManagedEntityGenericView] MEV  

  INNER JOIN ${Database}.[dbo].[ManagedTypeView] MTV ON MTV.Id = MEV.MonitoringClassId and MTV.name like '%.<Monitoring Object Type>'  

  INNER JOIN ${Database}.[dbo].[ManagementPackView] MPV ON MPV.Id = MTV.ManagementPackId and MPV.name like '%<MP name prefix>%'  

  INNER JOIN ${Database}.[dbo].[AlertView] AV ON AV.MonitoringClassId = MTV.Id and AV.ResolutionState = 0 AND  $__timeFilter(TimeRaised)  

  GROUP BY MEV.Name, HealthState, AV.ResolutionState

工作负荷上排名靠前的事件

SELECT EventDescription = LT5.LTValue  

      ,Count(*) Occurences  

      ,AffectedSQLServers = Count(DISTINCT(EV.LoggingComputer))  

  FROM ${Database}.[dbo].[EventView] EV  

  INNER JOIN ${Database}.[dbo].[ManagedTypeView] MTV ON MTV.Id = EV.ClassId   

  INNER JOIN ${Database}.[dbo].[ManagementPackView] MPV ON MPV.Id = MTV.ManagementPackId and MPV.name like '%<MP Name Prefix>%'  

  INNER JOIN ${Database}.dbo.LocalizedText LT5 ON EV.EventNumberStringId = LT5.LTStringId AND LT5.LanguageCode = 'ENU'  

  WHERE $__timeFilter(TimeGenerated) AND LevelId < 3  

GROUP BY Number, LT5.LTValue  

ORDER BY Occurences, AffectedSQLServers DESC;

工作负荷排名靠前的警报

SELECT AV.AlertStringName AS Alert  

      ,Occurrence = COUNT(AV.ResolutionState)  

      ,AffectedServers = COUNT(MEV.name)  

  FROM ${Database}.[dbo].[AlertView] AV  

  INNER JOIN ${Database}.[dbo].[ManagedTypeView] MTV ON MTV.Id = AV.MonitoringClassId and MTV.name like '%<Monitoring Object Type>'  

  INNER JOIN ${Database}.[dbo].[ManagementPackView] MPV ON MPV.Id = MTV.ManagementPackId and MPV.name like '%M<MP Name Prefix>%'  

  INNER JOIN ${Database}.[dbo].[ManagedEntityGenericView] MEV ON MTV.Id = MEV.MonitoringClassId  

  where AV.ResolutionState = 0 and  $__timeFilter(TimeRaised)  

  GROUP BY AV.AlertStringName, AV.ResolutionState

工作负荷计数器的性能数据查询

SELECT PD.TimeSampled 

   ,CASE  

		WHEN BME.Path IS NOT NULL AND BME.Path <> '' THEN CONCAT(BME.Path, '\', COALESCE(BME.Name, '')) 

		ELSE COALESCE(BME.Name, '') END AS TagetObjectPath 

  ,ObjectName = PC.ObjectName 

  ,CounterName = PC.CounterName 

  ,Value = PD.SampleValue 

  FROM dbo.PerformanceDataAllView PD  

  INNER JOIN dbo.PerformanceSource PS ON PD.PerformanceSourceInternalId = PS.PerformanceSourceInternalId 

  INNER JOIN dbo.PerformanceCounter PC ON PS.PerformanceCounterId = PC.PerformanceCounterId and CounterName = 'Receive I/Os/sec' 

  INNER JOIN dbo.BaseManagedEntity BME ON PS.BaseManagedEntityId = BME.BaseManagedEntityId AND BME.IsDeleted = 0  

  INNER JOIN [dbo].[ManagedTypeView] MTV ON MTV.Id = BME.BaseManagedTypeId  

  INNER JOIN [dbo].[ManagementPackView] MPV ON MPV.Id = MTV.ManagementPackId and MPV.name like 'Microsoft.SQL%'

后续步骤

排查 Azure Monitor SCOM 托管实例的问题。

通过