So - when there is a dataset aggregation failure - we log this and generate alerts already, and turn monitor states red/yellow - so this is already covered to some degree.
http://blogs.technet.com/b/kevinholman/archive/2010/08/30/the-31552-event-or-why-is-my-data-warehouse-server-consuming-so-much-cpu.aspx There is a specific query there above to show how many outstanding aggregations there are.... and you specifically would be interested in anything with a value greater than 1. To monitor a SQL statement output - here is a good example: http://blogs.technet.com/b/jonathanalmquist/archive/2008/11/12/monitor-default-management-pack.aspx http://www.systemcentercentral.com/download/script-query-value-from-sql-database-for-2-state-monitor/ http://www.systemcentercentral.com/scom-query-sql-database-for-value-in-monitoring-script/ From: [email protected] [mailto:[email protected]] On Behalf Of Pete Hakesley Sent: Thursday, April 3, 2014 9:27 AM To: MSMOM ([email protected]) Subject: [msmom] SCMO DataWareHouse Processing Backlog Hi all Believe we are seeing periodically problems with the data aggregation in the DW and that for days it can become backlogged. I found a couple of good articles http://operationsmanager2012.blogspot.co.uk/2013/02/scom-availability-report-monitoring.html and http://blogs.technet.com/b/operationsmgr/archive/2011/09/06/standard-dataset-maintenance-troubleshooter-for-system-center-operations-manager-2007.aspx Which discuss at least some of these points. I was wondering how to automatically detect backlog in the Ops DW and alert for tis in SCOM. Any ideas how to turn the a SQL query output into an Alert in SCOM 2007 R2? Believe the query I need to use is:- DECLARE @DatasetId uniqueidentifier SELECT @DatasetId = DatasetId FROM Dataset d WHERE (d.DatasetDefaultName ='State data set') Select AggregationDateTime, AggregationTypeId >From StandardDatasetAggregationHistory Where DatasetId = @DatasetId And DirtyInd = 1 Peter Hakesley PJH2711-RIPE, CDCT, MBCS SCC Services Data Centre Operations - Service Implementation - Enterprise Systems Engineer SCC Cole Valley 1, 20 Westwood Avenue, Tyseley, BIRMINGHAM B11 3RZ Tel: +44 (0)845 351 0680 xtn 4006 eMail: [email protected]<mailto:[email protected]> Web : www.scc.com<http://www.scc.com/> [Description: Description: BCS_Logo]
<<inline: image001.jpg>>
