Troubleshooting Exchange 2010 MP aggregations in Operations Manager 2012

A while back in September 2012 I posted about a bug in Exchange 2010 aggregations in the data warehouse(Troubleshooting Exchange 2010 MP aggregations). The issue is still there, at least until version

The aggregation stored procedure “” has been improved but the performance culprit has not been solved, so I took the time to rewrite the hack for the latest MP version. If you compare the two versions you’ll se the new hack is much more lighter, in fact I use an undocumented feature of the StateChangeEventList stored procedure that actually does the selection job for me.

If called with a null value for the @ManagedEntityMonitorRowId parameter, the procedure checks for the existence of a temporary table called #ManagedEntityMonitor to filter the returned change events. All I had to do was to create such a table and load with the proper monitors Ids just before the original storeds procedure calls the StateChangeEventList stored procedure:

CREATE TABLE #ManagedEntityMonitor
    ManagedEntityMonitorRowId int

INSERT #ManagedEntityMonitor (ManagedEntityMonitorRowId)
SELECT ManagedEntityMonitorRowId from Exchange2010.vAvailabilityObjListV14 WHERE Scope=0

INSERT #StateEvent (ManagedEntityMonitorRowId, [DateTime], OldHealthState, NewHealthState)
EXEC StateChangeEventList
        @ManagedEntityMonitorRowId =  NULL
       ,@IntervalStartDateTime = @Stretched_StartDateTimeForHealthDataRetrieval
       ,@IntervalEndDateTime = @Stretched_EndDateTimeForHealthDataRetrieval
       ,@UseAggregatesToBuildStartOfIntervalStateInd = 0
       ,@AggregationCoverViewName = NULL
       ,@AggregationDateTime = NULL

The complete stored procedure is available in the DropBox repository under Scripts, I cannot post on Technet Gallery since I’m directly modifying code that I don’t own.

– Daniele

This posting is provided “AS IS” with no warranties, and confers no rights.

  1. #1 by Tommy on May 16, 2014 - 8:22 am

    Thank you very much!

  2. #2 by Tommy on May 15, 2014 - 3:58 pm

    Can you please provide the full script? I can no longer find the Dropbox file.
    Thank you!

  1. NeWay Technologies – Weekly Newsletter #19 – November 29, 2012NeWay | NeWay

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: