Useful Operations Manager 2007 SQL queries


Last updated 10-12-10

 

 

Large Table query.  (I am putting this at the top, because I use it so much – to find out what is taking up so much space in the OpsDB or DW)

SELECT TOP 1000
a2.name AS [tablename], (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.rows as row_count, a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) – a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) – a1.used ELSE 0 END) * 8 AS unused,
(row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
a3.name AS [schemaname]
FROM (SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN (SELECT it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N’S’ and a2.type <> N’IT’  

 

Database Size and used space.  (People have a lot of confusion here – this will show the DB and log file size, plus the used/free space in each)

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

 

 

Operational Database Section: 

 

Alerts Section:

Number of console Alerts per Day:

SELECT CONVERT(VARCHAR(20), TimeAdded, 102) AS DayAdded, COUNT(*) AS NumAlertsPerDay
FROM Alert WITH (NOLOCK)
WHERE TimeRaised is not NULL
GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102)
ORDER BY DayAdded DESC

Top 20 Alerts in an Operational Database, by Alert Count

SELECT TOP 20 SUM(1) AS AlertCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name
FROM Alertview WITH (NOLOCK)
WHERE TimeRaised is not NULL
GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name
ORDER BY AlertCount DESC

Top 20 Alerts in an Operational Database, by Repeat Count

SELECT TOP 20 SUM(RepeatCount+1) AS RepeatCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name
FROM Alertview WITH (NOLOCK)
WHERE Timeraised is not NULL
GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name
ORDER BY RepeatCount DESC

Number of console Alerts per Day by Resolution State:

SELECT
CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeAdded, 102)) = 1) THEN ‘All Days’ ELSE CONVERT(VARCHAR(20), TimeAdded, 102) END AS [Date],
CASE WHEN(GROUPING(ResolutionState) = 1) THEN ‘All Resolution States’ ELSE CAST(ResolutionState AS VARCHAR(5)) END AS [ResolutionState],
COUNT(*) AS NumAlerts
FROM Alert WITH (NOLOCK)
WHERE TimeRaised is not NULL
GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102), ResolutionState WITH ROLLUP
ORDER BY DATE DESC

 

(Note:  There will be more alerts in the "Alert" table in the form of rows, than exist in the console.  This is because there are non-console alerts where TimeRaised is NULL – these have to do with driving state change records, and are not included in the above queries by design)

 

Events Section:

All Events by count by day, with total for entire database:  (this tells us how many events per day we are inserting – and helps us look for too many events, event storms, and the result after tuning rules that generate too many events)

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeAdded, 102)) = 1)
THEN ‘All Days’
ELSE CONVERT(VARCHAR(20), TimeAdded, 102) END AS DayAdded,
COUNT(*) AS EventsPerDay
FROM EventAllView
GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102) WITH ROLLUP
ORDER BY DayAdded DESC

Most Common Events by event number:  (This helps us know which event ID’s are the most common in the database)

SELECT top 20 Number as EventID, COUNT(*) AS TotalEvents
FROM EventView with (NOLOCK)
GROUP BY Number
ORDER BY TotalEvents DESC

Most common events by event number and event publishername: (This gives us the event source name to help see what is raising these events)

SELECT top 20 Number as EventID, COUNT(*) AS TotalEvents, Publishername as EventSource
FROM EventAllView eav with (nolock)
GROUP BY Number, Publishername
ORDER BY TotalEvents DESC

Most common events, grouped by identical event number, publishername, and event parameters: (This shows use completely redundant events with identical data – but might be different than the above queries… you need to see both data outputs to fully tune)

SELECT top 100 Number as EventID, COUNT(*) AS TotalEvents, Publishername as EventSource, EventParameters
FROM EventAllView with (NOLOCK)
GROUP BY Number, Publishername, EventParameters
ORDER BY TotalEvents DESC

Computers generating the most events: (This shows us which computers create the most event traffic and use the most database space)

SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents
FROM EventallView with (NOLOCK)
GROUP BY LoggingComputer
ORDER BY TotalEvents DESC

Computers generating the most events, by event number: (This shows the noisiest computers, group by unique event numbers)

SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents, Number as EventID
FROM EventallView with (NOLOCK)
GROUP BY LoggingComputer, Number
ORDER BY TotalEvents DESC

Computers generating the most events, grouped by identical event number and publishername: 

SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents, PublisherName as EventSource, Number as EventID
FROM EventallView with (NOLOCK)
GROUP BY LoggingComputer, PublisherName, Number
ORDER BY TotalEvents DESC

 

 

Performance Section: 

Performance insertions per day: 

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeSampled, 102)) = 1)
THEN ‘All Days’ ELSE CONVERT(VARCHAR(20), TimeSampled, 102)
END AS DaySampled, COUNT(*) AS PerfInsertPerDay
FROM PerformanceDataAllView with (NOLOCK)
GROUP BY CONVERT(VARCHAR(20), TimeSampled, 102) WITH ROLLUP
ORDER BY DaySampled DESC

Top 20 performance insertions by perf object and counter name: 

select top 20 pcv.ObjectName, pcv.CounterName, count (pcv.countername) as Total
from performancedataallview as pdv, performancecounterview as pcv
where (pdv.performancesourceinternalid = pcv.performancesourceinternalid)
group by pcv.objectname, pcv.countername
order by count (pcv.countername) desc

To view all performance insertions for a given computer:

select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where path = ‘omterm.opsmgr.net’
order by countername, timesampled

To refine a the above query to pull all perf data for a given computer, object, counter, and instance:

select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where path = ‘omterm.opsmgr.net’ AND
objectname = ‘LogicalDisk’ AND
countername = ‘Free Megabytes’
order by timesampled

 

 

State Section: 

To find out how old your StateChange data is:

declare @statedaystokeep INT
SELECT @statedaystokeep = DaysToKeep from PartitionAndGroomingSettings WHERE ObjectName = ‘StateChangeEvent’
SELECT COUNT(*) as ‘Total StateChanges’,
count(CASE WHEN sce.TimeGenerated > dateadd(dd,-@statedaystokeep,getutcdate()) THEN sce.TimeGenerated ELSE NULL END) as ‘within grooming retention’,
count(CASE WHEN sce.TimeGenerated < dateadd(dd,-@statedaystokeep,getutcdate()) THEN sce.TimeGenerated ELSE NULL END) as ‘> grooming retention’,
count(CASE WHEN sce.TimeGenerated < dateadd(dd,-30,getutcdate()) THEN sce.TimeGenerated ELSE NULL END) as ‘> 30 days’,
count(CASE WHEN sce.TimeGenerated < dateadd(dd,-90,getutcdate()) THEN sce.TimeGenerated ELSE NULL END) as ‘> 90 days’,
count(CASE WHEN sce.TimeGenerated < dateadd(dd,-365,getutcdate()) THEN sce.TimeGenerated ELSE NULL END) as ‘> 365 days’
from StateChangeEvent sce

Cleanup old statechanges for disabled monitors:

http://blogs.technet.com/kevinholman/archive/2009/12/21/tuning-tip-do-you-have-monitors-constantly-flip-flopping.aspx

State changes per day: 

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeGenerated, 102)) = 1)
THEN ‘All Days’ ELSE CONVERT(VARCHAR(20), TimeGenerated, 102)
END AS DayGenerated, COUNT(*) AS StateChangesPerDay
FROM StateChangeEvent WITH (NOLOCK)
GROUP BY CONVERT(VARCHAR(20), TimeGenerated, 102) WITH ROLLUP
ORDER BY DayGenerated DESC

Noisiest monitors changing state in the database in the last 7 days:

select distinct top 50 count(sce.StateId) as NumStateChanges,
m.DisplayName as MonitorDisplayName,
m.Name as MonitorIdName,
mt.typename AS TargetClass
from StateChangeEvent sce with (nolock)
join state s with (nolock) on sce.StateId = s.StateId
join monitorview m with (nolock) on s.MonitorId = m.Id
join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId
where m.IsUnitMonitor = 1
  — Scoped to within last 7 days
AND sce.TimeGenerated > dateadd(dd,-7,getutcdate())
group by m.DisplayName, m.Name,mt.typename
order by NumStateChanges desc

Noisiest Monitor in the database – PER Object/Computer in the last 7 days:

select distinct top 50 count(sce.StateId) as NumStateChanges,
bme.DisplayName AS ObjectName,
bme.Path,
m.DisplayName as MonitorDisplayName,
m.Name as MonitorIdName,
mt.typename AS TargetClass
from StateChangeEvent sce with (nolock)
join state s with (nolock) on sce.StateId = s.StateId
join BaseManagedEntity bme with (nolock) on s.BasemanagedEntityId = bme.BasemanagedEntityId
join MonitorView m with (nolock) on s.MonitorId = m.Id
join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId
where m.IsUnitMonitor = 1
   — Scoped to specific Monitor (remove the "–" below):
   — AND m.MonitorName like (‘%HealthService%’)
   — Scoped to specific Computer (remove the "–" below):
   — AND bme.Path like (‘%sql%’)
   — Scoped to within last 7 days
