How to add utilisation columns to CSV-exported interface data

Overview

NMIS interface utilisation graphs display the utilisation of an interface in percent relative to the configured interface bandwidth. If you click on the Export link, NMIS exports the underlying raw time-series data, which contains bytes per second. This page describes how to amend such export data with utilisation figures like what the graph contains.

Procedure

  1. The raw export does not contain node configuration information and thus no interface bandwith, but this number is crucially required to compute percentages.
    On the utilisation graph page you'll find one or two "Interface Speed" entries (e.g. "1 Gbps" or "IN: 10 Mbps OUT: 10 Mbps").
    Note both values and convert them to full numeric form (bits/second). For example, 1 Gbps = 1e9 = 1000 000 000; 10 Mbps = 1e7 = 10 000 000.
  2. 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.
  3. 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 "=") and enter the  following formula: =A2*8*100/N, but replace N by the interface input speed.
    In both Excel and Open/LibreOffice you may write those large numbers alternately 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 =A2*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 second. But speeds are in bits per second, therefore multiply by 8 to convert the bytes to bits. Dividing by the interface speed produces the utilisation as a fraction between 0.0 and 1.0. But utilisation is meant to be shown in percent instead of as a fraction, therefore multiply by 100.
  4. Add a formula column for output utilisation
    The formula is extremely similar: =C2*8*100/M, replacing 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 =C2*8*100/10000000.
  5. 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 include all rows. In Microsoft Excel, you can perform the same click-and-drag operation as in LibreOffice, or copy and paste the two formulas with Ctrl-C, then select the target rectangle and insert the formulas with Ctrl-V.
  6. 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. =something and not a number.

Example (In and Out speed are 100000000) :

ifInOctetsifOperStatusifOutOctetstimedateInputUtilOutputUtil
135362.73710012898.24255146889720019/07/16 13:00

=A2*8*100/100000000

=C2*8*100/100000000