WordPress.com



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.'

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.' 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:



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 '%50%'

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 '%2%'

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.puter') 

 

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.puter')

 

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 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 = =‘)

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.puterDown')

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 = LevelHostEntityID

WHERE bme2.IsDeleted = 0

            AND bme2.IsDeleted = 0

            AND bme2.BaseManagedTypeID = (SELECT TOP 1 ManagedTypeID FROM ManagedType WHERE TypeName = 'microsoft.puter')

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.')

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, LevelEntityName, 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 LevelHostEntityId = 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 LevelHostEntityId = 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 LevelHostEntityId = 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 putername 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.'

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.'

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.'

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.'

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

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download