Calculate network traffic consumed from a specific day of month and reset monthly

Ricky99 · June 4, 2024, 9:59am

Hello,
My LTE connection is metered and reset every month on a specific date, which is not the first day of month.
The network traffic consumed is measured by pfSense and the metering value keeps increase until the system reboots, when it restarts from zero.
How can I measure the traffic consumed from (for instance) the 16th of the month to the same day of the following month, then reset and restart from 0?
I don’t want to update the “start” value of the query every month.

Thank you.

Edit 04/06/2024 18:20 CET
InfluxDB Version: 2.7.6
Query language: Flux

scott · June 4, 2024, 3:59pm

@Ricky99 What version of InfluxDB are you using? What query language are you using?

Ricky99 · June 4, 2024, 4:20pm

Hello Scott, thanks for your reply.
I forgot to add those details in the opening message, but they are shown now.

scott · June 4, 2024, 5:27pm

@Ricky99 Perfect, thanks. I’ll try to answer each requirement one by one.

This is exactly what the increase() function was designed for. It’ll get rid of all the resets.

For this case case, I’d use aggreateWindow() with the last function to return the last value in each window. To offset the month calculation, you can use the offset parameter to specify a 16d offset. With a calendar-month window period and a 16d offset, each window will start on the 16th day of each calendar month.

The query would look something like this:

from(bucket: "example-bucket")
    |> range(start: -1y)
    |> filter(fn: (r) => r._measurement == "example-measurement")
    |> filter(fn: (r) => r._field == "example-field")
    |> increase()
    |> aggregateWindow(every: 1mo, fn: last, offset: 16d)

Ricky99 · June 6, 2024, 7:11am

Hello @scott,
many thanks for your help. I learned some new stuff in the code.
I gave a try with your suggestion but it is only a partial solution to my needs. I missed to specify in a better way my whole target.

What is good:
It effectively calculates the total traffic since the reset date based on the offset. It is OK for a single stat graph style, not to so good for a standard time series graph style in grafana because of a straight line being drawn.

What I also wish
The data should be visualized into a Time series graph in grafana.
Let’s say I wish to visualize the hourly/daily increase starting from the reset date of month #1 to the following reset date in month #2.
Could this be achievable in any way?

Thank you so much.
Regards

scott · June 6, 2024, 2:13pm

Can you define what you mean by “standard time series graph style”? Do you have screenshots of what you’re getting vs what you want?

Yes this is achievable, but I think I need to know a little more about how you want the data to be visualized. Are you looking to have an individually-colored line for each month/period? If so, do you want them to follow each other consecutively or be overlaid so you can compare the daily increase across months? Do you want to see the increase overtime or just return the final value for each month/period?

Ricky99 · June 8, 2024, 12:24pm

Hello @scott

I meant this kinda graph (in Grafana, but it is similar in the Influx GUI):

Now, by applying your code, I get something like this:

It starts from 18th of May (offset 17d) to the current day (OK), but the graph shows a straight line, so I cannot appreciate how the inbound traffic grows up during the timeframe.
This could be OK only in the case I wanted to visualise the information not in a graph but with a single stat panel, just like this:

Well, in first place, I would like to have a graph with a 30-day time frame showing the hourly/daily (no matter) increase starting from the last reset date and… yes, it could be interesting to visualise the precedent (non solar) month, for instance:

Current Month:
From 18th May to 17th June

Previous Month (as comparison):
From 18th April to 17th May

Hope I was a little bit clearer in my needs.
Thank you very much.

scott · June 10, 2024, 7:23pm

Ok, I understand your use case better. The reason you’re getting a straight line is because I assumed your query’s time range would span multiple months, but it doesn’t really. aggregateWindow() returns a single value from each window, so, with they time range you’re using, the query only returns two values and the straight line is rendered as the change between those two values. So the following query should give you what you’re looking for:

from(bucket: "pfsense")
    |> range(start: 2024-05-16T22:00:00Z)
    |> filter(fn: (r) => r._measurement == "net")
    |> filter(fn: (r) => r._field == "em5")
    |> filter(fn: (r) => r._field == "bytes_recv")
    |> window(every: 1mo, offset: 17d)
    |> increase()

To do this, you can just use experimental.alignTime() to align the timestamps of each window to a common start time. This way, when graphed, the months are overlaid.

_Note: The default time to align to is the Unix epoch (1970-01-01T00:00:00Z), but you can specify a time to align to with alignTime()'s alignTo parameter.

import "experimental"

from(bucket: "pfsense")
    |> range(start: 2024-05-16T22:00:00Z)
    |> filter(fn: (r) => r._measurement == "net")
    |> filter(fn: (r) => r._field == "em5")
    |> filter(fn: (r) => r._field == "bytes_recv")
    |> window(every: 1mo, offset: 17d)
    |> increase()
    |> experimental.alignTime()

Ricky99 · June 10, 2024, 8:26pm

Hello,
I don’t think it works properly. At the moment I get an issue in Grafana due to many data points to be rendered, so I cannot confirm to be the right solution. I should prepare a long term database to work with.
What I did not understand is: which interval the graph shows the increase? Hourly, daily, weekly….

For instance: I get this result

scott · June 10, 2024, 8:31pm

