SCOM SQL queries


 

These queries work for SCOM 2012 and SCOM 2016.  Updated 12/11/2016

 

 

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)

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

--Database Size and used space. --this will show the DB and log file size plus the used/free space in each 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 Queries:

 

Alerts Section (OperationsManager DB):

 

Number of console Alerts per Day:

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

--Top 20 Alerts in an Operational Database, by Alert Count SELECT TOP 20 SUM(1) AS AlertCount, AlertStringName AS 'AlertName', AlertStringDescription AS 'Description', Name, MonitoringRuleId FROM Alertview WITH (NOLOCK) WHERE TimeRaised is not NULL GROUP BY AlertStringName, AlertStringDescription, Name, MonitoringRuleId ORDER BY AlertCount DESC

 

Top 20 Alerts in an Operational Database, by Repeat Count

--Top 20 Alerts in an Operational Database, by Repeat Count SELECT TOP 20 SUM(RepeatCount+1) AS RepeatCount, AlertStringName as 'AlertName', AlertStringDescription as 'Description', Name, MonitoringRuleId FROM Alertview WITH (NOLOCK) WHERE Timeraised is not NULL GROUP BY AlertStringName, AlertStringDescription, Name, MonitoringRuleId ORDER BY RepeatCount DESC

 

Top 20 Objects generating the most Alerts in an Operational Database, by Repeat Count

--Top 20 Objects generating the most Alerts in an Operational Database, by Repeat Count SELECT TOP 20 SUM(RepeatCount+1) AS RepeatCount, MonitoringObjectPath AS 'Path' FROM Alertview WITH (NOLOCK) WHERE Timeraised is not NULL GROUP BY MonitoringObjectPath ORDER BY RepeatCount DESC

 

 

Top 20 Objects generating the most Alerts in an Operational Database, by Alert Count

--Top 20 Objects generating the most Alerts in an Operational Database, by Alert Count SELECT TOP 20 SUM(1) AS AlertCount, MonitoringObjectPath AS 'Path' FROM Alertview WITH (NOLOCK) WHERE TimeRaised is not NULL GROUP BY MonitoringObjectPath ORDER BY AlertCount DESC

 

Number of console Alerts per Day by Resolution State:

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

 

 

Events Section (OperationsManager DB):

 

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)

--All Events by count by day, with total for entire database 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 and event source: (This gives us the event source name to help see what is raising these events)

--Most common events by event number and event source 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

 

Computers generating the most events:

--Computers generating the most events SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents FROM EventallView with (NOLOCK) GROUP BY LoggingComputer ORDER BY TotalEvents DESC

 

 

Performance Section (OperationsManager DB):

 

Performance insertions per day:

--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:  (This shows us which counters are likely overcollected or have duplicate collection rules, and filling the databases)

--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 data collected for a given computer:

--To view all performance insertions for a given computer: select Distinct Path, ObjectName, CounterName, InstanceName 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 = 'sql2a.opsmgr.net' order by objectname, countername, InstanceName

 

To pull all perf data for a given computer, object, counter, and instance:

--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 = 'sql2a.opsmgr.net' AND objectname = 'LogicalDisk' AND countername = 'Free Megabytes' order by timesampled DESC

 

 

 

State Section:

 

To find out how old your StateChange data is:

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

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

 

State changes per day:

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

--Noisiest monitors changing state in the database in the last 7 days: SELECT DISTINCT TOP 50 count(sce.StateId) as StateChanges, m.DisplayName as MonitorName, m.Name as MonitorId, 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 StateChanges desc

 

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

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

 

 

 

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' --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 with a given alert severity: declare @mpid as varchar(50) select @mpid= managementpackid from managementpack where mpName='Microsoft.SystemCenter.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 '%<Severity>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.SystemCenter.2007') --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 = 'your 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 members of a given group (change the group name below): select TargetObjectDisplayName as 'Group Members' from RelationshipGenericView where isDeleted=0 AND SourceObjectDisplayName = 'All Windows Computers' ORDER BY TargetObjectDisplayName --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.TargetObjectId WHERE bme.IsDeleted = '0' AND rgv.SourceObjectDisplayName = 'All Windows Computers' ORDER BY bme.displayname --To find all groups for a given computer/object (change “computername” in the query below): SELECT SourceObjectDisplayName AS 'Group' FROM RelationshipGenericView WHERE TargetObjectDisplayName like ('%sql2a.opsmgr.net%') AND (SourceObjectDisplayName 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'

 

Management Pack and Instance Space misc queries:

