How to calculate the count for each field in the past 3 days?

[rick1168]

how to  calculate the count for each field in the past 3 days. If the count for all 3 days is 0, and the count for today is greater than 0, then the command triggers an alert that shows log. 

[rick1168]

i need following case to be searched, (past-3-days count=0 and today count >0)

                   past-3- days    today

field1           0                        4

field2          0                          1

.....

then show the table

_time    field   _raw

[yuanliu]

---

then show the table

_time    field   _raw

---

Note that expecting _raw in such an alert is very unreasonable and can be quite expensive.  In a simpler form following @bowesmana's recipe, you may get away with something like

index=... earliest=-3d@d
| bin _time span=1d@d
``` Calculates the count for a field by day ```
| stats count values(_raw) as _raw by field _time
``` Now calculate today's value and the total ```
| stats values(_raw) as _raw sum(eval(if(_time=relative_time(now(), "@d"),count, 0))) as today sum(count) as total by field
``` And set a field to be TRUE or FALSE to alert ```
| where today > 0 AND total - today == 0

In this form, _raw is not an ordered list, but a lexicographic one.

If you really, really need _raw in its raw form, you can consider using subsearch to limit values of fields to only those in alerts.  Then you must consider the cost of subsearch.

[rick1168]

 if the search result of "past days count=0 and today count>0" then trigger another search to show count >0 log as _time field1 _raw

[yuanliu]

 if the search result of "past days count=0 and today count>0" then trigger another search to show count >0 log as _time field1 _raw

---

Yes, that's exactly what a subsearch can do.

index=... earliest=-3d@d [search index=... earliest=-3d@d
| bin _time span=1d@d
``` Calculates the count for a field by day ```
| stats count by field _time
``` Now calculate today's value and the total ```
| stats sum(eval(if(_time=relative_time(now(), "@d"),count, 0))) as today sum(count) as total by field
``` And set a field to be TRUE or FALSE to alert ```
| where today > 0 AND total - today == 0
| fields field]

This use of subsearch is very inefficient.  Efficiency aside, I highly doubt if raw events is of value in an E-mail alert if today's finding are going to be more than a couple of events.

If I am to receive an alert like this, I would rather it simply tells me which fields are triggering this behavior so I can go back to a search window or a dashboard or a report to review event details.  If I really want a little more in the E-mail itself, aggregation of a handful of most concerned fields. 

If there are going to be only a couple of events to ever satisfy the criteria, you can use an alternative to produce an ordered list of _raw - provided it is really desirable by recipients.

index=... earliest=-3d@d
| bin _time span=1d@d
``` Calculates the count for a field by day ```
| stats count list(_raw) as _raw by info _time
``` Now calculate today's value and the total ```
| stats list(_raw) as _raw sum(eval(if(_time=relative_time(now(), "@d"),count, 0))) as today sum(count) as total by info
``` And set a field to be TRUE or FALSE to alert ```
| where today > 0 AND total - today == 0

This way, you don't need to run two searches sequentially.

BTW, last time I used field name "info" in place of "field".  This is corrected.

[bowesmana]

You'll need to a bit more specific when you say count for each field, but you could do something like this

index=... earliest=-3d@d latest=now
| bin _time span=1d  
``` Calculates the count for a field by day ```
| stats count by _time field
``` Now calculate today's value and the total ```
| stats sum(eval(if(_time=relative_time(now(), "@d"),count, 0))) as today sum(count) as total
``` And set a field to be TRUE or FALSE to alert ```
| eval alert=if(today>0 AND total-today=0, "TRUE", "FALSE")

Do this fit what you're trying to do?