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

**URL:** <https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450>\
**Category:** Systems\
**Created:** [June 4, 2024, 9:59am UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450 "2024-06-04T09:59:42Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ricky99](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ricky99/32/11420_2.png) [@Ricky99](https://community.influxdata.com/u/Ricky99)\
**Post date:** [June 4, 2024, 9:59am UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/1 "2024-06-04T09:59:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [June 4, 2024, 3:59pm UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/2 "2024-06-04T15:59:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Ricky99](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ricky99/32/11420_2.png) [@Ricky99](https://community.influxdata.com/u/Ricky99)\
**Post date:** [June 4, 2024, 4:20pm UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/3 "2024-06-04T16:20:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [June 4, 2024, 5:27pm UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/4 "2024-06-04T17:27:03Z")

</div>

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

> [@Ricky99](#):
>
> the metering value keeps increase until the system reboots, when it restarts from zero

This is exactly what the [`increase()` function](https://docs.influxdata.com/flux/v0/stdlib/universe/increase/) was designed for. It’ll get rid of all the resets.

> [@Ricky99](#):
>
> How can I measure the traffic consumed from (for instance) the 16th of the month to the same day of the following month

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:

```javascript
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)

```

---

<div class="post-metadata">

**Author:** ![Ricky99](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ricky99/32/11420_2.png) [@Ricky99](https://community.influxdata.com/u/Ricky99)\
**Post date:** [June 6, 2024, 7:11am UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/5 "2024-06-06T07:11:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [June 6, 2024, 2:13pm UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/6 "2024-06-06T14:13:04Z")

</div>

> [@Ricky99](#):
>
> 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.

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?

> [@Ricky99](#):
>
> 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?

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?

---

<div class="post-metadata">

**Author:** ![Ricky99](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ricky99/32/11420_2.png) [@Ricky99](https://community.influxdata.com/u/Ricky99)\
**Post date:** [June 8, 2024, 12:24pm UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/7 "2024-06-08T12:24:18Z")

</div>

Hello @scott

> [@scott](#):
>
> 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?

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

 ![grafana_time series graph](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/f/f60bcdc926b34443ca1d03f2f3659e8e67246654.jpeg)

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

 ![grafana_time series graph-1](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/c/ccf4230b42b4016d52dab7ae3968a2d7c8ba1d62.jpeg)

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:

 ![grafana_time series graph-4](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/1/1a9992e18cfdfaa5c8e1d3a1cc16509575cae4ec.jpeg)

> [@scott](#):
>
> 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?

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.

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [June 10, 2024, 7:23pm UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/8 "2024-06-10T19:23:14Z")

</div>

> [@Ricky99](#):
>
> 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.

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:

```javascript
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()

```

> [@Ricky99](#):
>
> yes, it could be interesting to visualise the precedent (non solar) month, for instance:

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.

```javascript
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()

```

---

<div class="post-metadata">

**Author:** ![Ricky99](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ricky99/32/11420_2.png) [@Ricky99](https://community.influxdata.com/u/Ricky99)\
**Post date:** [June 10, 2024, 8:26pm UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/9 "2024-06-10T20:26:25Z")

</div>

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

 ![Edit panel - pfSense - Dashboards - Grafana](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/3/3643aa9b06408e6a845dae15557a05f3f991be29.jpeg)

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [June 10, 2024, 8:31pm UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/10 "2024-06-10T20:31:06Z")

</div>

> [@Ricky99](#):
>
> What I did not understand is: which interval the graph shows the increase? Hourly, daily, weekly….

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:

```javascript
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.

---

<div class="post-metadata">

**Author:** ![Ricky99](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ricky99/32/11420_2.png) [@Ricky99](https://community.influxdata.com/u/Ricky99)\
**Post date:** [June 11, 2024, 7:22am UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/11 "2024-06-11T07:22:26Z")

</div>

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

```auto
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:

1. I have two series, with the same name and color;  
[![](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/7/717875995b9746312c78d5c556106c6dc7f13810.png)](https://postimg.cc/Q9BLsSBF)

2. 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:  
[![](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/2/22822d22ea7cb93ac52768e507f10959bf023933.png)](https://postimg.cc/75Xj2j0C)

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.

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [June 11, 2024, 2:50pm UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/12 "2024-06-11T14:50:26Z")

</div>

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:

```javascript
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.** _

* * *

> [@Ricky99](#):
>
> I have two series, with the same name and color;

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:

```javascript
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.

> [@Ricky99](#):
>
> 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:

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.

---

<div class="post-metadata">

**Author:** ![Ricky99](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ricky99/32/11420_2.png) [@Ricky99](https://community.influxdata.com/u/Ricky99)\
**Post date:** [June 12, 2024, 7:18am UTC](https://community.influxdata.com/t/calculate-network-traffic-consumed-from-a-specific-day-of-month-and-reset-monthly/34450/13 "2024-06-12T07:18:30Z")

</div>

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:

[![](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/1/1f08adecb96d0f11c0d7114618de14ffc86ab4b2.png)](https://postimg.cc/WdjtdZMz)

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

Bye.