Calculate Total Network Bandwidth Per Host

Analyze network traffic patterns using the groupBy() function with sum()

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3["Expression"] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result
logscale
event_simpleName="NetworkConnectStats"
groupBy([ComputerName], function=[
        sum(field="BytesReceived", as=InboundTraffic),
        sum(field="BytesSent", as=OutboundTraffic)
        ])
TotalTraffic := InboundTraffic + OutboundTraffic
sort(field="TotalTraffic", order="desc")

Introduction

The groupBy() function can be used to perform aggregate calculations on grouped data, allowing analysis of metrics like network traffic across different hosts or systems.

In this example, the groupBy() is used with nested sum() functions to calculate total inbound and outbound network traffic per host, followed by calculating the total bandwidth consumption.

Example incoming data might look like this:

@timestampevent_simpleNameComputerNameBytesReceivedBytesSent
1686837825000NetworkConnectStatsDESKTOP-A1150000008500000
1686837825000NetworkConnectStatsDESKTOP-A189000004200000
1686837825000NetworkConnectStatsLAPTOP-B22500000012000000
1686837826000NetworkConnectStatsSERVER-C39500000045000000
1686837826000NetworkConnectStatsDESKTOP-A1120000006800000
1686837826000NetworkConnectStatsLAPTOP-B2180000009500000
1686837827000NetworkConnectStatsSERVER-C38500000042000000
1686837827000NetworkConnectStatsDESKTOP-D450000002800000
1686837827000NetworkConnectStatsLAPTOP-B22200000011000000
1686837828000NetworkConnectStatsSERVER-C310500000052000000
1686837828000NetworkConnectStatsDESKTOP-D465000003200000
1686837828000NetworkConnectStatsDESKTOP-A198000005100000

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3["Expression"] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    event_simpleName="NetworkConnectStats"

    Filters events to include only those where event_simpleName equals NetworkConnectStats.

  3. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3["Expression"] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    groupBy([ComputerName], function=[
        sum(field="BytesReceived", as=InboundTraffic),
        sum(field="BytesSent", as=OutboundTraffic)
        ])

    Groups the data by the ComputerName field and calculates two aggregate values: the sum of BytesReceived stored in a field named InboundTraffic, and the sum of BytesSent stored in a field named OutboundTraffic.

  4. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3["Expression"] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 3 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    TotalTraffic := InboundTraffic + OutboundTraffic

    Creates a new field named TotalTraffic containing the values from the InboundTraffic and OutboundTraffic fields.

  5. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3["Expression"] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 4 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    sort(field="TotalTraffic", order="desc")

    Sorts the results based on the TotalTraffic field in descending order (order=desc), showing hosts with highest bandwidth consumption first.

  6. Event Result set.

Summary and Results

The query is used to analyze network bandwidth consumption patterns across different hosts in the network.

This query is useful, for example, to identify hosts consuming excessive bandwidth, monitor network usage patterns, or detect potential network-intensive applications or anomalies.

Sample output from the incoming example data:

ComputerNameInboundTrafficOutboundTrafficTotalTraffic
SERVER-C3285000000139000000424000000
LAPTOP-B2650000003250000097500000
DESKTOP-A1457000002460000070300000
DESKTOP-D411500000600000017500000

Note that the traffic values are in bytes and that each row represents the aggregated traffic for a unique host