AND sce.TimeGenerated > dateadd(dd,-7,getutcdate())
group by s.BasemanagedEntityId,bme.DisplayName,bme.Path,m.DisplayName,m.Name,mt.typename
order by NumStateChanges desc

 

 

Performance Signature Section: 

To find the rules collecting the most Performance Signature data in the database: 

select managementpack.MPName, ruleview.DisplayName,
count(*) AS TotalPerfSig
from performancesignaturedata with (nolock)
inner join performancesignaturehistory with (nolock)
on performancesignaturedata.performancesignaturehistoryid = performancesignaturehistory.performancesignaturehistoryid
inner join performancesignature with (nolock)
on performancesignaturehistory.performancesignatureid = performancesignature.performancesignatureid
inner join ruleview with (nolock)
on ruleview.id = performancesignature.learningruleid
inner join managementpack with(nolock)
on ruleview.managementpackid = managementpack.managementpackid
group by managementpack.mpname, ruleview.Displayname
order by TotalPerfSig DESC, managementpack.mpname, ruleview.DisplayName

To find all Performance Signature Collection rules: 

select managementpack.mpname, rules.rulename
from performancesignature with (nolock)
inner join rules with (nolock)
on rules.ruleid = performancesignature.learningruleid
inner join managementpack with(nolock)
on rules.managementpackid = managementpack.managementpackid
group by managementpack.mpname, rules.rulename
order by managementpack.mpname, rules.rulename

 

 

Management Pack info:

Rules section:

To find a common rule name given a Rule ID name:

SELECT DisplayName from RuleView
where name = ‘Microsoft.SystemCenter.GenericNTPerfMapperModule.FailedExecution.Alert’
— change the ‘name’ value above to the Rule ID shown in an alert

Rules per MP:

SELECT mp.MPName, COUNT(*) As RulesPerMP
FROM Rules r
INNER JOIN ManagementPack mp ON mp.ManagementPackID = r.ManagementPackID
GROUP BY mp.MPName
ORDER BY RulesPerMP DESC

Rules per MP by category:

SELECT mp.MPName, r.RuleCategory, COUNT(*) As RulesPerMPPerCategory
FROM Rules r
INNER JOIN ManagementPack mp ON mp.ManagementPackID = r.ManagementPackID
GROUP BY mp.MPName, r.RuleCategory
ORDER BY RulesPerMPPerCategory DESC 

To find all Rules per MP that generate an alert: 

declare @mpid as varchar(50)
select @mpid= managementpackid from managementpack where
mpName=’Microsoft.Exchange.2007′
select rl.rulename,rl.ruleid,md.modulename from rules rl, module md
where md.managementpackid = @mpid
and rl.ruleid=md.parentid
and moduleconfiguration like ‘%<AlertLevel>50</AlertLevel>%’

To find all rules per MP with a given alert severity:

declare @mpid as varchar(50)
select @mpid= managementpackid from managementpack where
mpName=’Microsoft.Exchange.Server.2003.Monitoring’
select rl.rulename,rl.ruleid,md.modulename from rules rl, module md
where md.managementpackid = @mpid
and rl.ruleid=md.parentid
and moduleconfiguration like ‘%<Severity>2</Severity>%’

Rules are stored in a table named Rules. This table has columns linking rules to classes and Management Packs. To find all rules in a Management Pack use the following query and substitute in the required Management Pack name:

SELECT * FROM Rules WHERE ManagementPackID = (SELECT ManagementPackID from ManagementPack WHERE MPName = ‘Microsoft.Windows.Server.2003’) 

To find all rules targeted at a given class use the following query and substitute in the required class name:

SELECT * FROM Rules WHERE TargetManagedEntityType = (SELECT ManagedTypeId FROM ManagedType WHERE TypeName = ‘Microsoft.Windows.Computer’) 

 

Monitors Section:

Monitors Per MP:

SELECT mp.MPName, COUNT(*) As MonitorsPerMPPerCategory
FROM Monitor m
INNER JOIN ManagementPack mp ON mp.ManagementPackID = m.ManagementPackID
GROUP BY mp.MPName
ORDER BY COUNT(*) Desc

To find your Monitor by common name:

select * from Monitor m
Inner join LocalizedText LT on LT.ElementName = m.MonitorName
where LTValue = ‘Monitor Common Name’

To find your Monitor by ID name:

select * from Monitor m
Inner join LocalizedText LT on LT.ElementName = m.MonitorName
where m.monitorname = ‘Monitor ID name’

To find all monitors targeted at a specific class:

SELECT * FROM monitor WHERE TargetManagedEntityType = (SELECT ManagedTypeId FROM ManagedType WHERE TypeName = ‘Microsoft.Windows.Computer’)

 

Groups Section:

To find all groups for a given computer/object (change “computername” in the query below):

SELECT SourceMonitoringObjectDisplayName AS ‘Group’
FROM RelationshipGenericView
WHERE TargetMonitoringObjectDisplayName like (‘%computername%’)
AND (SourceMonitoringObjectDisplayName IN
(SELECT ManagedEntityGenericView.DisplayName
FROM ManagedEntityGenericView INNER JOIN
(SELECT     BaseManagedEntityId
FROM          BaseManagedEntity WITH (NOLOCK)
WHERE      (BaseManagedEntityId = TopLevelHostEntityId) AND (BaseManagedEntityId NOT IN
(SELECT     R.TargetEntityId
FROM          Relationship AS R WITH (NOLOCK) INNER JOIN
dbo.fn_ContainmentRelationshipTypes() AS CRT ON R.RelationshipTypeId = CRT.RelationshipTypeId
WHERE      (R.IsDeleted = 0)))) AS GetTopLevelEntities ON
GetTopLevelEntities.BaseManagedEntityId = ManagedEntityGenericView.Id INNER JOIN
(SELECT DISTINCT BaseManagedEntityId
FROM          TypedManagedEntity WITH (NOLOCK)
WHERE      (ManagedTypeId IN
(SELECT     DerivedManagedTypeId
FROM dbo.fn_DerivedManagedTypes(dbo.fn_ManagedTypeId_Group()) AS fn_DerivedManagedTypes_1))) AS GetOnlyGroups ON
GetOnlyGroups.BaseManagedEntityId = ManagedEntityGenericView.Id))
ORDER BY ‘Group’

To find all members of a given group (change the group name below):

select SourceMonitoringObjectDisplayName as ‘Group Name’,
TargetMonitoringObjectDisplayName as ‘Group Members’
from RelationshipGenericView
where isDeleted=0
AND SourceMonitoringObjectDisplayName = ‘Agent Managed Computer Group
ORDER BY TargetMonitoringObjectDisplayName

Find find the entity data on all members of a given group (change the group name below):

SELECT bme.*
FROM BaseManagedEntity bme
INNER JOIN RelationshipGenericView rgv WITH(NOLOCK) ON bme.basemanagedentityid = rgv.TargetMonitoringObjectid
WHERE bme.IsDeleted = ‘0’
AND rgv.SourceMonitoringObjectDisplayName = ‘Agent Managed Computer Group
ORDER BY bme.displayname

 

 

Management Pack general:

To find all installed Management Packs and their version:

SELECT MPName, MPFriendlyName, MPVersion, MPIsSealed
FROM ManagementPack WITH(NOLOCK)
ORDER BY MPName

Number of Views per Management Pack:

SELECT mp.MPName, v.ViewVisible, COUNT(*) As ViewsPerMP
FROM [Views] v
            INNER JOIN ManagementPack mp ON mp.ManagementPackID = v.ManagementPackID
GROUP BY  mp.MPName, v.ViewVisible
ORDER BY v.ViewVisible DESC, COUNT(*) Desc

How to gather all the views in the database, their ID, MP location, and view type:

select vv.id as ‘View Id’,
vv.displayname as ‘View DisplayName’,
vv.name as ‘View Name’,
vtv.DisplayName as ‘ViewType’,
mpv.FriendlyName as ‘MP Name’
from ViewsView vv
inner join managementpackview mpv on mpv.id = vv.managementpackid
inner join viewtypeview vtv on vtv.id = vv.monitoringviewtypeid
–where mpv.FriendlyName like ‘%default%’
–where vv.displayname like ‘%operating%’
order by mpv.FriendlyName, vv.displayname

Classes available in the DB:

SELECT * FROM ManagedType

Classes available in the DB for Microsoft Windows type:

SELECT * FROM ManagedType WHERE TypeName LIKE ‘Microsoft.Windows.%’  

Every property of every class:

SELECT * FROM MT_Computer 

All instances of all types once discovered

SELECT * FROM BaseManagedEntity

To get the state of every instance of a particular monitor the following query can be run, (replace <MonitorName> with the name of the monitor):

SELECT bme.FullName, bme.DisplayName, s.HealthState FROM state AS s, BaseManagedEntity as bme WHERE s.basemanagedentityid = bme.basemanagedentityid AND s.monitorid IN (SELECT MonitorId FROM Monitor WHERE MonitorName = =<MonitorName>‘)

For example, this gets the state of the Microsoft.SQLServer.2005.DBEngine.ServiceMonitor for each instance of the SQL 2005 Database Engine class.

