About a week after I configured Event Scheduler in MariaDB as in the previous article, I have data like below in the database.
SELECT port_id, `ifOutOctets`, `ifInOctets`,captured_at
FROM ports_hist
WHERE captured_at < '2024-08-10' AND captured_at > '2024-08-02'
AND port_id IN (3,4)
ORDER BY port_id, captured_at;
+---------+--------------+--------------+---------------------+
| port_id | ifOutOctets | ifInOctets | captured_at |
+---------+--------------+--------------+---------------------+
| 3 | 35039656033 | 132556547082 | 2024-08-02 00:31:00 |
| 3 | 35558892925 | 134706292659 | 2024-08-03 00:31:00 |
| 3 | 39006864177 | 141900456510 | 2024-08-04 00:31:00 |
| 3 | 39766996221 | 155890098068 | 2024-08-05 00:31:00 |
| 3 | 40262419929 | 157388859352 | 2024-08-06 00:31:00 |
| 3 | 40613651178 | 158126070440 | 2024-08-07 00:31:00 |
| 3 | 41111053655 | 159948605991 | 2024-08-08 00:31:00 |
| 3 | 41447751875 | 160794683442 | 2024-08-09 00:31:00 |
| 4 | 839009919291 | 25483043737 | 2024-08-02 00:31:00 |
| 4 | 848572779644 | 25702077812 | 2024-08-03 00:31:00 |
...
I have multiple rows for each port_id. The next task is calculating deltas of these numbers.
The task apparently includes pivoting. The above example should be transformed like below.
ifOutOctets
+---------+--------------+--------------+-------------+-----
| port_id | 2024-08-02 | 2024-08-03 | 2024-08-04 |
+---------+---------------------+---------------------+-----
| 3 | 35039656033 | 35558892925 | 39006864177 |...
| 4 | 839009919291 | 848572779644 | ... |
...
Though Excel would be the most popular pivoting tool, I used Python Pandas to make the process easier to regularly redo.
The code is below.
Code explanation
Basic idea is pivoting the whole table as described above.
Then, subtract value of yesterday from value of today. Since all values are counters, this calculation gives daily volume. I calculated 1-day, 3-days, and 7-days.
Repeat the same for some other fields like ifInOctets, ifOutErrors, ifInErrors.
Then, add some fields, such as ifAlias from ports for readability.
The generated table is saved as Excel file (can be opened with LibreOffice, too) for browsing like below.
