|
Check retention settings for all core datasets |
|
/* Check retention setting for common data types */ USE OperationsManagerDW SELECT DS.DatasetDefaultName AS ‘Dataset’, SDA.MaxDataAgeDays AS ‘Days’, SDA.AggregationTypeId AS ‘Type’, SDA.BuildAggregationStoredProcedureName AS ‘Agg SP’, SDA.AggregationIntervalDurationMinutes AS ‘Agg Min’, SDA.GroomStoredProcedureName AS ‘Groom SP’, SDA.GroomingIntervalMinutes AS ‘Groom Min’, SDA.MaxRowsToGroom AS ‘Groom max rows’, SDA.LastGroomingDateTime AS ‘Last groomed’ FROM Dataset AS DS INNER JOIN StandardDatasetAggregation AS SDA ON DS.DatasetId = SDA.DatasetId WHERE (DS.DatasetDefaultName NOT LIKE ‘%Microsoft%’) ORDER BY ‘Dataset’ |
|
Update retention settings for Alert Dataset (400 to 90) |
|
/* Update retention settings for selected types */ DECLARE @Type AS INT, @Dataset AS VARCHAR(max), @DaysToKeep AS INT SET @Type = 0 /* Raw=0, Hourly=20, Daily=30 */ SET @Dataset = ‘Alert data set’ SET @DaysToKeep = 90 UPDATE StandardDatasetAggregation SET MaxDataAgeDays = @DaysToKeep FROM Dataset INNER JOIN StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId WHERE (Dataset.DatasetDefaultName = @Dataset) AND (StandardDatasetAggregation.AggregationTypeId = @Type) |
|
Update retention settings for Event Dataset (100 to 30) |
|
/* Update retention settings for selected types */ DECLARE @Type AS INT, @Dataset AS VARCHAR(max), @DaysToKeep AS INT SET @Type = 0 /* Raw=0, Hourly=20, Daily=30 */ SET @Dataset = ‘Event data set’ SET @DaysToKeep = 30 UPDATE StandardDatasetAggregation SET MaxDataAgeDays = @DaysToKeep FROM Dataset INNER JOIN StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId WHERE (Dataset.DatasetDefaultName = @Dataset) AND (StandardDatasetAggregation.AggregationTypeId = @Type) |
|
Update retention settings for State RAW Dataset (180 to 30) |
|
/* Update retention settings for selected types */ DECLARE @Type AS INT, @Dataset AS VARCHAR(max), @DaysToKeep AS INT SET @Type = 0 /* Raw=0, Hourly=20, Daily=30 */ SET @Dataset = ‘State data set’ SET @DaysToKeep = 30 UPDATE StandardDatasetAggregation SET MaxDataAgeDays = @DaysToKeep FROM Dataset INNER JOIN StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId WHERE (Dataset.DatasetDefaultName = @Dataset) AND (StandardDatasetAggregation.AggregationTypeId = @Type) |
|
Update retention settings for State HOURLY Dataset (400 to 90) |
|
/* Update retention settings for selected types */ DECLARE @Type AS INT, @Dataset AS VARCHAR(max), @DaysToKeep AS INT SET @Type = 20 /* Raw=0, Hourly=20, Daily=30 */ SET @Dataset = ‘State data set’ SET @DaysToKeep = 90 UPDATE StandardDatasetAggregation SET MaxDataAgeDays = @DaysToKeep FROM Dataset INNER JOIN StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId WHERE (Dataset.DatasetDefaultName = @Dataset) AND (StandardDatasetAggregation.AggregationTypeId = @Type) |
|
Update retention settings for State DAILY Dataset (400 to 90) |
|
/* Update retention settings for selected types */ DECLARE @Type AS INT, @Dataset AS VARCHAR(max), @DaysToKeep AS INT SET @Type = 30 /* Raw=0, Hourly=20, Daily=30 */ SET @Dataset = ‘State data set’ SET @DaysToKeep = 90 UPDATE StandardDatasetAggregation SET MaxDataAgeDays = @DaysToKeep FROM Dataset INNER JOIN StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId WHERE (Dataset.DatasetDefaultName = @Dataset) AND (StandardDatasetAggregation.AggregationTypeId = @Type) |