Date: Fri, 29 Mar 2024 08:04:01 +0000 (UTC)
Message-ID: <407060345.4055.1711699441409@skald.opmantek.com>
Subject: Exported From Confluence
MIME-Version: 1.0
Content-Type: multipart/related;
boundary="----=_Part_4054_298924024.1711699441407"
------=_Part_4054_298924024.1711699441407
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Content-Location: file:///C:/exported.html
How to add utilisation columns to CSV-exported interface data
How to add utilisation columns to CSV-exported interface data
Overview
NMIS interface utilisation graphs display the utilisation of an interfac=
e in percent relative to the configured interface bandwidth. If you click o=
n the Export link, NMIS exports the underlying raw time-series data, which =
contains bytes per second. This page describes how to amend such export dat=
a with utilisation figures like what the graph contains.
P=
rocedure
- The raw export does not contain node configuration information and thus=
no interface bandwith, but this number is crucially required to compute pe=
rcentages.
On the utilisation graph page you'll find one or two "Interface Speed" entr=
ies (e.g. "1 Gbps
" or "IN: 10 Mbps OUT: 10 Mbps
")=
.
Note both values and convert them to full numeric form (bits/second). For e=
xample, 1 Gbps =3D 1e9 =3D 1000 000 000; 10 Mbps =3D 1e7 =3D 10 000 000.
- Perform the download/export
Depending on your desktop environment this operation may already open your =
spreadsheet program with the CSV file in question; otherwise, open the =
; CSV file with your spreadsheet program.
- Add a formula column for input utilisation to your spreadsheet
Adding a header to the first row is recommended but not required.
Goto row 2 of said column, enter the formula editor (usually by typing "=3D") and enter the following formula: =3DA2*8*100/N<=
/code>, but replace N by the interface input speed.
In both Excel and Open/LibreOffice you may write those large numbers altern=
ately in full or shortened powers-of-10 form: 1000000
works as=
well as 1e6
.
For example, for a 100 Mbps interface this formula should look like =
=3DA2*8*100/100000000
.
A quick breakdown of what this does: A2 references the first column in row =
2, which is the ifInOctets
column, which is (avg) bytes per se=
cond. But speeds are in bits per second, therefore multiply by 8 to convert=
the bytes to bits. Dividing by the interface speed produces the utilisatio=
n as a fraction between 0.0 and 1.0. But utilisation is meant to be shown i=
n percent instead of as a fraction, therefore multiply by 100.
- Add a formula column for output utilisation
The formula is extremely similar: =3DC2*8*100/M,
replacin=
g M by the interface output speed.
The differences are that it references the third column in row 2 (which is =
the output bytes) and that the divisor is the interface output speed (which=
might be different from the interface input speed):
For example, for a 10 Mbps interface this should look like =3DC2*8*10=
0/10000000
.
- Copy the formulas for each data row in your spreadsheet.
This process differs slightly between spreadsheet systems; in OpenOffice/LibreOffice you select the formula cells and=
click the right bottom corner, then drag the selector rectangle down to in=
clude all rows. In Microsoft Excel, you can perfor=
m the same click-and-drag operation as in LibreOffice, or copy and paste th=
e two formulas with Ctrl-C, then select the target rectangle and insert the=
formulas with Ctrl-V.
- Verify that your spreadsheet program has properly copied the formulas, =
not the computed contents.
Generally, clicking on one of the copied formula cells should suffice: the =
cell contents/details display should show a formula, ie. =3Dsomething=
and not a number.
Example (In and Out speed are 100000000) :
ifInOctets |
ifOperStatus |
ifOutOctets |
time |
date |
InputUtil |
OutputUtil |
135362.737 |
100 |
12898.24255 |
1468897200 |
19/07/16 13:00 |
=3DA2*=
8*100/100000000 |
=3DC2*=
8*100/100000000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
------=_Part_4054_298924024.1711699441407--