OpsMgr - SQL Scripts for Dashboards

OpsMgr collects a lot of awesome data about your service. Why not display it? You might want to use Excel or Visio, or a HTML5 dashboard by Squared up. Possibilities are endless.

Here are some awesome SQL scripts that can used for effective dashboards! If you build any dashboards with these scripts, let me know by posting a picture Twitter or Instagram and use #scomrocks.

 

Number of active alerts per Computer group from OperationsManager:
SELECT COUNT(DIStINCt AlertView.ID) As AlertCount, GV.SourceObjectDisplayName
FROM AlertView
 INNER JOIN dbo.RecursiveMembership AS RM
     ON AlertView.MonitoringObjectId = RM.ContainedEntityId
 INNER JOIN RelationshipGenericView AS GV
 ON GV.SourceObjectId = RM.ContainerEntityId
WHERE (AlertView.Severity = 2 ) AND (AlertView.[ResolutionState] = 0) AND (AlertView.TimeRaised is not NULL )
Group by GV.SourceObjectDisplayName

 

Distributed Application availability from OperationsManager:
SELECT [DisplayName]
      ,[LastModified]
      ,[HealthState]
      ,[IsAvailable]
      ,[AvailabilityLastModified]
   ,[InMaintenanceMode]
   ,[MaintenanceModeLastModified]
  FROM [ManagedEntityGenericView]
  where displayname = 'My DA Name'

 

Days since last unplanned outage from OperationsManagerDW:
select top 1 OldHealthState, NewHealthState, DATEDIFF(DAY,CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset,DateTime),DATENAME(TzOffset, SYSDATETIMEOFFSET()))),getdate()) as 'Days Ago',
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset,DateTime),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS 'Availability Last Modified'
,mon.[MonitorDefaultName]
FROM [OperationsManagerDW].[dbo].[vManagedEntityMonitor] as vm
inner join vManagedEntity vme on vme.ManagedEntityRowId = vm.ManagedEntityRowId
inner join vMonitor mon on mon.MonitorRowId = vm.MonitorRowId
inner join State.vStateRaw vsr on vsr.ManagedEntityMonitorRowId = vm.MonitorRowId
where vme.DisplayName = 'TEST DA' and MonitorDefaultName = 'Availability' and (NewHealthState = 1 and OldHealthState = 3)
order by vsr.DateTime desc

 

Days since last planned outage (Maintenance mode) from OperationsManagerDW:
select top 1 OldHealthState, NewHealthState, DATEDIFF(DAY,CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset,DateTime),DATENAME(TzOffset, SYSDATETIMEOFFSET()))),getdate()) as 'Days Ago',
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset,DateTime),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS 'Availability Last Modified'
,mon.[MonitorDefaultName]
FROM [OperationsManagerDW].[dbo].[vManagedEntityMonitor] as vm
inner join vManagedEntity vme on vme.ManagedEntityRowId = vm.ManagedEntityRowId
inner join vMonitor mon on mon.MonitorRowId = vm.MonitorRowId
inner join State.vStateRaw vsr on vsr.ManagedEntityMonitorRowId = vm.MonitorRowId
where vme.DisplayName = 'TEST DA' and MonitorDefaultName = 'Availability' and (NewHealthState = 1 and OldHealthState = 3)
order by vsr.DateTime desc