Aggregating data points from metric index?

[adsquaired]

Hello,

collectd is the mechanism to obtain information about network traffic (octets per second).

The search to create a visualization of the data in a dashboard is below. 

| mstats rate_avg("octets.*") WHERE index="network" chart=true host="device-*" span=5m by host
| fields - _span*
| rename "rate_avg(octets.rx): *" AS "in * bit/s"
| rename "rate_avg(octets.tx): *" AS "out * bit/s"
| foreach * [eval <<FIELD>>='<<FIELD>>' * 8 ]

The issue I am facing is when trying to graph time frames wider than a few months. There are to many data points and the results are truncated. I have played with charting.chart.resultTruncationLimit but that only gets so far.

Note: the span of 5m cannot be changed or the data is skewed.

Is there a way to create graphs maintaining the time span but per day or per month?

For example,

• Display a graph of the last 30 days but summarize per day or per week
• Display a graph of the last year but summarize per month or per week.

Thanks in advance. 

[Tom_Lundie]

Yes, you've hit the nail on the head when you talk about summarising the data. You'll need to decide how to aggregate or roll-up your search into larger spans. For example do you want to display the average for that range? Or the max?

Either way, this can all be decided dynamically using a | timechart and subsearch in tandem: In this example, the avg() will be taken per week if the search is a year long, or per day if the search is a month long (otherwise the default is 5min).

| timechart 
    [| makeresults 
    | addinfo 
    | eval search_range = (info_max_time - info_min_time)/(24*3600) 
    | eval search = "avg(*) as * " + case(search_range>=365, "span=1week", search_range>=30, "span=1day", 1=1, span="5min") 
    | table search]

[adsquaired]

Thanks for the information @Tom_Lundie. Unfortunately, changing the span skews the results when creating  graphs of these metrics. If the span is not left at 5 minutes the rate average changes and does not display spikes accurately. 

I'm wondering if it would be easier to record the max value for each day or week and use those values to build yearly the graph. Appending  | stats max(*) AS "*" give me the highest record number for the search period.

Now, the question that comes to mind is how can I take those snapshots and create a larger graph from them.

For example, take the max value of data points over a 24 hour period for last seven days and create a graph with those data points. Display 7 (days) connected points on a single graph. Then, do the same thing for a month. Display 4 (each week) connected datapoints for the month. Finally, the end result would be a graph of the year. Show 52 (weeks) points on a graph.

I am not 100% on the best way to achieve what I'm trying to do here which is create graphs based on network interface metrics. Refresh from my first post. Weekly and monthly graphs work fine doing it the way I am but when trying to create a graph for the year the results are truncated.  

Thanks.

[Tom_Lundie]

Hi @adsquaired,

I understand what you're saying. I'm not necessarily suggesting that you change the span value in the mstats command. The | mstats is calculating the average rate on a 5-minutely basis.

To aggregate the averages how you suggest, you'll need to use an additional command. | timechart is going to be the one for this. For example, this will give you the maximum average rate across 5 minute windows for each week.

| mstats rate_avg("octets.*") WHERE index="network" chart=true host="device-*" span=5m by host
| fields - _span*
| rename "rate_avg(octets.rx): *" AS "in * bit/s"
| rename "rate_avg(octets.tx): *" AS "out * bit/s"
| foreach * [eval <<FIELD>>='<<FIELD>>' * 8 ]
| timechart span=1week max(*) as * 

When layering statistics on top of each other like this, it's important to bare in mind what they're doing. This is not the maximum rate at any given moment, it's the maximum average.

[adsquaired]

Looks like I have figured it out. 

| mstats rate_avg("octets.*") WHERE index="network" chart=true host="device-*" span=5m by host
| fields - _span*
| rename "rate_avg(octets.rx): *" AS "in * bit/s"
| rename "rate_avg(octets.tx): *" AS "out * bit/s"
| foreach * [eval <<FIELD>>='<<FIELD>>' * 8 ]
| timechart span=1w max(*) as "*" 

Adding the last pipe allows me to select "max" value for the time span.

For example, this query has a preset of 365 days. The result is 52 data points. Each one of those data points show the max value per week. 

If I set a preset of 30 days and updated the time span next to timechart to 1d I would get 30 data points. The max value for each day of the 30. 

[Tom_Lundie · Accepted solution]

Looks like we were replying at the same time, nice one!

On this particular point: If I set a preset of 30 days and updated the time span next to timechart to 1d I would get 30 data points. The max value for each day of the 30. 

This is exactly what I was going after in my first post. If you want that aggregation span to be dynamic with the search window, then you can use a sub-search to generate it. For example:

Replacing:

| timechart span=1w max(*) as "*" 

With:

| timechart 
    [| makeresults 
    | addinfo 
    | eval search_range = (info_max_time - info_min_time)/(24*3600) 
    | eval search = case(search_range>=365, "span=1week", search_range>=30, "span=1day", 1=1, span="5min") + " max(*) as *"
    | table search]

This will change that max() aggregation span to 1week, 1day, or 5min based on the search window being 1year, 1month, or anything smaller, respectively. This might be overcomplicated for your use-case, but something to try nonetheless.

[adsquaired]

@Tom_Lundie, your solution works. I like the dynamic nature of it as well. Thanks for the feedback.