SELECT bme.FullName, bme.DisplayName, s.HealthState FROM state AS s, BaseManagedEntity as bme WHERE s.basemanagedentityid = bme.basemanagedentityid AND s.monitorid IN (SELECT MonitorId FROM Monitor WHERE MonitorName = ‘Microsoft.SQLServer.2005.DBEngine.ServiceMonitor’) 

To find the overall state of any object in OpsMgr the following query should be used to return the state of the System.EntityState monitor:

SELECT bme.FullName, bme.DisplayName, s.HealthState FROM state AS s, mt_managedcomputer AS mt, BaseManagedEntity as bme WHERE s.basemanagedentityid = bme.basemanagedentityid AND s.monitorid IN (SELECT MonitorId FROM Monitor WHERE MonitorName = ‘System.Health.EntityState’) 

The Alert table contains all alerts currently open in OpsMgr. This includes resolved alerts until they are groomed out of the database. To get all alerts across all instances of a given monitor use the following query and substitute in the required monitor name:

SELECT * FROM Alert WHERE ProblemID IN (SELECT MonitorId FROM Monitor WHERE MonitorName = ‘Microsoft.SQLServer.2005.DBEngine.ServiceMonitor’)

To retrieve all alerts for all instances of a specific class use the following query and substitute in the required table name, in this example MT_DBEngine is used to look for SQL alerts:

SELECT * FROM Alert WHERE BaseManagedEntityID IN (SELECT BaseManagedEntityID from MT_DBEngine)

To determine which table is currently being written to for event and performance data use the following query:

SELECT * FROM PartitionTables WHERE IsCurrent = 1

To retrieve events generated by a specific rule use the following query and substitute in the required rule ID:

SELECT * FROM Event_00 WHERE RuleId = (SELECT RuleId FROM Rules WHERE RuleName = ‘Microsoft.Windows.Server.2003.OperatingSystem.CleanShutdown.Collection ‘)

To retrieve all events generated by rules in a specific Management Pack the following query can be used where the Management Pack name is substituted with the required value:

SELECT * FROM EventAllView WHERE RuleID IN (SELECT RuleId FROM Rules WHERE ManagementPackId = (SELECT ManagementPackId FROM ManagementPack WHERE MPName = ‘Microsoft.Windows.Server.2003’))

Number of instances of a type:  (Number of disks, computers, databases, etc that OpsMgr has discovered) 

SELECT mt.ManagedTypeID, mt.TypeName, COUNT(*) AS NumEntitiesByType
FROM BaseManagedEntity bme WITH(NOLOCK)
            LEFT JOIN ManagedType mt WITH(NOLOCK) ON mt.ManagedTypeID = bme.BaseManagedTypeID
WHERE bme.IsDeleted = 0
GROUP BY mt.ManagedTypeID, mt.TypeName
ORDER BY COUNT(*) DESC

To retrieve all performance data for a given rule in a readable format use the following query: (change the r.RuleName value – get list from Rules Table)

SELECT bme.Path, pc.ObjectName, pc.CounterName, ps.PerfmonInstanceName, pdav.SampleValue, pdav.TimeSampled
FROM PerformanceDataAllView AS pdav with (NOLOCK)
INNER JOIN PerformanceSource ps on pdav.PerformanceSourceInternalId = ps.PerformanceSourceInternalId
INNER JOIN PerformanceCounter pc on ps.PerformanceCounterId = pc.PerformanceCounterId
INNER JOIN Rules r on ps.RuleId = r.RuleId
INNER JOIN BaseManagedEntity bme on ps.BaseManagedEntityID = bme.BaseManagedEntityID
WHERE r.RuleName = ‘Microsoft.Windows.Server.2003.LogicalDisk.FreeSpace.Collection’
GROUP BY PerfmonInstanceName, ObjectName, CounterName, SampleValue, TimeSampled, bme.path
ORDER BY bme.path, PerfmonInstanceName, TimeSampled

To determine what discoveries are still associated with a computer – helpful in finding old stale computer objects in the console that are no longer agent managed, or desired.

select BME.FullName, DS.DiscoveryRuleID, D.DiscoveryName from typedmanagedentity TME
Join BaseManagedEntity BME ON TME.BaseManagedEntityId = BME.BaseManagedEntityId
JOIN DiscoverySourceToTypedManagedEntity DSTME ON TME.TypedManagedEntityID = DSTME.TypedManagedEntityID
JOIN DiscoverySource DS ON DS.DiscoverySourceID = DSTME.DiscoverySourceID
JOIN Discovery D ON DS.DiscoveryRuleID=D.DiscoveryID
Where BME.Fullname like ‘%ComputerName%’

To dump out all the rules and monitors that have overrides, and display the context and instance of the override:

select rv.DisplayName as WorkFlowName, OverrideName, mo.Value as OverrideValue,
mt.TypeName as OverrideScope, bme.DisplayName as InstanceName, bme.Path as InstancePath,
mpv.DisplayName as ORMPName, mo.LastModified as LastModified
from ModuleOverride mo
inner join managementpackview mpv on mpv.Id = mo.ManagementPackId
inner join ruleview rv on rv.Id = mo.ParentId
inner join ManagedType mt on mt.managedtypeid = mo.TypeContext
left join BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext
Where mpv.Sealed = 0
UNION ALL
select mv.DisplayName as WorkFlowName, OverrideName, mto.Value as OverrideValue,
mt.TypeName as OverrideScope, bme.DisplayName as InstanceName, bme.Path as InstancePath,
mpv.DisplayName as ORMPName, mto.LastModified as LastModified
from MonitorOverride mto
inner join managementpackview mpv on mpv.Id = mto.ManagementPackId
inner join monitorview mv on mv.Id = mto.MonitorId
inner join ManagedType mt on mt.managedtypeid = mto.TypeContext
left join BaseManagedEntity bme on bme.BaseManagedEntityId = mto.InstanceContext
Where mpv.Sealed = 0
Order By mpv.DisplayName

 

 

 

 

Agent Info:

To find all managed computers that are currently down and not pingable:

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

All managed computers count: 

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

To find a computer name from a HealthServiceID (guid from the Agent proxy alerts)

select DisplayName, Path, basemanagedentityid from basemanagedentity where basemanagedentityid = ‘guid’

To view the agent patch list (all hotfixes applied to all agents)

select bme.path AS ‘Agent Name’, hs.patchlist AS ‘Patch List’ from MT_HealthService hs
inner join BaseManagedEntity bme on hs.BaseManagedEntityId = bme.BaseManagedEntityId
order by path

To view all agents missing a specific hotfix (change the KB number below to the one you are looking for):

select bme.path AS ‘Agent Name’, hs.patchlist AS ‘Patch List’ from MT_HealthService hs
inner join BaseManagedEntity bme on hs.BaseManagedEntityId = bme.BaseManagedEntityId
where hs.patchlist not like ‘%951380%’
order by path

Here is a query to see all Agents which are manually installed:

select bme.DisplayName from MT_HealthService mths
INNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityId
where IsManuallyInstalled = 1

Here is a query that will set all agents back to Remotely Manageable:

UPDATE MT_HealthService
SET IsManuallyInstalled=0
WHERE IsManuallyInstalled=1

Now – the above query will set ALL agents back to “Remotely Manageable = Yes” in the console.  If you want to control it agent by agent – you need to specify it by name here:

UPDATE MT_HealthService
SET IsManuallyInstalled=0
WHERE IsManuallyInstalled=1
AND BaseManagedEntityId IN
(select BaseManagedEntityID from BaseManagedEntity
where BaseManagedTypeId = ‘AB4C891F-3359-3FB6-0704-075FBFE36710’
AND DisplayName = ‘servername.domain.com’)

Get the instance space of all agents  (Thanks to Hui and Michael Pearson)

DECLARE @RelationshipTypeId_Manages UNIQUEIDENTIFIER
SELECT @RelationshipTypeId_Manages = dbo.fn_RelationshipTypeId_Manages()
SELECT bme.FullName, dt.TopLevelEntityName, dt.BaseEntityName, dt.TypedEntityName
FROM BaseManagedEntity bme
RIGHT JOIN (
SELECT
      HBME.BaseManagedEntityId AS HS_BMEID,
      TBME.FullName AS TopLevelEntityName,
      BME.FullName AS BaseEntityName,
      TYPE.TypeName AS TypedEntityName
FROM BaseManagedEntity BME WITH(NOLOCK)
      INNER JOIN TypedManagedEntity TME WITH(NOLOCK) ON BME.BaseManagedEntityId = TME.BaseManagedEntityId AND BME.IsDeleted = 0 AND TME.IsDeleted = 0
      INNER JOIN BaseManagedEntity TBME WITH(NOLOCK) ON BME.TopLevelHostEntityId = TBME.BaseManagedEntityId AND TBME.IsDeleted = 0
      INNER JOIN ManagedType TYPE WITH(NOLOCK) ON TME.ManagedTypeID = TYPE.ManagedTypeID
      LEFT JOIN Relationship R WITH(NOLOCK) ON R.TargetEntityId = TBME.BaseManagedEntityId AND R.RelationshipTypeId = @RelationshipTypeId_Manages AND R.IsDeleted = 0
      LEFT JOIN BaseManagedEntity HBME WITH(NOLOCK) ON R.SourceEntityId = HBME.BaseManagedEntityId
) AS dt ON dt.HS_BMEID = bme.BaseManagedEntityId
ORDER BY bme.FullName, BaseEntityName

