Description

This custom IgniteVM alert can be used with IgniteVM data (assuming you have the Ignite8 Performance product where alerts are defined) and will watch the VM Disk Usage Rate VMware metric. This specific example is limited to between 7:30 am and 5:00 pm (defined within the T-SQL) with the theory being that backups and other batch jobs run at night and can send the VM Disk Usage Rate up normally. This alert is designed to catch high disk usage during the day when end users are using the system. Based on the default thresholds below, it will send emails when the value exceeds 10MB / sec (10,000 KB / sec) for the most recent 10 minute timeframe.

To create the alert, click on Alerts > Manage Alerts and create a Custom Alert of type Custom SQL Alert - Single Numeric Return.

The example below uses the "vmDiskUsageRate" metric, but a very similar query can be used for other metrics as well. For a list of all metric names, you can run these queries in the Ignite repository database:

select metric_key from conv_metrics order by metric_key;

T-SQL script

When creating the alert, make it look like the screenshot below and plug this into the SQL Statement section. Note: if you have an Oracle database for your Ignite repository, please contact Confio support (support@confio.com) for an alternative script.

declare @DBID int, 
@VMID int,
 @TIME int,
 @SQL varchar(1000)
 
-- #DBID# is passed in by the Ignite alert
 set @DBID = #DBID#
 
-- get the current time in integer format, i.e. 7:30 am = 730 and 5:00 pm = 1700
 select @TIME=CONVERT(INT,CONVERT(VARCHAR,DATEPART(HOUR, CURRENT_TIMESTAMP)) + 
CONVERT(VARCHAR,DATEPART(MINUTE, CURRENT_TIMESTAMP)))
 
-- only run the following code between 7:30 am and 5:00 pm, otherwise return 0 for vmDiskUsageRate
 IF @TIME BETWEEN 730 AND 1700
 BEGIN
 -- get the VMID for this instance
 select @VMID=vm.id
 from cond d
 inner join CONV_VM vm on vm.ID = d.VMID
 where d.ID = @DBID
 
set @SQL = 'select AVG(md.V) '+
 'from CONV_METRICS m '+
 'inner join CONV_METRIC_DETAIL_'+convert(varchar,@VMID)+' md on md.METRIC_ID = m.ID '+
 'where m.METRIC_KEY = ''vmDiskUsageRate'' '+
 'and md.D >= DATEADD(MINUTE, -10, CURRENT_TIMESTAMP)'
 exec (@SQL)
 END
 ELSE
 SELECT 0

Alert Definition