--To find all installed Management Packs and their version: SELECT Name AS 'ManagementPackID', FriendlyName, DisplayName, Version, Sealed, LastModified, TimeCreated FROM ManagementPackView WHERE LanguageCode = 'ENU' OR LanguageCode IS NULL ORDER BY DisplayName --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 count(*) FROM ManagedType --Total BaseManagedEntities SELECT count(*) FROM BaseManagedEntity --To get the state of every instance of a particular monitor the following query can be run, (replace <Health Service Heartbeat Failure> 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 Id FROM MonitorView WHERE DisplayName = 'Health Service Heartbeat Failure') --For example, this gets the state of the Microsoft.SQLServer.2012.DBEngine.ServiceMonitor for each instance of the SQL 2012 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.2012.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, 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.2012.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_Microsoft$SQLServer$2012$DBEngine is used to look for SQL alerts: SELECT * FROM Alert WHERE BaseManagedEntityID IN (SELECT BaseManagedEntityID from MT_Microsoft$SQLServer$2012$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 --Number of instances of a type: (Number of disks, computers, databases, etc that OpsMgr has discovered) SELECT 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.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.6.2.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 '%SQL2A%' --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 --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 --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 discovered instance count of the top 50 agents 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

 

Misc OpsDB:

--To get all the OperationsManager configuration settings from the database: SELECT ManagedTypePropertyName, SettingValue, mtv.DisplayName, gs.LastModified FROM GlobalSettings gs INNER JOIN ManagedTypeProperty mtp on gs.ManagedTypePropertyId = mtp.ManagedTypePropertyId INNER JOIN ManagedTypeView mtv on mtp.ManagedTypeId = mtv.Id ORDER BY mtv.DisplayName --To view grooming info: SELECT * FROM PartitionAndGroomingSettings WITH (NOLOCK) --GroomHistory select * from InternalJobHistory order by InternalJobHistoryId DESC --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 --Config Service logs SELECT * FROM cs.workitem ORDER BY WorkItemRowId DESC --Config Service Snapshot history SELECT * FROM cs.workitem WHERE WorkItemName like '%snap%' ORDER BY WorkItemRowId DESC

 

My Workspace Views:

SELECT MyWSViews.UserSid, MyWSViews.SavedSearchName, VT.ViewTypeName, VT.ManagementPackId, MyWSViews.ConfigurationXML FROM [OperationsManager].[dbo].[SavedSearch] AS MyWSViews INNER JOIN [OperationsManager].[dbo].[ViewType] AS VT ON MyWSViews.ViewTypeId=VT.ViewTypeId WHERE MyWSViews.TargetManagedTypeId is not NULL

 

 

 

 

 

Data Warehouse Database Queries:

 

Alerts Section (Warehouse):

--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 (Warehouse):

--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 = '6022'

 

Performance Section (Warehouse):

--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 = 'sql2a.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 like '%op%' --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:

--Here is a 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'

 

Aggregations and Config churn queries for the Warehouse:

--/* Top Noisy Rules in the last 24 hours */ select ManagedEntityTypeSystemName, DiscoverySystemName, count(*) As 'Changes' from (select distinct MP.ManagementPackSystemName, MET.ManagedEntityTypeSystemName, PropertySystemName, D.DiscoverySystemName, D.DiscoveryDefaultName, MET1.ManagedEntityTypeSystemName As 'TargetTypeSystemName', MET1.ManagedEntityTypeDefaultName 'TargetTypeDefaultName', ME.Path, ME.Name, C.OldValue, C.NewValue, C.ChangeDateTime from dbo.vManagedEntityPropertyChange C inner join dbo.vManagedEntity ME on ME.ManagedEntityRowId=C.ManagedEntityRowId inner join dbo.vManagedEntityTypeProperty METP on METP.PropertyGuid=C.PropertyGuid inner join dbo.vManagedEntityType MET on MET.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId inner join dbo.vManagementPack MP on MP.ManagementPackRowId=MET.ManagementPackRowId inner join dbo.vManagementPackVersion MPV on MPV.ManagementPackRowId=MP.ManagementPackRowId left join dbo.vDiscoveryManagementPackVersion DMP on DMP.ManagementPackVersionRowId=MPV.ManagementPackVersionRowId AND CAST(DefinitionXml.query('data(/Discovery/DiscoveryTypes/DiscoveryClass/@TypeID)') AS nvarchar(max)) like '%'+MET.ManagedEntityTypeSystemName+'%' left join dbo.vManagedEntityType MET1 on MET1.ManagedEntityTypeRowId=DMP.TargetManagedEntityTypeRowId left join dbo.vDiscovery D on D.DiscoveryRowId=DMP.DiscoveryRowId where ChangeDateTime > dateadd(hh,-24,getutcdate()) ) As #T group by ManagedEntityTypeSystemName, DiscoverySystemName order by count(*) DESC --/* Modified properties in the last 24 hours */ select distinct MP.ManagementPackSystemName, MET.ManagedEntityTypeSystemName, PropertySystemName, D.DiscoverySystemName, D.DiscoveryDefaultName, MET1.ManagedEntityTypeSystemName As 'TargetTypeSystemName', MET1.ManagedEntityTypeDefaultName 'TargetTypeDefaultName', ME.Path, ME.Name, C.OldValue, C.NewValue, C.ChangeDateTime from dbo.vManagedEntityPropertyChange C inner join dbo.vManagedEntity ME on ME.ManagedEntityRowId=C.ManagedEntityRowId inner join dbo.vManagedEntityTypeProperty METP on METP.PropertyGuid=C.PropertyGuid inner join dbo.vManagedEntityType MET on MET.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId inner join dbo.vManagementPack MP on MP.ManagementPackRowId=MET.ManagementPackRowId inner join dbo.vManagementPackVersion MPV on MPV.ManagementPackRowId=MP.ManagementPackRowId left join dbo.vDiscoveryManagementPackVersion DMP on DMP.ManagementPackVersionRowId=MPV.ManagementPackVersionRowId AND CAST(DefinitionXml.query('data(/Discovery/DiscoveryTypes/DiscoveryClass/@TypeID)') AS nvarchar(max)) like '%'+MET.ManagedEntityTypeSystemName+'%' left join dbo.vManagedEntityType MET1 on MET1.ManagedEntityTypeRowId=DMP.TargetManagedEntityTypeRowId left join dbo.vDiscovery D on D.DiscoveryRowId=DMP.DiscoveryRowId where ChangeDateTime > dateadd(hh,-24,getutcdate()) ORDER BY MP.ManagementPackSystemName, MET.ManagedEntityTypeSystemName --Aggregation history USE OperationsManagerDW; WITH AggregationInfo AS ( SELECT AggregationType = CASE WHEN AggregationTypeId = 0 THEN 'Raw' WHEN AggregationTypeId = 20 THEN 'Hourly' WHEN AggregationTypeId = 30 THEN 'Daily' ELSE NULL END ,AggregationTypeId ,MIN(AggregationDateTime) as 'TimeUTC_NextToAggregate' ,COUNT(AggregationDateTime) as 'Count_OutstandingAggregations' ,DatasetId FROM StandardDatasetAggregationHistory WHERE LastAggregationDurationSeconds IS NULL GROUP BY DatasetId, AggregationTypeId ) SELECT SDS.SchemaName ,AI.AggregationType ,AI.TimeUTC_NextToAggregate ,Count_OutstandingAggregations ,SDA.MaxDataAgeDays ,SDA.LastGroomingDateTime ,SDS.DebugLevel ,AI.DataSetId FROM StandardDataSet AS SDS WITH(NOLOCK) JOIN AggregationInfo AS AI WITH(NOLOCK) ON SDS.DatasetId = AI.DatasetId JOIN dbo.StandardDatasetAggregation AS SDA WITH(NOLOCK) ON SDA.DatasetId = SDS.DatasetId AND SDA.AggregationTypeID = AI.AggregationTypeID ORDER BY SchemaName DESC

 

Analyzing Dataset data in the DW:

--Rules creating the most inserts select TOP(30) vr.RuleSystemName, count (*) AS 'count' from [Perf].[PerfHourly_99D5C26784F74BA0B17D726400D58097] ph INNER JOIN PerformanceRuleInstance pri on ph.PerformanceRuleInstanceRowId = pri.PerformanceRuleInstanceRowId INNER JOIN vRule vr on pri.RuleRowId = vr.RuleRowId GROUP BY vr.RuleSystemName Order by count DESC --Instances with the most perf inserts select TOP(30) vme.FullName, count (*) AS 'count' from [Perf].[PerfHourly_99D5C26784F74BA0B17D726400D58097] ph INNER JOIN vManagedEntity vme on ph.ManagedEntityRowId = vme.ManagedEntityRowId GROUP BY vme.FullName Order by count DESC --Instance types with the most perf inserts select TOP(30) vmet.ManagedEntityTypeSystemName, count (*) AS 'count' from [Perf].[PerfHourly_99D5C26784F74BA0B17D726400D58097] ph INNER JOIN vManagedEntity vme on ph.ManagedEntityRowId = vme.ManagedEntityRowId INNER JOIN vManagedEntityType vmet on vmet.ManagedEntityTypeRowId = vme.ManagedEntityTypeRowId GROUP BY vmet.ManagedEntityTypeSystemName Order by count DESC --Find the current Perf partition table SELECT TOP(1) TableGuid, StartDateTime, EndDateTime FROM StandardDatasetTableMap sdtm INNER JOIN StandardDataset sd on sd.DatasetId = sdtm.DatasetId WHERE AggregationTypeId = '20' AND sd.SchemaName = 'Perf' ORDER BY sdtm.EndDateTime DESC

 

 

Misc Section:

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

Comments (9)

  1. cdufour says:

    Excellent! Thx!

  2. Pramod says:

    Thanks for providing updated SQL queries.!!

  3. Kamal says:

    Excellent! Thanks

  4. dro says:

    Dude you are the absolute man when it comes to ops mgr. I am going piggyback off these queries to create tableau visualizations in an IT Ops dashboard for our organization. Thanks again.

  5. Marlon says:

    Many thanks!

  6. Scratulous says:

    Kevin, I took the liberty of wrapping your wonderful queries into an old SAPIEN Technologies PrimalForms (Community Edition) GUI for everyone’s convenience. I use this GUI everyday, and am very satisfied how it performs. I also have included in the script the Out-Excel function adapted from “The Scripting Guys”. It can be remarked or left in as well as the Out-String to $RichTextBox1 as desired. I hope everyone enjoys the GUI as muck as I do. Happy SCOMing, Dave

    # BEGIN SCRIPT_______________

    # Out-Excel can be Added or Removed if you like the output to the RichTextBox1 in the various Script Blocks.
    # Edit the System Variables for Databases and DB Servers to reflect your OMDB and DWDB Servers\Instances.
    # SCOM 2012 and 2016 capable queries adapted from Kevin Holman’s TechNet Blog – #https://blogs.technet.microsoft.com/kevinholman/2016/11/11/scom-sql-queries/
    # Out-Excel function borrowed from “The Scripting Guys” – #https://blogs.technet.microsoft.com/heyscriptingguy/2014/01/10/powershell-and-excel-fast-safe-and-reliable/

    #Generated Form Function
    function GenerateForm {
    ########################################################################
    # Code Generated By: SAPIEN Technologies PrimalForms (Community Edition) v1.0.10.0
    # Generated On: 11/4/2016 12:07 PM
    # Generated By: David Bowman
    # Email – david@bowmanhome.com
    ########################################################################

    #region Import the Assemblies
    [reflection.assembly]::loadwithpartialname(“System.Drawing”) | Out-Null
    [reflection.assembly]::loadwithpartialname(“System.Windows.Forms”) | Out-Null
    #endregion

    #region Generated Form Objects
    $form1 = New-Object System.Windows.Forms.Form
    $tabControl1 = New-Object System.Windows.Forms.TabControl
    $tabPage1 = New-Object System.Windows.Forms.TabPage
    $OMDBSizeButton = New-Object System.Windows.Forms.Button
    $OMDBAlertsPerDayButton = New-Object System.Windows.Forms.Button
    $CONAlertsPerDayRSButton = New-Object System.Windows.Forms.Button
    $OMDBGroomButton = New-Object System.Windows.Forms.Button
    $EVTByDayButton = New-Object System.Windows.Forms.Button
    $OMDBTop20AlertsRCButton = New-Object System.Windows.Forms.Button
    $Top20ALTACButton = New-Object System.Windows.Forms.Button
    $MostEVTbyCompButton = New-Object System.Windows.Forms.Button
    $MostEVTbyCompByEVTButton = New-Object System.Windows.Forms.Button
    $StateCHGPerDayButton = New-Object System.Windows.Forms.Button
    $tabPage2 = New-Object System.Windows.Forms.TabPage
    $DWDBSizeButton = New-Object System.Windows.Forms.Button
    $MComEVT_ENButton = New-Object System.Windows.Forms.Button
    $DWEVTPerDayButton = New-Object System.Windows.Forms.Button
    $DWGroomButton = New-Object System.Windows.Forms.Button
    $tabPage3 = New-Object System.Windows.Forms.TabPage
    $RUNASProfAcctbutton = New-Object System.Windows.Forms.Button
    $PingDownButton = New-Object System.Windows.Forms.Button
    $ManCompCountButton = New-Object System.Windows.Forms.Button
    $RemManSetButton = New-Object System.Windows.Forms.Button
    $ManInstAGTButton = New-Object System.Windows.Forms.Button
    $MonClassOMDBButton = New-Object System.Windows.Forms.Button
    $MonPerMPButton = New-Object System.Windows.Forms.Button
    $RuMonOVRDButton = New-Object System.Windows.Forms.Button
    $MPVersionButton = New-Object System.Windows.Forms.Button
    $richTextBox1 = New-Object System.Windows.Forms.RichTextBox
    $InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState
    #endregion Generated Form Objects

    # System Variables for Databases and DB Servers
    $OMDBServer = “OM_DATABASE\INSTANCE”
    $OMDB = “OperationsManager”
    $DWDBServer = “DW_DATABASE\INSTANCE”
    $DWDB = “OperationsManagerDW”

    # Out-Excel Function
    function Out-Excel
    {param($Path = “$env:temp\$(Get-Date -Format yyyyMMddHHmmss).csv”)
    $input | Export-CSV -Path $Path -UseCulture -Encoding UTF8 -NoTypeInformation
    Invoke-Item -Path $Path
    }

    #———————————————-
    # Generated Event Script Blocks
    #———————————————-
    # Provide Custom Code for events specified in PrimalForms.
    $StateCHGPerDayButton_OnClick=
    {
    ############_____________ State changes per day: _____________############

    $Query = @”
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0] | Out-Excel
    #Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}

    }

    $MostEVTbyCompByEVTButton_OnClick=
    {
    ############_____________ Computers generating the most events, by event number: _____________############

    $Query = @”
    SELECT top 50 LoggingComputer as ComputerName, COUNT(*) as TotalEvents, Number as EventID
    FROM EventallView with (NOLOCK)
    GROUP BY LoggingComputer, Number
    ORDER BY TotalEvents DESC
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0]| Out-Excel
    #Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}

    }

    $MComEVT_ENButton_OnClick=
    {
    ############_____________ Most Common Events by event number: _____________############

    $Query = @”
    SELECT top 50 EventDisplayNumber, COUNT(*) AS TotalEvents
    FROM Event.vEvent
    GROUP BY EventDisplayNumber
    ORDER BY TotalEvents DESC
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$DWDBServer;Database=$DWDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0]| Out-Excel
    #$Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}

    }

    $DWEVTPerDayButton_OnClick=
    {
    ############_____________ To inspect total events in DW, and then break it down per day: _____________############

    $Query = @”
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$DWDBServer;Database=$DWDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0]| Out-Excel
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    }

    $OMDBTop20AlertsRCButton_OnClick=
    {
    ############_____________ Top 20 Alerts in an Operational Database, by Repeat Count _____________############

    $Query = @”
    SELECT TOP 50 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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel

    }

    $RuMonOVRDButton_OnClick=
    {
    ############_____________ To dump out all the rules and monitors that have overrides, and display the context and instance of the override: _____________############

    $Query = @”
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $MPVersionButton_OnClick=
    {
    ############_____________ Management Pack and Version: _____________############

    $Query = @”
    SELECT MPName, MPFriendlyName, MPVersion, MPIsSealed
    FROM ManagementPack WITH(NOLOCK)
    ORDER BY MPName
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $ManCompCountButton_OnClick=
    {
    ############_____________ To find all managed computers count: _____________############

    $Query = @”
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $ManInstAGTButton_OnClick=
    {
    ############_____________ Find All Agents which are manually installed: _____________############

    $Query = @”
    select bme.DisplayName from MT_HealthService mths
    INNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityId
    where IsManuallyInstalled = 1
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $RUNASProfAcctbutton_OnClick=
    {
    ############_____________ To view all Run-As Profiles, their associated Run-As account, and associated agent name: _____________############

    $Query = @”
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $RemManSetButton_OnClick=
    {
    ############_____________ Set All Agents to Remotely Managed: _____________############

    $Query = @”
    UPDATE MT_HealthService
    SET IsManuallyInstalled=0
    WHERE IsManuallyInstalled=1
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $OMDBSizeButton_OnClick=
    {
    ############_____________ OM Database Size and used space. _____________############

    $Query = @”
    USE $OMDB
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    }

    $EVTByDayButton_OnClick=
    {

    ############_____________ All Events by count by day, with total for entire database: _____________############

    $Query = @”
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}

    }

    $PingDownButton_OnClick=
    {
    ############_____________ To find all managed computers that are currently down and not pingable: _____________############

    $Query = @”
    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

    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $Top20ALTACButton_OnClick=
    {
    ############______x_______ Top 20 Alerts in an Operational Database, by Repeat Count _____________############

    $Query = @”
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0] | out-gridview
    $Dataset.Tables[0]| Out-Excel
    }

    $MostEVTbyCompButton_OnClick=
    {
    ############_____________ Computers generating the most events: _____________############

    $Query = @”
    SELECT top 50 LoggingComputer as ComputerName, COUNT(*) as TotalEvents
    FROM EventallView with (NOLOCK)
    GROUP BY LoggingComputer
    ORDER BY TotalEvents DESC
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $DWDBSizeButton_OnClick=
    {
    ############_____________ DW Database Size and used space. _____________############

    $Query = @”
    USE $DWDB
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$DWDBServer;Database=$OMDBDW;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $MonPerMPButton_OnClick=
    {
    ############_____________ Monitors Per MP:: _____________############

    $Query = @”
    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

    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $OMDBAlertsPerDayButton_OnClick=
    {
    ############_____________ Number of console Alerts per Day: OMDB _____________############

    $Query = @”
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $MonClassOMDBButton_OnClick=
    {
    ############_____________ Classes available in the DB: _____________############

    $Query = @”
    SELECT * FROM ManagedType
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $CONAlertsPerDayRSButton_OnClick=
    {
    ############_____________ Number of console Alerts per Day by Resolution State: _____________############

    $Query = @”
    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
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel\
    }

    $OMDBGroomButton_OnClick=
    {
    ############_____________ OMDB Grooming Settings: _____________############

    $Query = @”
    SELECT * FROM PartitionAndGroomingSettings WITH (NOLOCK)
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$OMDBServer;Database=$OMDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset) | Out-Null
    $SqlConnection.Close()
    $Dataset.Tables[0] | Out-Excel | Out-String -Verbose -Width 180 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $DWGroomButton_OnClick=
    {
    ############_____________ Grooming for the DW for each dataset: _____________############

    $Query = @”
    SELECT GroomStoredProcedureName, MaxDataAgeDays, BuildAggregationStoredProcedureName, AggregationIntervalDurationMinutes, GroomingIntervalMinutes FROM StandardDatasetAggregation
    “@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=$DWDBServer;Database=$DWDB;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $Dataset = New-Object System.Data.Dataset
    $SqlAdapter.Fill($Dataset)
    $SqlConnection.Close()
    $Dataset.Tables[0] | Ft -AutoSize | Out-String -Verbose -Width 120 | foreach-object {$RichTextBox1.AppendText($_ +”`n”)}
    $Dataset.Tables[0]| Out-Excel
    }

    $OnLoadForm_StateCorrection=
    {#Correct the initial state of the form to prevent the .Net maximized form issue
    $form1.WindowState = $InitialFormWindowState
    }

    #———————————————-
    #region Generated Form Code
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 779
    $System_Drawing_Size.Width = 1377
    $form1.ClientSize = $System_Drawing_Size
    $form1.DataBindings.DefaultDataSourceUpdateMode = 0
    $form1.Name = “form1”
    $form1.Text = “SCOM 2012 SQL Database Queries”
    $form1.WindowState = 1

    $tabControl1.DataBindings.DefaultDataSourceUpdateMode = 0
    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 4
    $System_Drawing_Point.Y = 4
    $tabControl1.Location = $System_Drawing_Point
    $tabControl1.Name = “tabControl1”
    $tabControl1.SelectedIndex = 0
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 770
    $System_Drawing_Size.Width = 416
    $tabControl1.Size = $System_Drawing_Size
    $tabControl1.TabIndex = 22

    $form1.Controls.Add($tabControl1)
    $tabPage1.DataBindings.DefaultDataSourceUpdateMode = 0
    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 4
    $System_Drawing_Point.Y = 22
    $tabPage1.Location = $System_Drawing_Point
    $tabPage1.Name = “tabPage1”
    $System_Windows_Forms_Padding = New-Object System.Windows.Forms.Padding
    $System_Windows_Forms_Padding.All = 3
    $System_Windows_Forms_Padding.Bottom = 3
    $System_Windows_Forms_Padding.Left = 3
    $System_Windows_Forms_Padding.Right = 3
    $System_Windows_Forms_Padding.Top = 3
    $tabPage1.Padding = $System_Windows_Forms_Padding
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 744
    $System_Drawing_Size.Width = 408
    $tabPage1.Size = $System_Drawing_Size
    $tabPage1.TabIndex = 0
    $tabPage1.Text = “Operational DB Section”
    $tabPage1.UseVisualStyleBackColor = $True

    $tabControl1.Controls.Add($tabPage1)

    $OMDBSizeButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 15
    $OMDBSizeButton.Location = $System_Drawing_Point
    $OMDBSizeButton.Name = “OMDBSizeButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $OMDBSizeButton.Size = $System_Drawing_Size
    $OMDBSizeButton.TabIndex = 0
    $OMDBSizeButton.Text = “OM Database Size and used space”
    $OMDBSizeButton.UseVisualStyleBackColor = $True
    $OMDBSizeButton.add_Click($OMDBSizeButton_OnClick)

    $tabPage1.Controls.Add($OMDBSizeButton)

    $OMDBAlertsPerDayButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 56
    $OMDBAlertsPerDayButton.Location = $System_Drawing_Point
    $OMDBAlertsPerDayButton.Name = “OMDBAlertsPerDayButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $OMDBAlertsPerDayButton.Size = $System_Drawing_Size
    $OMDBAlertsPerDayButton.TabIndex = 2
    $OMDBAlertsPerDayButton.Text = “Number of console Alerts per Day: OMDB”
    $OMDBAlertsPerDayButton.UseVisualStyleBackColor = $True
    $OMDBAlertsPerDayButton.add_Click($OMDBAlertsPerDayButton_OnClick)

    $tabPage1.Controls.Add($OMDBAlertsPerDayButton)

    $CONAlertsPerDayRSButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 97
    $CONAlertsPerDayRSButton.Location = $System_Drawing_Point
    $CONAlertsPerDayRSButton.Name = “CONAlertsPerDayRSButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $CONAlertsPerDayRSButton.Size = $System_Drawing_Size
    $CONAlertsPerDayRSButton.TabIndex = 3
    $CONAlertsPerDayRSButton.Text = “Console Alerts per Day by Resolution State: OMDB”
    $CONAlertsPerDayRSButton.UseVisualStyleBackColor = $True
    $CONAlertsPerDayRSButton.add_Click($CONAlertsPerDayRSButton_OnClick)

    $tabPage1.Controls.Add($CONAlertsPerDayRSButton)

    $OMDBGroomButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 384
    $OMDBGroomButton.Location = $System_Drawing_Point
    $OMDBGroomButton.Name = “OMDBGroomButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $OMDBGroomButton.Size = $System_Drawing_Size
    $OMDBGroomButton.TabIndex = 19
    $OMDBGroomButton.Text = “OMDB grooming info”
    $OMDBGroomButton.UseVisualStyleBackColor = $True
    $OMDBGroomButton.add_Click($OMDBGroomButton_OnClick)

    $tabPage1.Controls.Add($OMDBGroomButton)

    $EVTByDayButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 302
    $EVTByDayButton.Location = $System_Drawing_Point
    $EVTByDayButton.Name = “EVTByDayButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $EVTByDayButton.Size = $System_Drawing_Size
    $EVTByDayButton.TabIndex = 4
    $EVTByDayButton.Text = “All Events by count by day, with total for entire database”
    $EVTByDayButton.UseVisualStyleBackColor = $True
    $EVTByDayButton.add_Click($EVTByDayButton_OnClick)

    $tabPage1.Controls.Add($EVTByDayButton)

    $OMDBTop20AlertsRCButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 179
    $OMDBTop20AlertsRCButton.Location = $System_Drawing_Point
    $OMDBTop20AlertsRCButton.Name = “OMDBTop20AlertsRCButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $OMDBTop20AlertsRCButton.Size = $System_Drawing_Size
    $OMDBTop20AlertsRCButton.TabIndex = 6
    $OMDBTop20AlertsRCButton.Text = “Top 20 Alerts by Repeat Count: OMDB”
    $OMDBTop20AlertsRCButton.UseVisualStyleBackColor = $True
    $OMDBTop20AlertsRCButton.add_Click($OMDBTop20AlertsRCButton_OnClick)

    $tabPage1.Controls.Add($OMDBTop20AlertsRCButton)

    $Top20ALTACButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 138
    $Top20ALTACButton.Location = $System_Drawing_Point
    $Top20ALTACButton.Name = “Top20ALTACButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $Top20ALTACButton.Size = $System_Drawing_Size
    $Top20ALTACButton.TabIndex = 7
    $Top20ALTACButton.Text = “Top 20 Alerts by Alert Count: OMDB”
    $Top20ALTACButton.UseVisualStyleBackColor = $True
    $Top20ALTACButton.add_Click($Top20ALTACButton_OnClick)

    $tabPage1.Controls.Add($Top20ALTACButton)

    $MostEVTbyCompButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 220
    $MostEVTbyCompButton.Location = $System_Drawing_Point
    $MostEVTbyCompButton.Name = “MostEVTbyCompButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $MostEVTbyCompButton.Size = $System_Drawing_Size
    $MostEVTbyCompButton.TabIndex = 10
    $MostEVTbyCompButton.Text = “Computers generating the most events: OMDB”
    $MostEVTbyCompButton.UseVisualStyleBackColor = $True
    $MostEVTbyCompButton.add_Click($MostEVTbyCompButton_OnClick)

    $tabPage1.Controls.Add($MostEVTbyCompButton)

    $MostEVTbyCompByEVTButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 261
    $MostEVTbyCompByEVTButton.Location = $System_Drawing_Point
    $MostEVTbyCompByEVTButton.Name = “MostEVTbyCompByEVTButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $MostEVTbyCompByEVTButton.Size = $System_Drawing_Size
    $MostEVTbyCompByEVTButton.TabIndex = 11
    $MostEVTbyCompByEVTButton.Text = “Computers generating the most events, by event number: OMDB”
    $MostEVTbyCompByEVTButton.UseVisualStyleBackColor = $True
    $MostEVTbyCompByEVTButton.add_Click($MostEVTbyCompByEVTButton_OnClick)

    $tabPage1.Controls.Add($MostEVTbyCompByEVTButton)

    $StateCHGPerDayButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 343
    $StateCHGPerDayButton.Location = $System_Drawing_Point
    $StateCHGPerDayButton.Name = “StateCHGPerDayButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $StateCHGPerDayButton.Size = $System_Drawing_Size
    $StateCHGPerDayButton.TabIndex = 12
    $StateCHGPerDayButton.Text = “State changes per day”
    $StateCHGPerDayButton.UseVisualStyleBackColor = $True
    $StateCHGPerDayButton.add_Click($StateCHGPerDayButton_OnClick)

    $tabPage1.Controls.Add($StateCHGPerDayButton)

    $tabPage2.DataBindings.DefaultDataSourceUpdateMode = 0
    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 4
    $System_Drawing_Point.Y = 22
    $tabPage2.Location = $System_Drawing_Point
    $tabPage2.Name = “tabPage2”
    $System_Windows_Forms_Padding = New-Object System.Windows.Forms.Padding
    $System_Windows_Forms_Padding.All = 3
    $System_Windows_Forms_Padding.Bottom = 3
    $System_Windows_Forms_Padding.Left = 3
    $System_Windows_Forms_Padding.Right = 3
    $System_Windows_Forms_Padding.Top = 3
    $tabPage2.Padding = $System_Windows_Forms_Padding
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 744
    $System_Drawing_Size.Width = 408
    $tabPage2.Size = $System_Drawing_Size
    $tabPage2.TabIndex = 1
    $tabPage2.Text = “DataWarehouse Section”
    $tabPage2.UseVisualStyleBackColor = $True
    $tabPage2.add_Click($handler_TabPage:_Click)

    $tabControl1.Controls.Add($tabPage2)

    $DWDBSizeButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 14
    $DWDBSizeButton.Location = $System_Drawing_Point
    $DWDBSizeButton.Name = “DWDBSizeButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $DWDBSizeButton.Size = $System_Drawing_Size
    $DWDBSizeButton.TabIndex = 1
    $DWDBSizeButton.Text = “DW Database Size and used space”
    $DWDBSizeButton.UseVisualStyleBackColor = $True
    $DWDBSizeButton.add_Click($DWDBSizeButton_OnClick)

    $tabPage2.Controls.Add($DWDBSizeButton)

    $MComEVT_ENButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 14
    $System_Drawing_Point.Y = 55
    $MComEVT_ENButton.Location = $System_Drawing_Point
    $MComEVT_ENButton.Name = “MComEVT_ENButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $MComEVT_ENButton.Size = $System_Drawing_Size
    $MComEVT_ENButton.TabIndex = 8
    $MComEVT_ENButton.Text = “Most Common Events by event number”
    $MComEVT_ENButton.UseVisualStyleBackColor = $True
    $MComEVT_ENButton.add_Click($MComEVT_ENButton_OnClick)

    $tabPage2.Controls.Add($MComEVT_ENButton)

    $DWEVTPerDayButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 14
    $System_Drawing_Point.Y = 96
    $DWEVTPerDayButton.Location = $System_Drawing_Point
    $DWEVTPerDayButton.Name = “DWEVTPerDayButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $DWEVTPerDayButton.Size = $System_Drawing_Size
    $DWEVTPerDayButton.TabIndex = 13
    $DWEVTPerDayButton.Text = “Total events in DW, and then break it down per day”
    $DWEVTPerDayButton.UseVisualStyleBackColor = $True
    $DWEVTPerDayButton.add_Click($DWEVTPerDayButton_OnClick)

    $tabPage2.Controls.Add($DWEVTPerDayButton)

    $DWGroomButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 14
    $System_Drawing_Point.Y = 137
    $DWGroomButton.Location = $System_Drawing_Point
    $DWGroomButton.Name = “DWGroomButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $DWGroomButton.Size = $System_Drawing_Size
    $DWGroomButton.TabIndex = 18
    $DWGroomButton.Text = “Grooming for the DW for each dataset”
    $DWGroomButton.UseVisualStyleBackColor = $True
    $DWGroomButton.add_Click($DWGroomButton_OnClick)

    $tabPage2.Controls.Add($DWGroomButton)

    $tabPage3.DataBindings.DefaultDataSourceUpdateMode = 0
    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 4
    $System_Drawing_Point.Y = 22
    $tabPage3.Location = $System_Drawing_Point
    $tabPage3.Name = “tabPage3”
    $System_Windows_Forms_Padding = New-Object System.Windows.Forms.Padding
    $System_Windows_Forms_Padding.All = 3
    $System_Windows_Forms_Padding.Bottom = 3
    $System_Windows_Forms_Padding.Left = 3
    $System_Windows_Forms_Padding.Right = 3
    $System_Windows_Forms_Padding.Top = 3
    $tabPage3.Padding = $System_Windows_Forms_Padding
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 744
    $System_Drawing_Size.Width = 408
    $tabPage3.Size = $System_Drawing_Size
    $tabPage3.TabIndex = 2
    $tabPage3.Text = “Agent/Monitor/MISC Section”
    $tabPage3.UseVisualStyleBackColor = $True

    $tabControl1.Controls.Add($tabPage3)

    $RUNASProfAcctbutton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 14
    $System_Drawing_Point.Y = 350
    $RUNASProfAcctbutton.Location = $System_Drawing_Point
    $RUNASProfAcctbutton.Name = “RUNASProfAcctbutton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 38
    $System_Drawing_Size.Width = 376
    $RUNASProfAcctbutton.Size = $System_Drawing_Size
    $RUNASProfAcctbutton.TabIndex = 13
    $RUNASProfAcctbutton.Text = “Run-As Profiles, Run-As account, and agent name”
    $RUNASProfAcctbutton.UseVisualStyleBackColor = $True
    $RUNASProfAcctbutton.add_Click($RUNASProfAcctbutton_OnClick)

    $tabPage3.Controls.Add($RUNASProfAcctbutton)

    $PingDownButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 303
    $PingDownButton.Location = $System_Drawing_Point
    $PingDownButton.Name = “PingDownButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $PingDownButton.Size = $System_Drawing_Size
    $PingDownButton.TabIndex = 12
    $PingDownButton.Text = “All managed computers that are currently down and not pingable”
    $PingDownButton.UseVisualStyleBackColor = $True
    $PingDownButton.add_Click($PingDownButton_OnClick)

    $tabPage3.Controls.Add($PingDownButton)

    $ManCompCountButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 138
    $ManCompCountButton.Location = $System_Drawing_Point
    $ManCompCountButton.Name = “ManCompCountButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $ManCompCountButton.Size = $System_Drawing_Size
    $ManCompCountButton.TabIndex = 11
    $ManCompCountButton.Text = “All Managed Computers count”
    $ManCompCountButton.UseVisualStyleBackColor = $True
    $ManCompCountButton.add_Click($ManCompCountButton_OnClick)

    $tabPage3.Controls.Add($ManCompCountButton)

    $RemManSetButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 97
    $RemManSetButton.Location = $System_Drawing_Point
    $RemManSetButton.Name = “RemManSetButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $RemManSetButton.Size = $System_Drawing_Size
    $RemManSetButton.TabIndex = 10
    $RemManSetButton.Text = “Set All Agents to Remotely Managed”
    $RemManSetButton.UseVisualStyleBackColor = $True
    $RemManSetButton.add_Click($RemManSetButton_OnClick)

    $tabPage3.Controls.Add($RemManSetButton)

    $ManInstAGTButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 56
    $ManInstAGTButton.Location = $System_Drawing_Point
    $ManInstAGTButton.Name = “ManInstAGTButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $ManInstAGTButton.Size = $System_Drawing_Size
    $ManInstAGTButton.TabIndex = 9
    $ManInstAGTButton.Text = “Find All Agents which are manually installed”
    $ManInstAGTButton.UseVisualStyleBackColor = $True
    $ManInstAGTButton.add_Click($ManInstAGTButton_OnClick)

    $tabPage3.Controls.Add($ManInstAGTButton)

    $MonClassOMDBButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 262
    $MonClassOMDBButton.Location = $System_Drawing_Point
    $MonClassOMDBButton.Name = “MonClassOMDBButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $MonClassOMDBButton.Size = $System_Drawing_Size
    $MonClassOMDBButton.TabIndex = 8
    $MonClassOMDBButton.Text = “Available Monitor Classes in the OMDB”
    $MonClassOMDBButton.UseVisualStyleBackColor = $True
    $MonClassOMDBButton.add_Click($MonClassOMDBButton_OnClick)

    $tabPage3.Controls.Add($MonClassOMDBButton)

    $MonPerMPButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 221
    $MonPerMPButton.Location = $System_Drawing_Point
    $MonPerMPButton.Name = “MonPerMPButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $MonPerMPButton.Size = $System_Drawing_Size
    $MonPerMPButton.TabIndex = 7
    $MonPerMPButton.Text = “Monitors Per Management Pack”
    $MonPerMPButton.UseVisualStyleBackColor = $True
    $MonPerMPButton.add_Click($MonPerMPButton_OnClick)

    $tabPage3.Controls.Add($MonPerMPButton)

    $RuMonOVRDButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 180
    $RuMonOVRDButton.Location = $System_Drawing_Point
    $RuMonOVRDButton.Name = “RuMonOVRDButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $RuMonOVRDButton.Size = $System_Drawing_Size
    $RuMonOVRDButton.TabIndex = 6
    $RuMonOVRDButton.Text = “Rules and Monitors that have overrides: Display the context and instance”
    $RuMonOVRDButton.UseVisualStyleBackColor = $True
    $RuMonOVRDButton.add_Click($RuMonOVRDButton_OnClick)

    $tabPage3.Controls.Add($RuMonOVRDButton)

    $MPVersionButton.DataBindings.DefaultDataSourceUpdateMode = 0

    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 15
    $System_Drawing_Point.Y = 15
    $MPVersionButton.Location = $System_Drawing_Point
    $MPVersionButton.Name = “MPVersionButton”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 35
    $System_Drawing_Size.Width = 376
    $MPVersionButton.Size = $System_Drawing_Size
    $MPVersionButton.TabIndex = 5
    $MPVersionButton.Text = “Management Pack and Version”
    $MPVersionButton.UseVisualStyleBackColor = $True
    $MPVersionButton.add_Click($MPVersionButton_OnClick)

    $tabPage3.Controls.Add($MPVersionButton)

    $richTextBox1.DataBindings.DefaultDataSourceUpdateMode = 0
    $System_Drawing_Point = New-Object System.Drawing.Point
    $System_Drawing_Point.X = 426
    $System_Drawing_Point.Y = 11
    $richTextBox1.Location = $System_Drawing_Point
    $richTextBox1.Name = “richTextBox1”
    $System_Drawing_Size = New-Object System.Drawing.Size
    $System_Drawing_Size.Height = 763
    $System_Drawing_Size.Width = 946
    $richTextBox1.Size = $System_Drawing_Size
    $richTextBox1.TabIndex = 15
    $richTextBox1.Text = “”

    $form1.Controls.Add($richTextBox1)

    #endregion Generated Form Code

    #Save the initial state of the form
    $InitialFormWindowState = $form1.WindowState
    #Init the OnLoad event to correct the initial state of the form
    $form1.add_Load($OnLoadForm_StateCorrection)
    #Show the Form
    $form1.ShowDialog()| Out-Null

    } #End Function

    #Call the Function
    GenerateForm

Skip to main content