Avg() and conditional in a query for timechart

[asarolkar]

I am doing a simple tiimechart for the average value of a field from a log (this part is trivial)

sourcetype="syslog" | timechart span=15m avg(Value) as averageValue by host 

I need to set a condition on "averageValue" - a threshold - such that this filters out all entries for which averageValue > 3.00

The following solution (which simply adds a filter) to my requirement is not currently working 😞

sourcetype="syslog" | timechart span=15m avg(Value) as averageValue by host | where averageValue GREATERTHANSYMBOL 3.00

I also wonder what this query does really:

sourcetype="syslog" | search Value GREATERTHANSYMBOL 3.00 | timechart span=15m avg(Value) as averageValue by host

Any suggestions are welcome 🙂

[ashleyherbert]

The problem you have is that the 'timechart' command makes the columns the values of the 'host' field. So when you do your 'where' command, there is no averageValue column to search.

You could change the where command to filter based on the hostnames (assuming there's not a lot of them), such as:

... | where host1 > 3 OR host2 > 3

I'm not sure I understand why you would want to filter out values from a timechart, it means you will have gaps in your chart.
If you're just wanting to view it as a table, using the stats command may be better. Eg:

sourcetype="syslog" | bucket _time span=15m | stats avg(Value) as averageValue by _time,host | where averageValue > 3

Cheers,

Ashley

[lguinn2]

I think the problem is that a timechart wants to have data for each time interval, and you are trying to eliminate time intervals that don't meet a criteria. I think you can do it, but the chart will be weird even if it works. You could try this instead:

sourcetype="syslog" | 
bucket _time span=15m |
stats avg(Value) as averageValue by host _time |
where averageValue > 3.0

The following search:

sourcetype="syslog" | search Value > 3.00 | timechart span=15m avg(Value) as averageValue by host

would be more efficient if it were written:

sourcetype="syslog" Value > 3.00 | timechart span=15m avg(Value) as averageValue by host

This search retrieves only syslog events with a Value greater than 3, and then computes a timechart of those values. This is mathematically completely different from computing an average value of all syslog events...