As is, it just returns the raw, un-downsampled data. Also, the “Data outside time range” error is cause by experimental.alignTime() aligning the start times to the Unix epoch. The default behavior. Try this:

import "experimental"

from(bucket: "pfsense")
    |> range(start: 2024-05-16T22:00:00Z)
    |> filter(fn: (r) => r._measurement == "net")
    |> filter(fn: (r) => r._field == "em5")
    |> filter(fn: (r) => r._field == "bytes_recv")
    |> aggregateWindow(every: 1h, fn: last)
    |> window(every: 1mo, offset: 17d)
    |> increase()
    |> experimental.alignTime(alignTo: v.timeRangeStart)

In this case, you use aggregateWindow() to downsample the data into less points. The every paramenter in aggregateWindow() defines the interval to show in the graph. The last function defined in the fn parameter returns the last row from each interval. alignTime now aligns each monthly window to the start time of your query range.

Ricky99 · June 11, 2024, 7:22am

Hello @scott, I have just tried your suggestion, but I cannot confirm it works per my needs.

import "experimental"
import "timezone"
option location = timezone.location(name: "Europe/Rome")

from(bucket: "pfsense")
  |> range(start: 2024-05-08T22:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "net")
  |> filter(fn: (r) => r["interface"] == "em5")
  |> filter(fn: (r) => r["_field"] == "bytes_recv")
  |> aggregateWindow(every: 1h, fn: last, createEmpty: false, offset: 8d)
  |> window(every: 1mo, offset: 8d)
  |> increase()
  |> experimental.alignTime(alignTo: v.timeRangeStart)

I find some issues with the above:

I have two series, with the same name and color;
When I move the cursor to the last record of the precedent month it gives me a date which is out of the time frame wanted (From 9th May to 8th June), as in the picture below:

It should stop at 8th June 24:00:00, but it overtakes that date.
It is not necessary to proceed further with the analysis.
We can close the thread.

Thank you.

scott · June 11, 2024, 2:50pm

To address your original need, just remove the experimental.alignTime function. This will give a graph that shows the increase per period (month starting on your defined offset) with no data overlapping:

import "experimental"
import "timezone"

option location = timezone.location(name: "Europe/Rome")

from(bucket: "pfsense")
  |> range(start: 2024-05-08T22:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "net")
  |> filter(fn: (r) => r["interface"] == "em5")
  |> filter(fn: (r) => r["_field"] == "bytes_recv")
  |> aggregateWindow(every: 1h, fn: last, createEmpty: false)
  |> window(every: 1mo, offset: 8d)
  |> increase()

The rest of this response only applies if you want to overlay the periods to see how they compare.

This graph is generated using the technique used to overlay periods. Each series represents a month starting on your defined offset. The color and labeling of the series is determined by the grouping of the data. You need to enrich the data with a period identifier and then group by that identifier. I’d normally suggest using the month as the identifier, but where your periods don’t land on calendar month boundaries, that won’t necessarily work. But let’s assume we assign a period using the year and the month the period starts:

import "date"
import "experimental"
import "timezone"

option location = timezone.location(name: "Europe/Rome")

offsetDays = 8
offsetDuration = duration(v: "${offsetDays}d")

assignPeriod = (t) => {
    _month = if date.monthDay(t: t) >= offsetDays then date.month(t: t) else date.month(t: t) - 1
    _adjMonth = if _month == 0 then 12 else _month
    _year = if _month == 0 then date.year(t: t) - 1 else date.year(t: t)

    return "${_year}-${_adjMonth}"
}

from(bucket: "pfsense")
    |> range(start: 2024-05-08T22:00:00Z)
    |> filter(fn: (r) => r["_measurement"] == "net")
    |> filter(fn: (r) => r["interface"] == "em5")
    |> filter(fn: (r) => r["_field"] == "bytes_recv")
    |> aggregateWindow(every: 1h, fn: last, createEmpty: false)
    |> window(every: 1mo, offset: offsetDuration)
    |> increase()
    |> map(fn: (r) => ({r with period: assignPeriod(t: r._time)}))
    |> experimental.group(columns: ["period"], mode: "extend")
    |> experimental.alignTime(alignTo: v.timeRangeStart)

That should solve the grouping problem.

This is due to the alignTime() function. It shifts all the timestamps in each table so that the first timestamp is at the specified alignTo time and all subsequent timestamps are based on that new starting time.

Ricky99 · June 12, 2024, 7:18am

Hello @scott,
thanks for the time you spent on this, but it is not what I was intended. This is with the latest code, a little bit messed up:

We can stop.
Thank you, I will try other ways.

Bye.

Topic		Replies	Views
Monthly usage based on daily cumulative values InfluxDB 2	3	537	November 4, 2022
Month duration line of Consume Performance Dashboards	0	636	May 3, 2019
Monthly usage based on daily (cumulative) values Fluxlang	2	546	November 2, 2022
Continuous query to get monthly energy consumption Telegraf influxdb , query	3	1624	July 22, 2019
Interface Bandwidth - Machine Learning Telegraf influxdb , kapacitor	2	1950	August 30, 2018

Calculate network traffic consumed from a specific day of month and reset monthly

Related topics