SQL Queries for SCOM Dashboards

SQL Queries used for Sharepoint Dashboards used with Service Center Dashboard Solution Accelorator and WSS 3.0.

 

 

 

Display Distributed Application Health

Use with Score Card

SELECT distinct BaseManagedEntity.DisplayName, State.HealthState FROM State INNER JOIN BaseManagedEntity ON State.BaseManagedEntityId = BaseManagedEntity.BaseManagedEntityId

where (BaseManagedEntity.FullName like ‘Service_%’

or BaseManagedEntity.DisplayName = ‘Active Directory Topology Root’

or BaseManagedEntity.DisplayName = ‘Certificate Services’

or BaseManagedEntity.DisplayName = ‘Exchange 2007 Service’

or BaseManagedEntity.DisplayName = ‘Operations Manager Management Group’)and BaseManagedEntity.Name is NULL

Percentage of Failed Agents

Use with Gauge

WITH ServerStatus AS (

SELECT

CASE WHEN ManagedEntityGenericView.IsAvailable = ‘True’ THEN 1 ELSE 0 END AS Responding,

CASE WHEN ManagedEntityGenericView.IsAvailable = ‘False’ THEN 1 ELSE 0 END AS NotResponding FROM

ManagedEntityGenericView

INNER JOIN ManagedTypeView

ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id WHERE

ManagedTypeView.Name = ‘Microsoft.SystemCenter.Agent’) SELECT

SUM(Responding),

SUM(NotResponding),

(CAST(SUM(NotResponding)AS MONEY) / CAST((SUM(Responding) + SUM(NotResponding))AS money) * 100.00) AS DownAgentPercent FROM

ServerStatus

All Managed Agents in MG

Use with Gauge

SELECT COUNT(*) AS NumManagedComps FROM (

SELECT bme2.BaseManagedEntityID

FROM BaseManagedEntity bme WITH (NOLOCK)

INNER JOIN BaseManagedEntity bme2 WITH (NOLOCK) ON bme2.BaseManagedEntityID = bme.TopLevelHostEntityID

WHERE bme2.IsDeleted = 0

AND bme2.IsDeleted = 0

AND bme2.BaseManagedTypeID = (SELECT TOP 1 ManagedTypeID FROM ManagedType WHERE TypeName = ‘microsoft.windows.computer’)

GROUP BY bme2.BaseManagedEntityID

) AS Comps

Top 20 Alerts

Use with Pie Chart

 

SELECT TOP 20 SUM(1) AS AlertCount, AlertStringName

FROM Alertview WITH (NOLOCK)

WHERE TimeRaised is not NULL

GROUP BY AlertStringName

ORDER BY AlertCount DESC

Noisy Alerts high repeat Counts Top 10

 

SELECT TOP 10 SUM(RepeatCount+1) AS RepeatCount, AlertStringName

FROM Alertview WITH (NOLOCK)

WHERE Timeraised is not NULL

GROUP BY AlertStringName

ORDER BY RepeatCount DESC

Database Free Space

Use as Score Card Label Filename Value Space Used

USE OperationsManager

select a.FILEID,

[FILE_SIZE_MB]=convert(decimal(12,2),round(a.size/128.000,2)),

[SPACE_USED_MB]=convert(decimal(12,2),round(fileproperty(a.name,’SpaceUsed’)/128.000,2)),

[FREE_SPACE_MB]=convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) ,

[GROWTH_MB]=convert(decimal(12,2),round(a.growth/128.000,2)),

NAME=left(a.NAME,15),

FILENAME=left(a.FILENAME,60)

from dbo.sysfiles a

Agent Status (Responding and Not Responding

Use as Bar

SELECT ‘Responding’ as Status,  COUNT(*) as TotalMachines FROM ManagedEntityGenericView INNER JOIN ManagedTypeView WITH (NOLOCK)

ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id

WHERE (ManagedEntityGenericView.IsAvailable = ‘True’) AND (ManagedTypeView.Name = ‘Microsoft.SystemCenter.Agent’)

Union

SELECT ‘NotResponding’ as Status,  COUNT(*) as TotalMachines FROM ManagedEntityGenericView INNER JOIN ManagedTypeView WITH (NOLOCK)

ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id

WHERE (ManagedEntityGenericView.IsAvailable = ‘false’) AND (ManagedTypeView.Name = ‘Microsoft.SystemCenter.Agent’)

Total Number Of Alerts Per Day 7 Days

Take this from the OperationsManagerDW Display As Bar

 

USE OperationsManagerDW

SELECT CONVERT(VARCHAR(10), DBCreatedDateTime, 101) AS Date, COUNT(*) AS Alerts

FROM  Alert.vAlert

WHERE (DBCreatedDateTime BETWEEN DATEADD(day, -6, GETDATE()) AND GETDATE())

GROUP BY CONVERT(VARCHAR(10), DBCreatedDateTime, 101)

ORDER BY Date DESC

Installed Management Packs

Display As DataGrid

 

SELECT MPName, MPFriendlyName, MPVersion, MPIsSealed

FROM ManagementPack WITH(NOLOCK)

ORDER BY MPName

Servers Down

 

Display As DataGrid

 

SELECT bme.DisplayName,s.LastModified as LastModifiedUTC, dateadd(hh,-5,s.LastModified) as ‘LastModifiedCST (GMT-5)’

FROM state AS s, BaseManagedEntity AS bme

WHERE s.basemanagedentityid = bme.basemanagedentityid

AND s.monitorid

IN (SELECT MonitorId FROM Monitor WHERE MonitorName = ‘Microsoft.SystemCenter.HealthService.ComputerDown’)

AND s.Healthstate = ‘3’ AND bme.IsDeleted = ‘0’

ORDER BY s.Lastmodified DESC

Blogs on subject

http://www.systemcentercentral.com/tabid/143/indexid/86564/default.aspx

http://www.pavleck.net/2008/06/useful-scom-sql-queries/

http://www.systemcentercentral.com/BlogDetails/tabid/143/IndexID/20677/Default.aspx

WSS 3.0 Download

http://www.microsoft.com/download/en/details.aspx?id=10404

System Center Service Manager 2010 Dashboard

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16445

Advertisements
This entry was posted in Service Level Dashboard, SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s