Count of memory usage breaches

[uzahid]

Hi, 

I'm trying to get my query to return a count of breaches of a threshold for the memory usage on a set of server. 

Unfortunately, i'm not quite being able to figure out that's possible. The current query I have returns the max memory usage for the given hosts. How would I go about adding a count() feature to this?

timeseries usage=max(dt.host.memory.usage), by:{host.name}, interval:1h
| filter host.name == "host1" OR host.name == "host2" OR host.name == "host3" OR host.name == "host4"

[krzysztof_hoja · Accepted solution]

You can use iterative expression to compare each value of returned timeseries against threshold and put in breaches table 1 when  it is crossed (otherwise 0). By summing elements of breaches table you get count of hours where memory usage was above certain level at least once:

timeseries usage=max(dt.host.memory.usage, default:0), interval:1h, by:{dt.entity.host}
, filter: { matchesValue(host.name,"*filter*", caseSensitive:false) }
| fieldsAdd breaches= if(usage[]>22,1,else:0)  // my ex. threshold = 22
| fieldsAdd breachesCount=arraySum(breaches)

result of this query on my data looks like this:

you can also do the operation in single line using iCollectArray:

| fieldsAdd breachesCount= arraySum(iCollectArray(if(usage[]>22,1,else:0)))

Kris