Troubleshooting Empty OM12x Reports – Part 1: Exchange Server 2010 Report MP

Prelude:
When I started out to write a blog post about how to troubleshoot empty OM12x Reports, I quickly found myself writing about the not so much appreciated (this is a BIG understatement…) Exchange Server 2010 Reports MP.

So I decided to ditch that posting, start all over again and make it a two part series posting since there is much to tell. So this first posting will be all about the Exchange Server 2010 Reports MP. Simply because this MP is a SNAFU as pure as they can get.

Mind you, I am talking about the REPORTING MP, Microsoft.Exchange.2010.Reports.mp. I know the other part is also SNAFU, but this posting is all about the Microsoft.Exchange.2010.Reports.mp.

Yes, I blogged quite a few times about this MP and the ‘challenges’ it creates. Also wrote some postings about how to fix some issues, like the empty reports. But as it turned out, that fix doesn’t work with OM12x. Hence this posting.

Exchange Server 2010 Reports MP = Empty Reports?
Not ALL the times, but MANY times this MP is the culprit of empty OM12x Reports. The cause is the poorly written SQL queries who must aggregate the data present in the Exchange Server 2010 datasets in the Data Warehouse database.

But because the underlying queries for these aggregations are poorly written, they time out. So OTHER aggregation jobs don’t get the time anymore to run! Resulting in the RAW tables being loaded with tons of information without being aggregated so that data never ever goes into other datasets like HOURLY or DAILY. As a result: EMPTY reports.

Read further about how to solve that issue. Of course, you can rewrite the aggregation query as Daniele Grandini once did, but when you don’t use the Exchange Server 2010 Reports, it’s better to remove that MP and the related datasets.

Hey wait! Exchange Server 2010?! That’s so 2010 man!
I agree totally. But quite a few companies still have Exchange Server 2010 running. Yes, they’re moving away from it, to either Exchange Server 2016 (which became GA first half of October) or a combination with Office 365 in the mix.

But until that time, Exchange Server 2010 is production so monitoring is required.

Remove those Exchange Server 2010 datasets
Like stated before, the Exchange Server 2010 Reports MP creates a bunch of new datasets in the Data Warehouse database. In order to see all the datasets, run this query against the Data Warehouse database:

Use OperationsManagerDW SELECT DataSetDefaultName, AggregationTypeId, MaxDataAgeDays FROM StandardDatasetAggregation sda INNER JOIN dataset ds on ds.datasetid = sda.datasetid ORDER BY DataSetDefaultName

Now you’ll see all the datasets in your Data Warehouse database. Except for ONE Exchange Server 2010 dataset (Microsoft.Exchange.2010.Dataset.AlertImpact) you can remove them all. The earlier mentioned dataset is created by the Exchange Server 2010 MP, not the Exchange Server 2010 Reports MP.

Okay, since we have now a full view of the Exchange Server 2010 datasets to be removed, it’s time to continue.

BEFORE YOU CONTINUE
Please make a BACKUP of BOTH SCOM databases (OperationsManager & OperationsManagerDW). This way there is always a way back when things turn sour.

Also good to know:
When you continue from here, be aware all the steps are UNSUPPORTED by Microsoft. So you’re on your own!

Now follow these steps:

1. Have you made a verified backup of BOTH SCOM databases?
2. You KNOW that what you’re about to do is UNSUPPORTED & that you can’t blame me either?
3. Remove the Exchange Server 2010 Reports MP (Microsoft.Exchange.2010.Reports.mp) from SCOM, like you always do through the Console. Mind you, this is SUPPORTED by Microsoft ;
4. Start SQL Server Management Studio with an account that has sufficient permissions to modify the OperationsManagerDW database;
5. Run the second query of this blog posting in order to remove the related Exchange Server 2010 Reports MP data sets, one by one;
6. Don’t touch the Microsoft.Exchange.2010.Dataset.AlertImpact, see previous explanation;
7. When you’ve removed ALL Exchange Server 2010 Reports data sets (except the one mentioned in Step 6!), close SQL Server Management Studio and go to the SCOM Console > Tools > Search > Rules;
   
8. In the Search box, type Data Set > hit Search > one Rule will be found: Standard Data Warehouse Data Set maintenance rule > click the blue link View Knowledge so the properties of this Rule will be shown;
   
9. Go to the tab Overrides > Disable > For a specific object of class: Standard Data Set > select the  Microsoft.Exchange.2010.Dataset.AlertImpact data set > OK and save the Override in a dedicated unsealed MP.
10. Now the default grooming won’t run anymore for this particular data set. Isn’t necessary anymore since the Exchange Server 2010 Reports MP is already removed (Step 3).

Now all data should be processed (aggregated) step by step, resulting in FILLED reports. Sweet!

Close but no cigar?
Please know that the aggregation of data to catch up takes time (from a few hours to days depending on the size of your Data Warehouse database and the amount of data present in the RAW tables) so you won’t get filled reports right away.

However, when you still have empty reports, and nothing seems to be moving for days, it might be that you’ve got other issues with your data aggregation. In the next posting I’ll write about how to see whether you’re affected and even better, how to crack it (most of the times).

See you all next time!