when no events or any field contains contains zero for past hour through an alert using tstats

[kirrusk]

Hi,

using the below query to trigger an alert.

| tstats count WHERE index=your_index AND(TMPFIELD="FIELD1" OR TMPFIELD="FIELD2" OR TMPFIELD="FIELD3") GROUPBY index TMPFIELD _time latest=-1h@h earliest=@h
| timechart count(eval(FIELD1)) AS FIELD1 count(eval(FIELD2)) AS FIELD2 count(eval(FIELD3)) AS FIELD3 
| append [ index=_internal latest=-1h@h earliest=@h | head 1 | eval FIELD1=0, FIELD2=0, FIELD3=0| fields _time FIELD1 FIELD2 FIELD3 ]
| stats sum(FIELD1) AS FIELD1 sum(FIELD2) AS FIELD2 sum(FIELD3) AS FIELD3 BY _time
| where FIELD1=0 OR FIELD2=0 OR FIELD3=0

But problem is, it's giving zero in the table if data is present in the field also

EXAMPLE,
FIELD1    FIELD2   FIELD3
0                0               0

But in reality, field3 has values
FIELD1    FIELD2   FIELD3
0                0               59

so it should through alert as well, because FIELD1 & FIELD2 are Zero.
@gcusello 

[gcusello · Accepted solution]

Hi @kirrusk,

let me understand: you want an alert if one of the values is zero, is it correct or a different condition?

in this case the condition "| where FIELD1=0 OR FIELD2=0 OR FIELD3=0" is correct for all the conditions: because it triggers the alert when one of the FIELD* is zero, so also when all the FIELD* are zero.

If you want different messages for the found condition you could use an eval command before the where command:

| tstats count WHERE index=your_index AND(TMPFIELD="FIELD1" OR TMPFIELD="FIELD2" OR TMPFIELD="FIELD3") GROUPBY index TMPFIELD _time latest=-1h@h earliest=@h
| timechart count(eval(FIELD1)) AS FIELD1 count(eval(FIELD2)) AS FIELD2 count(eval(FIELD3)) AS FIELD3 
| append [ index=_internal latest=-1h@h earliest=@h | head 1 | eval FIELD1=0, FIELD2=0, FIELD3=0| fields _time FIELD1 FIELD2 FIELD3 ]
| stats sum(FIELD1) AS FIELD1 sum(FIELD2) AS FIELD2 sum(FIELD3) AS FIELD3 BY _time
| eval status=case(FIELD1=0 AND FIELD2>0 AND FIELD3>0,"Only FIELD1=0", FIELD1>0 AND FIELD2=0 AND FIELD3>0,"Only FIELD2=0", FIELD1=0 AND FIELD2=0 AND FIELD3=0,"Only FIELD3=0", FIELD1=0 AND FIELD2=0 AND FIELD3>0,"Only FIELD1 and FIELD2=0", FIELD1=0 AND FIELD2>0 AND FIELD3=0,"Only FIELD1 and FIELD3=0", FIELD1>0 AND FIELD2=0 AND FIELD3=0,"Only FIELD2 and FIELD3=0", FIELD1=0 AND FIELD2=0 AND FIELD3=0,"All the FIELDs=0"
| where FIELD1=0 OR FIELD2=0 OR FIELD3=0

 Ciao.

Giuseppe