SCOM 2012 - Empty Reports?

  • [アーティクル]

Updated 06 July 2015

 

A few years ago I hit an issue with my logical disk reports being empty...and this week so did a client of mine.  The data is coming in, everything looks healthy, so what could it be?  As the data comes into the data warehouse (DW) it's in a RAW state, but reports are run against aggregated data (hourly, daily).  If that data isn't being aggregated, then your reports will be empty.  In both cases I've investigated, aggregation (via StandardDataSetMaintenance) is running....it's just behind.

How is your DW is doing?  Try running this query on your DW:

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'
,SUM(Cast (DirtyInd As Int)) As 'Count_OutstandingAggregations'
,DatasetId
From StandardDatasetAggregationHistory
Where LastAggregationDurationSeconds Is Not 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 

 

 Hopefully, you don't have more than 1-2 outstanding aggregations.  However, if the number is higher than that keep reading....

 

 For each schema that has >3 outstanding aggregations, run this:

Note: Example is for perf schema.

Note: Running  will aggregate hourly and daily data for that schema.

Declare @i int
Set @i=0
Declare @DataSet uniqueidentifier
Set @DataSet = (Select DataSetId From StandardDataset Where SchemaName = 'Perf')
While(@i<=500)
Begin
Exec StandardDataSetMaintenance @DataSet
Set @i=@i+1
Waitfor Delay '00:00:05'
End

*The "While(@I<=500)" determines how many times to loop.  You can alter 500 to meet your needs.

This will get you back to a healthy state, but doesn't guarantee you'll stay current.  If this is a chronic issue for you, consider tuning your MPs to limit unnecessary writes to the DW.  Do you really record/store logical disk size multiple times a day?  As always, tuning generally is the key to keeping OpsMgr happy and healthy!

 

Update: 23 January 2015

If your database is chronically behind, try looking at how much data you receive a day...and how much the default grooming will handle.

1)  Run this on the OpsDB to see how many events you get a day

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

2) Run this on the DW and note the "MaxRowstoGroom" number.

Select * from StandardDatasetAggregation  Where GroomStoredProcedureName = 'StateGroom'

3) If "MaxRowstoGroom" isn't bigger than the average number of events received daily, increase it.  For example, set the MaxRowsToGroom to 150000:

Update StandardDatasetAggregation

Set MaxRowsToGroom = 150000

Where GroomStoredProcedureName = 'StateGroom'

 

Do the same for alert data:

1) Number of console Alerts per Day (OpsDB):

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

2) Run this on the DW and note the "MaxRowstoGroom" number.
Select * from StandardDatasetAggregation  Where GroomStoredProcedureName = 'AlertGroom'

3) Increase MaxRowstoGroom if needed

 

Do the same for perf data:

1) Performance insertions per day (OpsDB):

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

2) Run this on the DW and note the "MaxRowstoGroom" number.
Select * from StandardDatasetAggregation  Where GroomStoredProcedureName = 'PerformanceGroom'
3) Increase MaxRowstoGroom if needed

 

Update: 06 July 2015

If you are hugely behind (50+ rows) you could see a conflict with the automatic job running at the same time as your manual one.  Try disabling this rule until you stop the manual grooming….  This stored procedure is called on the data warehouse every 60 seconds.  It performs many, many tasks, of which Index optimization is but one.

Good luck!  Nicole

 

For more guidance, see https://support.microsoft.com/kb/2573329

Comments

  • Anonymous
    May 31, 2015
    Nice one. Thanks a lot.

  • Anonymous
    June 29, 2015
    Awsome. It worked.Able to view the performance reports now. Thank You.

  • Anonymous
    July 19, 2015
    This saved a 1TB DW that was unagle to run reports after an upgrade to CU6. Thank you Nicole!!!

  • Anonymous
    June 17, 2019
    While executing the aggregation query mentioned in this article. I am receiving following error, can you please help.Error says...Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    • Anonymous
      June 18, 2019
      That seems to indicate you don't have the correct permissions. However, I'm not a SQL expert. Can you clarify which of the queries you were running and what your access is to the DB you're running it in?