Version 1
Created by darichar on Jan 10, 2014 12:35 PM. Last modified by darichar on Jan 10, 2014 12:35 PM.

    Description

    This alert will calculate the average host disk device read and write latency metrics for last 10 minutes (based on frequency of alert) and send an email when the value is over 15% (warning) or 30% (critical).

     

    Alert Definition

    To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

     

    Alert Name: VMware Host Disk Device Latency

     

    Execution Interval: 10 Minutes

     

    Notification Text: The following VMware storage devices have high latencies

     

    SQL Statement:

    DECLARE @HostID varchar(5), @VMID varchar(5), @SQL varchar(max);

     

    -- the Ignite metric data is stored in VM and HOST specific tables, and we need the VMID and HOSTID to get there

    SELECT @VMID=vmid FROM cond WHERE id = #DBID#;

    SELECT @HostID=current_host_id FROM conv_vm WHERE id=@VMID;

     

    -- query the VM metric tables for the last 10 minutes (frequency above) and calculate an average VM Disk Usage Rate

    SET @SQL = 'select ''Disk - '' + d.display_name + '' - Metric: '' + m.metric_key, AVG(md.v) latency_ms '+

              +'from conv_metrics m '+

              +'inner join conv_metric_detail_'+@HostID+' md ON md.metric_id = m.id '+

              +'inner join conv_device d on d.id = md.device_id '+

              +'WHERE d.device_type=''Disk'' '+

              +'AND m.metric_key like ''%latency%'' '+

              +'AND md.d >= DATEADD(MINUTE, -#FREQUENCY#, CURRENT_TIMESTAMP) '+

              +'GROUP BY d.display_name, m.metric_key'

    EXEC (@SQL)

     

    Execute Against: Repository

     

    Units: ms latency

     

    High Threshold: Min 30, Max empty

    Medium Threshold: Min 15, Max 30