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>Augment Data] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3>Augment Data] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result
logscale
type="network_connection"
| groupBy([host], function=[
    sum(field="bytes_in", as=InboundTraffic),
    sum(field="bytes_out", 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 multiple 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:

@timestamptypehostbytes_inbytes_out
1686837825000network_connectionhost-a150000008500000
1686837825000network_connectionhost-a89000004200000
1686837825000network_connectionhost-b2500000012000000
1686837826000network_connectionhost-c9500000045000000
1686837826000network_connectionhost-a120000006800000
1686837826000network_connectionhost-b180000009500000
1686837827000network_connectionhost-c8500000042000000
1686837827000network_connectionhost-d50000002800000
1686837827000network_connectionhost-b2200000011000000
1686837828000network_connectionhost-c10500000052000000
1686837828000network_connectionhost-d65000003200000
1686837828000network_connectionhost-a98000005100000

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3>Augment Data] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3>Augment Data] 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
    type="network_connection"

    Filters events to include only those where type equals network_connection.

  3. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3>Augment Data] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
    flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3>Augment Data] 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([host], function=[
    sum(field="bytes_in", as=InboundTraffic),
    sum(field="bytes_out", as=OutboundTraffic)
    ])

    Groups the data by the host field and calculates two aggregate values: the sum of bytes_in stored in a field named InboundTraffic, and the sum of bytes_out stored in a field named OutboundTraffic.

  4. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3>Augment Data] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 3 fill:#ff0000,stroke-width:4px,stroke:#000;
    flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3>Augment Data] 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 by adding the values from the InboundTraffic and OutboundTraffic fields.

  5. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3>Augment Data] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 4 fill:#ff0000,stroke-width:4px,stroke:#000;
    flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3>Augment Data] 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:

hostInboundTrafficOutboundTrafficTotalTraffic
host-c285000000139000000424000000
host-b650000003250000097500000
host-a457000002460000070300000
host-d11500000600000017500000

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