none
Age of available performance data RRS feed

  • Question

  • Hi all,

    Looking for evolution of consumed space on one our our server, I see that only 7 last days are available in the history.

    As I can read on various articles about that, this the default and preferred setting for OperationsManager DB.

    But I want to believe the older performance datas are available in OperationsManagerDW DB.

    Reading this article I guess the data set which correspond in "dbo.Dataset" table should be "Performance data set". But I'm not sure about the "dbo.StandardDatasetAggregation" table where I find 3 records for "Performance data set" :

    DatasetId AggregationTypeId AggregationIntervalDurationMinutes AggregationStartDelayMinutes BuildAggregationStoredProcedureName DeleteAggregationStoredProcedureName GroomStoredProcedureName IndexOptimizationIntervalMinutes MaxDataAgeDays GroomingIntervalMinutes MaxRowsToGroom LastGroomingDateTime
    1015045C-B1EF-48CF-A6F6-5FEC4046AC64 0 NULL NULL PerformanceAggregate NULL PerformanceGroom 240 10 240 100000 03/12/2019 05:58
    1015045C-B1EF-48CF-A6F6-5FEC4046AC64 20 60 20 PerformanceAggregate PerformanceAggregationDelete PerformanceGroom 240 400 240 100000 03/12/2019 06:10
    1015045C-B1EF-48CF-A6F6-5FEC4046AC64 30 1440 30 PerformanceAggregate PerformanceAggregationDelete PerformanceGroom 240 400 240 100000 03/12/2019 06:30

    The first row is set to 10 days and the 2 others about 240 days. So I guess I should be able to see at least 10 days aged performance data. Isn't it ?

    Thank you.

    Regards,


    FXE

    Tuesday, December 3, 2019 9:33 AM

Answers

  • Hi all and thank you for your answers.

    Now since I have understood how to retrieve OperationsManagerDW datas, I followed this article to access them via reports.

    Thank you again.

    Regards,


    FXE

    • Marked as answer by FXE Tuesday, December 3, 2019 1:04 PM
    Tuesday, December 3, 2019 1:04 PM

All replies

  • That's correct, you should find at least 10 days of raw perf data in the datawarehouse. However, that data is not displayed in "Performance views" in the console.

    If you want to access it, you have 2 options : 

    - Use a dashboard view with a performance widget instead of performance views

    - Query the datawarehouse using T-SQL queries

    Reports are also getting their data from the datawarehouse, but most of them will only allow you to chose between Hourly and Daily aggregation, not raw data.

    Tuesday, December 3, 2019 10:18 AM
  • Hi,

    You may find the SQL queries needed for this on Kevin's blog post over here:
    Understanding and modifying Data Warehouse retention and grooming
    SQL:

    --Actual Data Retained
    --This queries the tables and finds out how old your data actually is in the DW to verify if grooming is working
    select min(DWLastModifiedDateTime) as MinDate, max(DWLastModifiedDateTime) as MaxDate, datediff(d,min(DWLastModifiedDateTime),max(DWLastModifiedDateTime)) AS Alert from Alert.vAlert
    select min(datetime) as MinDate, max(datetime) as MaxDate, datediff(d,min(datetime),max(datetime)) AS Event from Event.vEvent 
    select min(datetime) as MinDate, max(datetime) as MaxDate, datediff(d,min(datetime),max(datetime)) AS PerfHourly from Perf.vPerfHourly
    select min(datetime) as MinDate, max(datetime) as MaxDate, datediff(d,min(datetime),max(datetime)) AS StateHourly from State.vStateHourly

    To get your current retention settings, run the following SQL query:

    SQL:

    --Retention Settings
    --This is the current setting for retention in your DW and the current Grooming Interval
    SELECT ds.datasetDefaultName AS 'Dataset Name',
     CASE
       WHEN sda.AggregationTypeId = 0 THEN 'raw'
       WHEN sda.AggregationTypeId = 20 THEN 'hourly'
       WHEN sda.AggregationTypeId = 30 THEN 'daily'
       ELSE 'unknown' 
     END AS 'AggregationType',
     sda.MaxDataAgeDays AS 'RetentionDays',
     sda.GroomingIntervalMinutes
    FROM dataset ds, StandardDatasetAggregation sda 
    WHERE ds.datasetid = sda.datasetid
    --AND ds.datasetDefaultName IN ('Alert data set','Event data set','Performance data set','State data set')
    ORDER by ds.datasetDefaultName


    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    Tuesday, December 3, 2019 10:19 AM
  • Hi all and thank you for your answers.

    Now since I have understood how to retrieve OperationsManagerDW datas, I followed this article to access them via reports.

    Thank you again.

    Regards,


    FXE

    • Marked as answer by FXE Tuesday, December 3, 2019 1:04 PM
    Tuesday, December 3, 2019 1:04 PM