Function max95 (and min95) for trigger expressions

[SergK]

Hello,

I think almost all here know method "maximum of 95%" that used in telecommunication to measure normal maximum bandwidth of network channel without consideration of small bursts. Algorithm is quite simple: all values for given period sorted ascending, then 5% of top values dropped and final result is maximum of rest 95% values.
That algorithm usually used for billing purposes, but I think it also would be useful in zabbix triggers to determinate "normal" minimum and maximum for day bandwidth (without spikes and drops) and raise alerts when bandwidth goes far beyond this values. Previously I used average value for such checks, but random bursts of traffic significantly distort average value and checks become unreliable.
min95 function pretty similar to max95, but 5% of bottom values dropped and minimum of 95% rest values used.
Syntax of new function max95 and min95 is identical to regular max and min.

Following patch is against svn release 6165. It is unintrusive, but have one limitation. I've added function DBnum_rows to internal Zabbix API (which return number of th rows in result of SQL request) but wrote realization of it only for mysql case. If my patch would be accepted to mainline i can extend that function to other supported databases or may be this task can do someone else who more experienced with database programming.

[nitrus10032]

I'd like to bump this and add that this would be awesome to have in future releases. Being able to trigger actions if this situation occurs would make it nice to cap bandwidth at a colo or something. I've got a server co-located with a burst package and I get charged big bucks for overages on the 95% plan. I'm setting up zabbix to execute a shell script on my router to enable a HTB queue that limits my upload down to my contracted amount. Also, here's the fix for the num_rows:

/*      
 * Get number of rows in result
 */
zbx_uint64_t    zbx_db_num_rows(DB_RESULT result)
{
        if(!result)     return NULL;
#ifdef  HAVE_MYSQL
        return mysql_num_rows(result);
#endif

#ifdef  HAVE_POSTGRESQL
        return result->row_num;
#endif

#ifdef  HAVE_ORACLE
        return sqlo_prows(result);
#endif

#ifdef  HAVE_SQLITE3
        return result->nrow;
#endif
}

[Alexei]

Please register it in https://support.zabbix.com/browse/ZBXNEXT for much better visibility.

[SergK]

I plan to rewrite the patch and make one generic function mmsort (I don't know which name suits it better) with a second parameter which defines percentage level. So mmsort(...., 95) will be equivalent to max95 and mmsort(...., 5) to min95. This is going to be more flexible as values other than 95% can be used. Also the code for it will be shorter and simpler.

nitrus10032, thanks for the code for other SQL engines!

[SergK]

at last I've found a time to rewrite my original patch and port it to Zabbix 1.8.
As I had promised now there is only one generic statistical function 'percentile' (http://en.wikipedia.org/wiki/Percentile).
Function has three arguments, first is number of percentile, e.g. 95, 5 or something else. Other two arguments exactly like in avg, min or max functions: length of period (sec or #num) and optional time shift (sec).
So new equivalent of max95 is percentile(95, ....) and for min95 - percentile(5, ....)

Also I've rewritten DB code with realloc function, so now there is no need to add new function to Zabbix DB API.

Attached patch is against last svn snapshot of 1.8.x tree, but also applicable without errors to trunk svn tree, though I haven't tested it against trunk.

Now patch is small and unintrusive at all, so I think it can be safely merged even to 1.8.x tree.

There is only small issue with trigger popup window (which adds function to trigger expression), looks like it can't hadle function with three arguments. And I'm not expert in PHP and webinterfaces and don't know how to fix it.

As Alexei suggested in this thread, I've created feature request (https://support.zabbix.com/browse/ZBXNEXT-756).

[obrienmd]

Unfortunately, this no longer patches against 1.9.x trunk. I was able to make the PHP parts of the patch work in 1.9.x, but the C side goes over my head - I was able to find where it 'fit', but build failed as that C file now no longer includes find_table_by_value_type.