Get the discovered instance count of the top 50 agents (Thanks to Hui and Michael Pearson)

DECLARE @RelationshipTypeId_Manages UNIQUEIDENTIFIER
SELECT @RelationshipTypeId_Manages = dbo.fn_RelationshipTypeId_Manages()
SELECT TOP 50 bme.DisplayName, SUM(1) AS HostedInstances
FROM BaseManagedEntity bme
RIGHT JOIN (
SELECT
      HBME.BaseManagedEntityId AS HS_BMEID,
      TBME.FullName AS TopLevelEntityName,
      BME.FullName AS BaseEntityName,
      TYPE.TypeName AS TypedEntityName
FROM BaseManagedEntity BME WITH(NOLOCK)
      INNER JOIN TypedManagedEntity TME WITH(NOLOCK) ON BME.BaseManagedEntityId = TME.BaseManagedEntityId AND BME.IsDeleted = 0 AND TME.IsDeleted = 0
      INNER JOIN BaseManagedEntity TBME WITH(NOLOCK) ON BME.TopLevelHostEntityId = TBME.BaseManagedEntityId AND TBME.IsDeleted = 0
      INNER JOIN ManagedType TYPE WITH(NOLOCK) ON TME.ManagedTypeID = TYPE.ManagedTypeID
      LEFT JOIN Relationship R WITH(NOLOCK) ON R.TargetEntityId = TBME.BaseManagedEntityId AND R.RelationshipTypeId = @RelationshipTypeId_Manages AND R.IsDeleted = 0
      LEFT JOIN BaseManagedEntity HBME WITH(NOLOCK) ON R.SourceEntityId = HBME.BaseManagedEntityId
) AS dt ON dt.HS_BMEID = bme.BaseManagedEntityId
GROUP by BME.displayname
order by HostedInstances DESC

Instance space per class (not happy with this – includes duplicates for clusters)

DECLARE @RelationshipTypeId_Manages UNIQUEIDENTIFIER
SELECT @RelationshipTypeId_Manages = dbo.fn_RelationshipTypeId_Manages()
SELECT TypedEntityName, SUM(1) AS InstanceCount
FROM BaseManagedEntity bme
RIGHT JOIN (
SELECT
      HBME.BaseManagedEntityId AS HS_BMEID,
      TBME.FullName AS TopLevelEntityName,
      BME.FullName AS BaseEntityName,
      TYPE.TypeName AS TypedEntityName
FROM BaseManagedEntity BME WITH(NOLOCK)
      INNER JOIN TypedManagedEntity TME WITH(NOLOCK) ON BME.BaseManagedEntityId = TME.BaseManagedEntityId AND BME.IsDeleted = 0 AND TME.IsDeleted = 0
      INNER JOIN BaseManagedEntity TBME WITH(NOLOCK) ON BME.TopLevelHostEntityId = TBME.BaseManagedEntityId AND TBME.IsDeleted = 0
      INNER JOIN ManagedType TYPE WITH(NOLOCK) ON TME.ManagedTypeID = TYPE.ManagedTypeID
      LEFT JOIN Relationship R WITH(NOLOCK) ON R.TargetEntityId = TBME.BaseManagedEntityId AND R.RelationshipTypeId = @RelationshipTypeId_Manages AND R.IsDeleted = 0
      LEFT JOIN BaseManagedEntity HBME WITH(NOLOCK) ON R.SourceEntityId = HBME.BaseManagedEntityId
) AS dt ON dt.HS_BMEID = bme.BaseManagedEntityId
GROUP by TypedEntityName
order by InstanceCount DESC

 

 

Misc OpsDB: 

To view grooming info:

SELECT * FROM PartitionAndGroomingSettings WITH (NOLOCK)

Information on existing User Roles:

SELECT UserRoleName, IsSystem from userrole

Operational DB version:

select DBVersion from __MOMManagementGroupInfo__

To view all Run-As Profiles, their associated Run-As account, and associated agent name:

select srv.displayname as ‘RunAs Profile Name’,
srv.description as ‘RunAs Profile Description’,
cmss.name as ‘RunAs Account Name’,
cmss.description as ‘RunAs Account Description’,
cmss.username as ‘RunAs Account Username’,
cmss.domain as ‘RunAs Account Domain’,
mp.FriendlyName as ‘RunAs Profile MP’,
bme.displayname as ‘HealthService’
from dbo.SecureStorageSecureReference sssr
inner join SecureReferenceView srv on srv.id = sssr.securereferenceID
inner join CredentialManagerSecureStorage cmss on cmss.securestorageelementID = sssr.securestorageelementID
inner join managementpackview mp on srv.ManagementPackId = mp.Id
inner join BaseManagedEntity bme on bme.basemanagedentityID = sssr.healthserviceid
order by srv.displayname

To clean up old StateChangeEvent data for state changes that are older than the defined grooming period, such as monitors currently in a disabled, warning, or critical state.  By default we only groom monitor statechangeevents where the monitor is enabled and healthy at the time of grooming.

USE [OperationsManager]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN

    SET NOCOUNT ON

    DECLARE @Err int
    DECLARE @Ret int
    DECLARE @DaysToKeep tinyint
    DECLARE @GroomingThresholdLocal datetime
    DECLARE @GroomingThresholdUTC datetime
    DECLARE @TimeGroomingRan datetime
    DECLARE @MaxTimeGroomed datetime
    DECLARE @RowCount int
    SET @TimeGroomingRan = getutcdate()

    SELECT @GroomingThresholdLocal = dbo.fn_GroomingThreshold(DaysToKeep, getdate())
    FROM dbo.PartitionAndGroomingSettings
    WHERE ObjectName = ‘StateChangeEvent’

    EXEC dbo.p_ConvertLocalTimeToUTC @GroomingThresholdLocal, @GroomingThresholdUTC OUT
    SET @Err = @@ERROR

    IF (@Err <> 0)
    BEGIN
        GOTO Error_Exit
    END

    SET @RowCount = 1  

    — This is to update the settings table
    — with the max groomed data
    SELECT @MaxTimeGroomed = MAX(TimeGenerated)
    FROM dbo.StateChangeEvent
    WHERE TimeGenerated < @GroomingThresholdUTC

    IF @MaxTimeGroomed IS NULL
        GOTO Success_Exit

    — Instead of the FK DELETE CASCADE handling the deletion of the rows from
    — the MJS table, do it explicitly. Performance is much better this way.
    DELETE MJS
    FROM dbo.MonitoringJobStatus MJS
    JOIN dbo.StateChangeEvent SCE
        ON SCE.StateChangeEventId = MJS.StateChangeEventId
    JOIN dbo.State S WITH(NOLOCK)
        ON SCE.[StateId] = S.[StateId]
    WHERE SCE.TimeGenerated < @GroomingThresholdUTC
    AND S.[HealthState] in (0,1,2,3)

    SELECT @Err = @@ERROR
    IF (@Err <> 0)
    BEGIN
        GOTO Error_Exit
    END

    WHILE (@RowCount > 0)
    BEGIN
        — Delete StateChangeEvents that are older than @GroomingThresholdUTC
        — We are doing this in chunks in separate transactions on
        — purpose: to avoid the transaction log to grow too large.
        DELETE TOP (10000) SCE
        FROM dbo.StateChangeEvent SCE
        JOIN dbo.State S WITH(NOLOCK)
            ON SCE.[StateId] = S.[StateId]
        WHERE TimeGenerated < @GroomingThresholdUTC
        AND S.[HealthState] in (0,1,2,3)

        SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT

        IF (@Err <> 0)
        BEGIN
            GOTO Error_Exit
        END
    END   

    UPDATE dbo.PartitionAndGroomingSettings
    SET GroomingRunTime = @TimeGroomingRan,
        DataGroomedMaxTime = @MaxTimeGroomed
    WHERE ObjectName = ‘StateChangeEvent’

    SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT

    IF (@Err <> 0)
    BEGIN
        GOTO Error_Exit
    END 
Success_Exit:
Error_Exit:   
END

 

 

Data Warehouse Database Section:

Alerts Section:

To get all raw alert data from the data warehouse to build reports from:

select * from Alert.vAlertResolutionState ars
inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid

To view data on all alerts modified by a specific user:

select ars.alertguid, alertname, alertdescription, statesetbyuserid, resolutionstate, statesetdatetime, severity, priority, managedentityrowID, repeatcount
from Alert.vAlertResolutionState ars
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
where statesetbyuserid like ‘%username%’
order by statesetdatetime

To view a count of all alerts closed by all users:

select statesetbyuserid, count(*) as ‘Number of Alerts’
from Alert.vAlertResolutionState ars
where resolutionstate = ‘255’
group by statesetbyuserid
order by ‘Number of Alerts’ DESC

Events Section:

To inspect total events in DW, and then break it down per day:  (this helps us know what we will be grooming out, and look for partitcular day event storms)

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), DateTime, 101)) = 1)
THEN ‘All Days’
ELSE CONVERT(VARCHAR(20), DateTime, 101) END AS DayAdded,
COUNT(*) AS NumEventsPerDay
FROM Event.vEvent
GROUP BY CONVERT(VARCHAR(20), DateTime, 101) WITH ROLLUP
ORDER BY DayAdded DESC

