State Change Event – Clean Up

SCOM state change event grooming should happen as per the settings present in the database grooming settings

SCOM Console ->Administration ->Settings -> General ->Database Settings

The grooming process should remove unnecessary data in the operations manager to maintain performance

as per the settings , scom should groom the state change event data of monitors older than 1 day for example

To confirm this we need to run the sql query in operationsmanagerdb

“SELECT DATEDIFF(d, MIN(TimeAdded), GETDATE()) AS [Current] FROM statechangeevent”

If the value of the output is greater than the value set in the scom database grooming settings

then grooming is not happening as expected

To clean up the older data , we need to run below sql query in operationsmanagerdb

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

Output will be the below message if the query execution is successful

“Command(s) completed successfully.”

Now verify still how many days state change event data is present by running this sql query

“SELECT DATEDIFF(d, MIN(TimeAdded), GETDATE()) AS [Current] FROM statechangeevent”

It should be days set in grooming + current day(1)

 

SCOM Agent Groups

SELECT SourceObjectDisplayName AS ‘Group’
FROM RelationshipGenericView
WHERE TargetObjectDisplayName like (‘%servername%’)
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’

SQL Query to find groups of a SCOM Managed Computer

#This query is to find groups of a specific agent managed computer in SCOM 2012.

SELECT SourceObjectDisplayName AS ‘Group’
FROM RelationshipGenericView
WHERE TargetObjectDisplayName like ‘%server_name%’
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’

SCOM Agents Maintenance Mode History SQL Query

USE OperationsManagerDW
SELECT ManagedEntity.DisplayName, MaintenanceModeHistory.*
FROM ManagedEntity WITH (NOLOCK)
INNER JOIN
MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId
INNER JOIN
MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId
where DisplayName Like ‘%servername%’

Use the SQL script below to find your last database backup time

–Use the SQL script below to find your last database backup time.

–SQL Query:

Use Master
declare @sd datetime
declare @ed datetime
SET @sd = CONVERT(date,getdate())
select @sd
Select database_name, backup_start_date ,backup_finish_date From Msdb.Dbo.Backupset where database_name = ‘Database_Name’ and backup_start_date > ‘2016-02-03’