Most Common Events by event number:  (This helps us know which event ID’s are the most common in the database)

SELECT top 50 EventDisplayNumber, COUNT(*) AS TotalEvents
FROM Event.vEvent
GROUP BY EventDisplayNumber
ORDER BY TotalEvents DESC

Most common events by event number and raw event description (this will take a very long time to run but it shows us not only event ID – but a description of the event to help understand which MP is the generating the noise)

SELECT top 50 EventDisplayNumber, Rawdescription, COUNT(*) AS TotalEvents
FROM Event.vEvent evt
inner join Event.vEventDetail evtd on evt.eventoriginid = evtd.eventoriginid
GROUP BY EventDisplayNumber, Rawdescription
ORDER BY TotalEvents DESC

To view all event data in the DW for a given Event ID:

select * from Event.vEvent ev
inner join Event.vEventDetail evd on ev.eventoriginid = evd.eventoriginid
inner join Event.vEventParameter evp on ev.eventoriginid = evp.eventoriginid
where eventdisplaynumber = ‘528’

To search for all computers who have NOT logged a specific event in the DW:

select distinct elc.computername from Event.vEvent ev
inner join vEventLoggingComputer elc on elc.eventloggingcomputerrowid = ev.loggingcomputerrowid
where NOT eventdisplaynumber = ‘223’

Performance Section:

Raw data – core query:

select top 10 *
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId

Raw data – More selective of “interesting” output data:

select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId

Raw data – Scoped to a ComputerName (FQDN)

select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE Path = ‘OMDB.opsmgr.net’

Raw data – Scoped to a Counter:

select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE CounterName = ‘Private Bytes’

Raw data – Scoped to a Computer and Counter:

select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE CounterName = ‘Private Bytes’
AND Path = ‘OMDB.OPSMGR.NET’

Raw data – Ordered By DateTime:

select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE CounterName = ‘Private Bytes’
AND Path = ‘OMDB.OPSMGR.NET’
Order By DateTime DESC

Raw data – Modified DateTime relative to Central Time zone:

select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, dateadd(hh,-5,DateTime) as ‘DateTime (GMT-5)’
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE CounterName = ‘Private Bytes’
AND Path = ‘OMDB.OPSMGR.NET’
Order By DateTime DESC

Raw data – How to get all the possible optional data to modify these queries above, in a list:

Select Distinct Path
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId

Select Distinct Fullname
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId

Select Distinct ObjectName
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId

Select Distinct CounterName
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId

Select Distinct InstanceName
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId

 

 

Grooming in the DataWarehouse: 

Grooming no longer uses SQL agent jobs.  Grooming is handled by scheduled stored procedures, that run much more frequently, which provides less impact than in the previous version. 

Default grooming for the DW for each dataset, to examine Data Warehouse grooming settings:

SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes FROM StandardDatasetAggregation

The first row is the interval in minutes.
NULL is raw data, 60 is hourly, and 1440 is daily.
The second and third row shows what data it is
MaxDataAgeDays has the retention period in days – this is the field to update if the administrator wants to lower the days of retention.
RAW alert – 400 days
RAW event – 100 days
RAW perf – 10 days (hourly and daily perf = 400 days)
RAW state – 180 days  (hourly and daily state = 400 days)

Here is a better view of the current data retention in your data warehouse:

select ds.datasetDefaultName AS ‘Dataset Name’, sda.AggregationTypeId AS ‘Agg Type 0=raw, 20=Hourly, 30=Daily’, sda.MaxDataAgeDays AS ‘Retention Time in Days’
from dataset ds, StandardDatasetAggregation sda
WHERE ds.datasetid = sda.datasetid
ORDER by ds.datasetDefaultName

To view the number of days of total data of each type in the DW:

SELECT DATEDIFF(d, MIN(DWCreatedDateTime), GETDATE()) AS [Current] FROM Alert.vAlert
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM Event.vEvent
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM Perf.vPerfRaw
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM Perf.vPerfHourly
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM Perf.vPerfDaily
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM State.vStateRaw
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM State.vStateHourly
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM State.vStateDaily

To view the oldest and newest recorded timestamps of each data type in the DW:

select min(DateTime) from Event.vEvent
select max(DateTime) from Event.vEvent
select min(DateTime) from Perf.vPerfRaw
select max(DateTime) from Perf.vPerfRaw
select min(DWCreatedDateTime) from Alert.vAlert
select max(DWCreatedDateTime) from Alert.vAlert

 

 

AEM Queries (Data Warehouse):

Default query to return all RAW AEM data: 

select * from [CM].[vCMAemRaw] Rw
inner join dbo.AemComputer Computer on Computer.AemComputerRowID = Rw.AemComputerRowID
inner join dbo.AemUser Usr on Usr.AemUserRowId = Rw.AemUserRowId
inner join dbo.AemErrorGroup EGrp on Egrp.ErrorGroupRowId = Rw.ErrorGroupRowId
Inner join dbo.AemApplication App on App.ApplicationRowId = Egrp.ApplicationRowId

Count the raw crashes per day:

SELECT CONVERT(char(10), DateTime, 101) AS "Crash Date (by Day)", COUNT(*) AS "Number of Crashes"
FROM [CM].[vCMAemRaw]
GROUP BY CONVERT(char(10), DateTime, 101)
ORDER BY "Crash Date (by Day)" DESC

Count the total number of raw crashes in the DW database:

select count(*) from CM.vCMAemRaw

Default grooming for the DW for the AEM dataset:  (Aggregated data kept for 400 days, RAW 30 days by default)

SELECT AggregationTypeID, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes
FROM StandardDatasetAggregation WHERE BuildAggregationStoredProcedureName = ‘AemAggregate’

 

 

 

 

 

 

Misc Section:

Simple query to display large tables, to determine what is taking up space in the database:

SELECT so.name,
8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb,
Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb,
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id)
WHERE ‘U’ = so.type GROUP BY so.name  ORDER BY data_kb DESC

Is SQL broker enabled?

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘OperationsManager’

How to identify your version of SQL server:

SELECT  SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

SQL 2005:
SQL Server 2005 RTM                    2005.90.1399
SQL Server 2005 SP1                     2005.90.2047
SQL Server 2005 SP1 plus 918222  2005.90.2153
SQL Server 2005 SP2                     2005.90.3042

How to identify your version of OpsMgr 2007:

RTM:          6.0.5000.0

SP1-RC:     6.0.6246.0

SP1:          6.0.6278.0

To get better performance manually:

Update Statistics (will help speed up reports and takes less time than a full reindex):

EXEC sp_updatestats

Show index fragmentation (to determine how badly you need a reindex – logical scan frag > 10% = bad. Scan density below 80 = bad):

DBCC SHOWCONTIG

DBCC SHOWCONTIG WITH FAST (less data than above – in case you don’t have time)

Reindex the database:

USE OperationsManager
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
EXEC SP_MSForEachTable "Print ‘Reindexing ‘+’?’ DBCC DBREINDEX (‘?’)"

Table by table:

DBCC DBREINDEX (‘TableName’)

Query to view the index job history on domain tables in the databases:

select *
from DomainTable dt
inner join DomainTableIndexOptimizationHistory dti
on dt.domaintablerowID = dti.domaintableindexrowID
ORDER BY optimizationdurationseconds DESC

Query to view the update statistics job history on domain tables in the databases:

select *
from DomainTable dt
inner join DomainTableStatisticsUpdateHistory dti
on dt.domaintablerowID = dti.domaintablerowID
ORDER BY UpdateDurationSeconds DESC

Data Warehouse query to examine the index and statistics history – run the following query for the Alert, Event, Perf, and State tables (these are non-domain tables):

select basetablename, optimizationstartdatetime, optimizationdurationseconds,
      beforeavgfragmentationinpercent, afteravgfragmentationinpercent,
      optimizationmethod, onlinerebuildlastperformeddatetime
from StandardDatasetOptimizationHistory sdoh
inner join StandardDatasetAggregationStorageIndex sdasi
on sdoh.StandardDatasetAggregationStorageIndexRowId = sdasi.StandardDatasetAggregationStorageIndexRowId
inner join StandardDatasetAggregationStorage sdas
on sdasi.StandardDatasetAggregationStorageRowId = sdas.StandardDatasetAggregationStorageRowId
ORDER BY optimizationdurationseconds DESC

Comments (94)

  1. Anonymous says:

    Jonathan Almquist started a blog post about Operations Manager 2007 SQL Queries . “I’m starting a new

  2. Anonymous says:

    I’m starting a new post here, similar to my Command Shell reference .&#160; This will include some useful

  3. Anonymous says:

    This is a continuation of my other post, on general alert grooming: How grooming and auto-resolution

  4. Anonymous says:

    Dontchya hate that?  🙂

    I’d much rather write a new query than re-engineer someone elses and trust it.

    But I myself am no Tsql expert.  Most of these above that I have written are pretty basic joins from simple trables/views.  The complex ones are always something I got from someone else and just published it.  I think the most complex one I ever wrote was the Outage and Maintenance report, and I had to get some help on that one.  Dont feel bad.

  5. Anonymous says:

    I’ve been getting a few questions lately about slow performance with the console so I thought a post

  6. Anonymous says:

    Kevin,

    Need little help here. After updating Scom2007 R2 with CU6 hotfix and approving 'pending agents', i ran the sql query "To view all agents missing a specific hotfix " using your article. Now i see that about 100 agents are missing the hotfix, although 'Pending Management' node in SCOM console does not list them. From your CU6 article I understand that manually installed agents need hotfix to be installed manually.

    Now, if I run the sql query that will set all agents back to Remotely Manageable, will it allow me to update agents with the CU6 hotfix? Your feedback is much appreciated.

    Thanks,

    Fahim,

    SQL quesry:

    UPDATE MT_HealthService

    SET IsManuallyInstalled=0

    WHERE IsManuallyInstalled=1

  7. Anonymous says:

    Re:  SCE

    I cannot say as I dont support SCE, nor have I even seen the product.  🙂

    However – I believe it should… as the DB’s are very similar.  If you can see dbo.AlertView then the error message you are getting is simply because you dont have enough rights, or you are running it against the wrong database…. make sure you are executing it against "OperationsManager" and not "Master"

  8. Anonymous says:

    Ravi – I will answer this here – but I am not sure why you posted this on my SQL query page?

    The problem with your statement is #3 is not a fact.

    The agent *initiates* to communication channel over 5723 TCP, always.  This means the communication BEGINS from the agent, to the management server.  However, once that communication channel is established, the communications is bidirectional.  It is always opened initially by the agent, but from that point forward, the management server can also communicate to the agent.   For the purposes of a firewall, the direction is from the agent to the MS, because the firewall inspects the initial communication channel.

    When the management server/RMS notifies the agent of updated config available – this occurs over 5723 TCP.

  9. Anonymous says:

    Hi Kevin,

    Considering the below facts:

    1. Agent initiates the communication with its reporting Management server.

    2. For Communication we need the TCP 5723 to be opened from Agent to RMS (uni-direction) only.

    3. Agent will pull all the data from its reporting MS. MS never push the data to an Agent.

    My question is:

    Once we do some configuration change, RMS/MS will notify their Agents for this change. On which port this communication happens (considering 5723 is open uni-directional) ?

    I appriciate if you could please shed some light on this.

    regards,

    Ravi

  10. Anonymous says:

    You can get space used by type by using the large table query….  which is why I include and use that one all the time.  It could be made prettier… but it works.  There is also a report in SQL 2005 SP2 for reporting on table by size….  which will show you this.  The tables are named to be fairly self explanatory.

  11. Anonymous says:

    Hi Kevin,

    I used "To find all groups for a given computer/object", but it seems couldn't find the computer which is in the subgroup.

    Thanks.

  12. Anonymous says:

    Re:  Kent

    Kent – there is no 4GB limit that I am aware of.  To what are you speaking?

    Localizedtext filling up is a known issue – this is documented and there are two scripts to clean this up here:  

    http://blogs.technet.com/kevinholman/archive/2008/10/13/does-your-opsdb-keep-growing-is-your-localizedtext-table-using-all-the-space.aspx

  13. Anonymous says:

    Hello,

    I am looking for a List of all Rules and Monitors applied to a group of Computers owning an application… there are file servers, SQL, applications, etc… in the group,

    Also i need the parameters used within each rule/monitor.

    Thanks,

    Dom

  14. Anonymous says:

    Hi Kevin,

    Thanks for your Queries. It helped me a lot and saved my time. Thanks.

    I got a request to pull Memory Utilization (Physical) report for all servers. When I use to pull the report from DW using "% committed bytes in use" counter, I am not getting the expected result. Am I looking correct counter?

    One of my server utilized 99% for 7 hrs but my report shows approximate 55%.

    Please help me if you have any query and correct COUNTER name.

    Thanks,

    Balaji Suresh

  15. Anonymous says:

    Great question!

    There are queries you can use against the opsDB, drawing on the assumption that these are collected for both databases.  This is covered here:

    blogs.technet.com/…/management-group-checkup-a-database-perspective.aspx

    and

    blogs.technet.com/…/tuning-tip-turning-off-some-over-collection-of-events.aspx

  16. Anonymous says:

    Lot of new updates…

  17. Anonymous says:

    Very helpful queries, Kevin!

    Do you by chance have one that shows all alerts filtered to a specified SCOM group? I can find all alerts, and I can find groups, but can not seem to join them together.

    Thanks,

    Patrick

  18. Anonymous says:

    Hi Nayden,

    more than a great script; thank you. Any chance to add information about the fallback MS in this query?

    Thank your very much for such a support.

    Alex

  19. Anonymous says:

    Could you help with a query to retrieve Dell hardware with critical alert state = New.

    Thanks

  20. Anonymous says:

    Re:  Kent

    Kent – you are probably running SQL express – as this is the ONLY version fo SQL with such a limitation.  SQL express is NOT supported.

    http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

    http://technet.microsoft.com/en-us/library/bb309428.aspx

  21. Anonymous says:

    @Pallavi:  Everybody needs that.  Every customer I have worked with to date has asked for that since RTM.  Unfortunately, there is no easy way to get this from a report.  I am confident it could be done, but not with my feeble SQL skills.  We have asked the product teams for this, so I am hoping the next version of SCOM might have something like this built in mind.

  22. Anonymous says:

    How ’bout a query that show space used by type, being alert, event, perf, state for the dw?

  23. Anonymous says:

    Hi Kevin

    Might be a weird question, but why dont i have the EventAllView table in my OpsMgr DW?

    Thanks!

  24. Anonymous says:

    How check someone deleted some group ?

  25. sna says:

    Hi – This is a great post.

    I was wondering if you could help me with a question.

    In the view “PerformanceDataAllView” in the “OperationsManager” database, what do the columns “TimeSampled” and “TimeAdded” mean?

    Thanks,

    Sna

  26. Availability Query says:

    Hello,

    Can you post a query one would run to get availability of a server for a certain time period?  

    Thanks,

    MB

  27. paul keely says:

    what another fantastic resourse!!

  28. Orhan says:

    Hello everyone,

    I am trying to create report something like this,

    User Name              Number of Closed Alerts

    operatorname1             245

    Operatorname2             165

    I am querying the OperationManager Database my query is,

    SELECT     LastModifiedBy AS ‘User Name’, COUNT(*) AS ‘Number of Closed Alerts’

    FROM         AlertView

    WHERE     (ResolutionState = ‘255’)

    GROUP BY LastModifiedBy

    ORDER BY ‘Number of Closed Alerts’

    I am confused little in the alert view table which filed should I use for the date and time parameters.

     if I just want to get the closed alert count by specific user and by specific time is that field going to be "TimeResolved" or which one?

    Thank you.

  29. Orhan says:

    Hi Kevin,

    Your report works fine, but I needed to get last 7 days data which not included in your report because you are getting data from Datawarehouse by default we know Opsmgr keeps 7 days alert data in the OperationManager DB so I figured out from your query and modify it against OperationManager DB.

    My issue was I could not figured out which field should I for data and time parameter. I think I found out,

    Here is the final query I believe this is correct  

    SELECT     LastModifiedBy AS ‘User Name’, COUNT(*) AS ‘Number of Closed Alerts’

    FROM         AlertView

    WHERE     (ResolutionState = ‘255’)

    and TimeResolved between @startdate and @enddate

    GROUP BY LastModifiedBy

    ORDER BY ‘Number of Closed Alerts’

    Now I have another issue, SQL server TimeZone

    SQL server time  4 hours ahead than system time when I pull data from Report manager I am not getting accurate data from database.  Do you know how can we fix this issue?

    Thank you.

  30. Pradeesh says:

    This a great topic

    Thanks

    Pradeesh

  31. Olie says:

    Hi Kevin,

    Thanks for the post, will prove very useful.

    Got an issue when I run a few of the queries though. I reveive an error stating that PerformanceDataAllView is an invalid object name.

    Any ideas?

    Thanks,

    Olie

  32. Olie says:

    Hi Kevin,

    Please ignore my last post regarding PerfromanceDataAllView as I have managed to resolve the issue.

    Thanks again for the scripts… very handy!

    Olie

  33. srikanth says:

    How to get InternetInformationServices information from operations manager databse

  34. Nayden says:

    Here is one I found useful (I am sure it can be modified and improved, but this one works for me). It returns the Name, Action Account Identity, Install Date, Version, Health State, Availability (the greyed out icon state when the agent is not reporting back to the MS), and the MS it reports to. Some stuff is commented out, you can include it if you want…

    SELECT

    –[MT_C].[BaseManagedEntityId] AS [Id],

    [MT_C].[PrincipalName] AS [Name],

    –[MT_C].[NetbiosDomainName],

    –[MT_C].[NetbiosComputerName],

    –[MT_C].[NetworkName],

    [MT_H].[ActionAccountIdentity],

    –[MT_H].[InstalledBy],

    [MT_H].[InstallTime],

    –[MT_H].[ProxyingEnabled],

    –[MT_H].[IsManuallyInstalled],

    –[MT_H].[ActiveDirectoryManaged],

    [MT_H].[Version],

    [S].[HealthState],

    CASE

    WHEN MAX(CAST(A.[IsAvailable] AS int)) IS NULL

    THEN CAST(0 AS BIT)

    ELSE CAST(MAX(CAST(A.[IsAvailable] AS int)) AS BIT)

    END

    AS IsAvailable,

    –[BME].[BaseManagedEntityId] AS [HealthServiceId],

    –[TME].[ManagedTypeId] AS [HealthServiceTypeId],

    [BME2].[DisplayName] AS [ReportingTo]

    –[BME2].[TopLevelHostEntityId] AS [TargetHostId],

    –[HSC].[RelationshipTypeId] AS [CommunicationTypeId],

    –[BME].[LastModified]

    FROM dbo.BaseManagedEntity (nolock)

    AS BME  

    INNER JOIN dbo.TypedManagedEntity AS TME       ON BME.[BaseManagedEntityId] = TME.[BaseManagedEntityId]  

    INNER JOIN dbo.Relationship AS HSC       ON HSC.[SourceEntityId] = BME.[BaseManagedEntityId]  

    INNER JOIN dbo.BaseManagedEntity AS BME2       ON BME2.[BaseManagedEntityId] = HSC.[TargetEntityId]  

    INNER JOIN dbo.MT_Computer AS MT_C       ON MT_C.[BaseManagedEntityId] = BME.[TopLevelHostEntityId]  

    INNER JOIN dbo.MT_HealthService AS MT_H       ON MT_H.[BaseManagedEntityId] = BME.[BaseManagedEntityId]  

    LEFT OUTER JOIN dbo.State AS S ON BME.[BaseManagedEntityId] = S.[BaseManagedEntityId]

    AND S.[MonitorId] = dbo.fn_ManagedTypeId_SystemHealthEntityState()

    LEFT OUTER JOIN dbo.Relationship AS R ON BME.[TopLevelHostEntityId] = R.[TargetEntityId]

    AND R.[RelationshipTypeId] = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceManagesEntity()

    AND R.[IsDeleted] = 0

    LEFT OUTER JOIN dbo.Availability AS A ON A.[BaseManagedEntityId] = R.[SourceEntityId]  

    WHERE (( BME.[LastModified] >  ‘Jul 1 1999 1:24:55:063PM’ ) )

    AND (((TME.ManagedTypeId = ‘118C0F18-7A70-5F57-3A9B-EEAF1705B3FC’)

    AND (HSC.[RelationshipTypeId] = ‘37848E16-37A2-B81B-DAAF-60A5A626BE93’)

    AND (BME.[IsDeleted] = 0

    AND TME.[IsDeleted] = 0

    AND HSC.[IsDeleted] = 0)))

    GROUP BY BME.[BaseManagedEntityId], MT_C.[BaseManagedEntityId], MT_C.[PrincipalName],

    MT_C.[NetbiosDomainName],

    MT_C.[NetbiosComputerName],

    MT_C.[NetworkName],

    MT_H.[ActionAccountIdentity],

    MT_H.[InstalledBy],

    MT_H.[InstallTime],

    MT_H.[ProxyingEnabled],

    MT_H.[IsManuallyInstalled],

    MT_H.[ActiveDirectoryManaged],

    MT_H.[Version],

    S.[HealthState],

    TME.[ManagedTypeId],

    BME2.[DisplayName],

    BME2.[TopLevelHostEntityId],

    HSC.[RelationshipTypeId],

    BME.[LastModified]

    order by Name asc

  35. Nayden says:

    Well, I am not sure whether it is possible or not – I myself am not a SQL expert… Since I could not find this info anywhere (documentation or via PowerShell) my last resort was to run a trace on the DB and reverse-engineer the query out of the thousands of lines of output. Sorry I couldn’t be of more help.

  36. kldsts says:

    How can i get the total no of alerts raised for a specifically OS related  for a month. ?

    How can i get the total no of notification send from my scom server to the notified groups ?

  37. james says:

    Hi

    Is it possible to have a query that shows the number of alerts per day by a group of servers?

    I have a number of groups, ie DomainA, DomainB, DomainC etc so would like to report the number of alerts generated by group with also the number of days to be a variable

  38. Jasmine says:

    I am trying to write a query to display alert history which users might have update on certain alerts under alert properties / History. I have no idea which table I should call as I could not find a table which stores this data. Any advise?

  39. Steve Tolbert says:

    Is there a query to retrieve all rules and monitors running on a given host including overrides?

    I am trying to do something similar to the effective configuration viewer but I would prefer to do it in sql.

  40. Kent Grundström says:

    Hmm… Something that I need is a way to shrink the LokalizedText table, it contains >2 million records making the SQL-server hit the 4Gb limit

  41. Kent Grundström says:

    Re: kevinhol

    Thanks for the info and the pointer!

    The server itself says there is; "CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database" event ID 1827

  42. Kent Grundström says:

    Re: kevinhol

    Ahh… Yes you are right, it is, that is the version with the EBS, I’ve tried to replace it with a better version some time ago, but then the "normal" version was not runnable on the management server…

    BTW, the scripts worked like a breeze and now everything runs like a kittens purr, so thanks again for the tip…

  43. Amal says:

    some queries need to be updated due to change in the names of the fields

    for example:

    SourceMonitoringObjectDisplayName has been changes to SourceObjectDisplayName

  44. Niel says:

    Hi,

    I ma looking for a query to give me a report as follow:

    Computername|ManagementPack|Rule and/or |Monitor

    Anything will help?

    Thanks

    Niel

  45. Tom Kasmir says:

    Will these work against a System Center 2007 Essentials SQL database?  Some queries do for me, some don’t.

    Example of a non-working one from "Top 20 Alerts in an Operational Database, by Alert Count"

    SELECT TOP 20 SUM(1) AS AlertCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name

    FROM Alertview WITH (NOLOCK)

    WHERE TimeRaised is not NULL

    GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name

    ORDER BY AlertCount DESC

    :

    I get – Msg 208, Level 16, State 1, Line 1

    Invalid object name ‘Alertview’.

    I’m doing this in the SQL Mgmt. Studio, and I can see dbo.AlertView table under VIEWS which is under the OPERATIONSMANAGER database.

    – thank you

  46. Tom Kasmir says:

    Thanks. That’s it.

    All I had to do was add USE OperationsManager to the top of the query. Due to my lack of familiarity with SQL querying I assumed wrongly that just highlighting a database on the left would be sufficient.

  47. Wolfgang says:

    Hi Kevin,

    I`m looking for a query to retrieve informations about clusters.

    Nodename + Clustername

    Could you help me?

  48. Hi Kevin,

    How we can find the list of greyed out agents? any SQL query for this?

    Thanks

  49. Pallavi G says:

    Hi Kevin,

    I need a help. I want a report which gives me all the monitors and rules which are associated with one computer.

    Thanks and regards,

    Pallavi

  50. Pallavi G says:

    Yes I tried couple of combinations and there doesn’t seem to be a common thread which will link the tables together. Thanks for your efforts of answering my query.

  51. Sven Wells says:

    I am trying to write a query against the DW db on SCOM 2007 SP1 for listing all the servers that have a CPU Utilization of equal-to-or-less-than 5% for a certain timespan.  I'm also trying to write a query for equal-to-or-greater-than 95%, also for a certain timespan.

    Can anyone help with that?

  52. Ravi says:

    I was little confused about the section where I should post this query. So I choose randomly. Sorry about that.

    Thanks to clarify the doubt.

    regards,

    Ravi

  53. Wilson W. says:

    Hi Kevin, I tried using the SQL query to find out what is causing my Datawarehouse database to be so huge.  I ran the query and got the tables back…..what I'm not clear about is what i do with this information now?  For example, one of the values returns that is apparently taking up a lot of space is:

    EventDetail_B20DD394FCE54972ADC7542596500DF6

    Is this a specific table that I should be looking for?  How to I correlate this information with the actual rule/monitor that is causing the data overhead?

  54. AnExtermist says:

    AnExtermist says:

    Hi Kevin,

    Need your Immediate Assistance with Pulling Out Reports from SCOM Pertaining to User Logon.., I have gone through your blog “Domain User Logon”.., However I am Facing Problems with it as i am not able to see the options in the report builder the ones you have mentioned to be able to pull out reports..,

    My Setup –

    One Dedicated Server for SCOM – ( SCOM 2007 R2 CU 3 running Windows 2008 R2 Ent)

    One Dedicated Server for SQL- ( SCOM Database with ACS Database )

    Monitoring approx 60 Domain Controllers with it.., Basically collecting Security Logs from Every Domain Controller into this ACS Database..!

    Now this is My Requirement as far as Reports are Concerned:

    I have got the following request for a report.

    All workstations for a Region and all users that logged on to them, and the dc at which this was logged.

    Produced in csv/xlsx format with time stamp” as it adds knowledge helping to tie a computer to a user.

    This means all workstation located bellow the following

    ou: “OU=XYZ,OU=Workstations,OU=Resources,DC=Contoso,DC=com”

    They want these sort of reports on a weekly basis sent to them.

    Is this possible to create?

    PLEASE DO REPLY ASAP..!

  55. AnExtermist says:

    Basically I want to Pull Out Reports for One User or For Multiple Users Located anywhere in the domain to Find out:

    1. What All Computers did they Logon on to

    2. Number of times they Logged ON (Interactive, Network, Service, Batch, Unlock, RemoteInteractive, CachedCreds..)

    3. Which Domain Controller Authenticated them.!

    I have used reportbuilder.exe the one that comes with SQL Server 2008 R2 Std. Edition as well as i have installed the latest ReportBuilder 3.0 as well to check if this helps in pulling out the details mentioned above..,

    I have successfully created a Report Model Project to be able to use ReportBuilder.exe.., I have done this for both OperationsManagerAC as well as OperationsManagerDW.., I tried following the steps mentioned in your blog to be able to pull out reports.., but looks like i am novice at this.., seeking spoon feeding at this point in time..! as i was not able to see all the options mentioned there..!

    Hope this info helps you u in understanding my requirement…, if it does..!

    PLEASE HELP ME with this..! DO REPLY SOON.!

  56. narendra says:

    To find a particular agent assinged to which mangement group and mangement server.

    uspgetagentassignment 'agentname'

    in the same way,  can we "find the rules and monitors and other stuff applied on a particular agentserver". do we have any Sql query for this..Please

  57. Dominique says:

    Hello Kevin,

    I change the grooming MaxDataAgeDays and would like to know how to apply it (or when will it be effective and taking in ). As I decrease the number of days i would like to decrease the size of the OperationsManagerDW afterwards.

    Thanks,

    DOm

  58. shaji says:

    Hi kevin,

    I need a help! Can I've a query to find the total downtime in minutes for a particular computer/server for a particular period (for a month or for a year)?

    Thanks,

    Shaji

  59. Brantley Daughtry says:

    Outstanding Collection!

  60. Laurence Vandeyar says:

    Kevin- Do you have an update for SCOM 2012 as the DB Schema has changed? I need a group membership query.

    http://www.systemcentercentral.com/…/Default.aspx

  61. JosephAB says:

    Wonderful article. Could you also help with a query to retrieve all memeber names of the user roles under the security?

  62. SCOM 2007 R2 - SQL Query for Web Application State says:

    Does anyone have the SQL Query to get Web Application state from the Live Database and SQL Query I can execute which will return eaxt same information as the Web Application State under monitoring in the Console.

  63. scom 2007 says:

    Does any one have a query to find all rules that are applied to a particular computer in scom 2007

  64. To pull Alerts within a given time of creation from DWH says:

    I was trying to pull data including alert Name limited to certain time of my choice and came up with below, hope it helps someone else too

    select ars.alertguid,ars.DWCreatedDateTime, alertname, alertdescription, statesetbyuserid, resolutionstate, statesetdatetime, severity, priority, managedentityrowID, repeatcount

    from Alert.vAlertResolutionState ars

    inner join Alert.vAlert alt on ars.alertguid = alt.alertguid

    where ( (ars.DWCreatedDateTime > '2012-10-01 00:00:01.000') and (ars.DWCreatedDateTime < '2012-10-31 23:59:59.999'))

    order by statesetdatetime

  65. Tommy says:

    How to get all alerts from a specified period of time?

    This query works, but does not give me the objects/path and such;

    SELECT *

    FROM Alert WITH (NOLOCK)

    WHERE TimeAdded >= DATEADD(day,-7, GETDATE())

  66. Shashikanth says:

    Please share the script to generate the report for Alerts raised by a specific Managment pack

  67. Anonymous says:

    我一直在与很多用户进行交谈,包括(在 MMS、TechED 和很多其他场合)在线交谈和当面交谈,随着用户计划部署 System Center 2012 – Operations Manager

  68. Dominique says:

    Hello,
    Is it possible to have a view or report which list each SQL Instances on a SQL Cluster with the memory allocated to each instance?

    Thanks,
    Dom

  69. FTZ says:

    Hello,
    I’m trying to create a report about the average of CPU(70%),Disk(60%) and Ram(70%) of network,windows and linux devices ,Do any one have any helpful queries?
    Thanks
    DTZ

  70. DSS says:

    Hi Kevin,

    can anyone help me to get the querie to find the server name for the top SCOM alerts

    Thanks
    DSS

  71. nico says:

    Hi Kevin, I am experiencing an error using a query: SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes FROM StandardDatasetAggregation

    It says Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘StandardDatasetAggregation’.

    I see the table dbo.StandardDataSetAggregation.

  72. Gary Barber says:

    Is there a query that will list all of the Monitors and Rules that will generate an alert, along with the Severity and Priority that also takes into account whether or not there are overrides in place that may have changed the Severity or Priority or turn the monitor / rule off entirely?

  73. Vinoth says:

    Hi Kevin,

    I unable to find Mail Latency (Send/ Receive) Data in SCOM 2012 SQL Query.

    need your help to find SQL query that fetches below details

    1. Average Time for Accessing Mailbox.

    2. Average Time to Send and Receive Mail.

    Regards,
    Vinoth Kumar.

  74. Vinoth says:

    Hi Kevin,

    I unable to find Mail Latency (Send/ Receive) Data in SCOM 2012 SQL Query.

    need your help to find SQL query that fetches below details

    1. Average Time for Accessing Mailbox.

    2. Average Time to Send and Receive Mail.

    Regards,
    Vinoth Kumar.

  75. Scott Tiger says:

    I want to get the sql query for
    1>Data Mail Latency (Send/ Receive) … Means we need average time to take for the mail to send and receive. And how frequently mail box has been accessed.
    from scom server, but after numerous tried I am failed to retrieve that. Help me.
    Thanks in Advance.

  76. Nithin KG says:

    We have SCOM 2012 R2 in our envirnment.
    Our clients have shared a list of servers and it’s big list to do by hand.
    I need to write an sql querry which determine which all have SCOM agent installed and which all have not .
    Can anyone help me on this?

    Regards,
    Nithin KG

  77. Pritam says:

    Hi Kevin, could you give a query for giving the total alert data(including alert name + alert description) from SCOM DB/DW for last 3 months ?

  78. Tech SCOM says:

    Hi,
    Do we have any query for all alerts for a specific management pack with severity?

  79. kyleolson says:

    Can someone help me with a query.. I am trying to tie into the groups that I have created.. I have the view for alerts, but I want to tie them back to groups in the Operations Database…

  80. Anonymous says:

    Kevin Holman has already published the SCOM SQL bible –
    http://blogs.technet.com/b/kevinholman/archive

  81. Clifford2 says:

    Hi, would like to know the SQL used to show the time the alert cleared from console
    i have a query to show the alert description, incident number and alert path and raised alert , but how to add so that it shows the time alert cleared as well

  82. Dave says:

    Is there a query for finding out what administrator removed an object from SCOM 2012 r2?

  83. Kevin Holman says:

    @Dave – unfortunately – SCOM does not log administrator actions for auditing.

  84. Anonymous says:

    ~ Scott Walker | Senior Support Escalation Engineer In the first installment in this series, I discussed

  85. Sean says:

    Can you send me query to pull Mp for particular computer

  86. Sergey says:

    If you want to find the Rule ID, just add "RuleId" table, then using Get-SCOMRule -id "ID Number" we can find the problem rule and override it.

    select top 10 Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled, RuleId

    from PerformanceDataAllView pdv with (NOLOCK)
    inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid

    inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId

    where objectname = ‘Network Interface’ AND
    countername = ‘Bytes Sent/sec’
    order by timesampled

  87. hi,

    I have a simple query that calculate the number of connection to database i.e

    SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame

    Now how can I run this query in SCOM 2012 R2 .
    Do I have to make new rule or new monitor for it.
    How to convert this query into script and run it.

    Can you show me how to execute this query to get the number of connection,please

    Thanks ,

  88. Naeblis says:

    Kevin,

    you can update all the queries with the following to help with local time, since SCOM records all time in UTC,

    declare @tmz int
    set @tmz = DATEDIFF(HOUR,getutcdate(), getdate())

    then in the body of the query,
    dateadd(hour,@tmz,TimeAdded)

    for example the query ‘Number of console Alerts per Day:’

    declare @tmz int
    set @tmz = DATEDIFF(HOUR,getutcdate(), getdate())

    SELECT CONVERT(VARCHAR(20), dateadd(hour,@tmz,TimeAdded), 102) AS DayAdded, COUNT(*) AS NumAlertsPerDay
    FROM Alert WITH (NOLOCK)
    WHERE TimeRaised is not NULL
    GROUP BY CONVERT(VARCHAR(20), dateadd(hour,@tmz,TimeAdded), 102)
    ORDER BY DayAdded DESC

  89. Dexter says:

    Hi,
    I am looking for a query to pull out the availability and response time of website or webapplication. And is there any way to find the matching sql query for the reports that we pull from Reporting section in SCOM.
    Thanks in advance.

  90. Gregg says:

    We have the PKI MP installed and looking for a SQL query that will list all of the valid certificates (these are found in the Certificates-Valid state view. I need to export the list of all valid certificates that SCOM has discovered and is monitoring.