Sum the sub buckets returned results in transform

Elastic Stack Elasticsearch
1

Here is my transform preview:

POST _transform/_preview
{
  "source": {
    "index": ".ds-metrics-ap.clients-default-*",
    "query": {
      "range": {
        "@timestamp": {
          "gte": "now/d",
          "lt": "now/d+1d"
        }
      }
    }
  },
  "pivot": {
    "group_by": {
      "client_mac": {
        "terms": { "field": "client.mac" }
      },
      "client_ip": {
        "terms": { "field": "client.ip" }
      },
      "client_name": {
        "terms": { "field": "client.name" }
      }
    },
    "aggregations": {
      "sessions": {
        "terms": {
          "field": "connection.session",
          "size": 100
        },
        "aggregations": {
          "last_bytes_received": {
            "top_metrics": {
              "metrics": { "field": "client.network.bytes.received" },
              "sort": { "@timestamp": "desc" }
            }
          },
          "last_bytes_sent": {
            "top_metrics": {
              "metrics": { "field": "client.network.bytes.sent" },
              "sort": { "@timestamp": "desc" }
            }
          }
        }
      }
    }
  },
  "description": "Sum of last bytes sent/received per session per client (for today)"
}

And i have results of this is like:

{
      "client_mac": "b0:60:99:db:73:3d",
      "sessions": {
        "1": {
          "last_bytes_sent": {
            "client.network.bytes.sent": "244804661"
          },
          "last_bytes_received": {
            "client.network.bytes.received": "21340206"
          }
        },
        "2": {
          "last_bytes_sent": {
            "client.network.bytes.sent": "23701946"
          },
          "last_bytes_received": {
            "client.network.bytes.received": "13354132"
          }
        },
        "3": {
          "last_bytes_sent": {
            "client.network.bytes.sent": "195071281"
          },
          "last_bytes_received": {
            "client.network.bytes.received": "16804618"
          }
        },
        "4": {
          "last_bytes_sent": {
            "client.network.bytes.sent": "195268002"
          },
          "last_bytes_received": {
            "client.network.bytes.received": "16948694"
          }
        },
        "5": {
          "last_bytes_sent": {
            "client.network.bytes.sent": "195466002"
          },
          "last_bytes_received": {
            "client.network.bytes.received": "17176374"
          }
        },
        "6": {
          "last_bytes_sent": {
            "client.network.bytes.sent": "195618273"
          },
          "last_bytes_received": {
            "client.network.bytes.received": "17347029"
          }
        },
        "7": {
          "last_bytes_sent": {
            "client.network.bytes.sent": "195799497"
          },
          "last_bytes_received": {
            "client.network.bytes.received": "17525731"
          }
        }
      },
      "client_ip": "192.33.250.72",
      "client_name": "test"
    }

I need sum of all session last received and sent values per client, how do it in current transform

Thanks
transforms Elastic Stack > Elasticsearch Elastic Search

2

I don’t think this is currently directly supported using a single transform. We would need to add support for sum_bucket, which you could open an enhancement request for if you’d like to go that route.

Here are a few different options for workarounds:

  1. Create two transforms, the first transform gets the last_bytes_received and last_bytes_sent for all sessions per client, and then the second transform sources the first transform and sums across the session buckets.

  2. Create an ingestion pipeline with a script to sum across the session buckets, and then attach that ingestion pipeline to the destination index.

PUT _ingest/pipeline/sum-totals-pipeline
{
  "processors": [
    {
      "script": {
        "lang": "painless",
        "source": """
          double sum = 0;
          if (ctx.name != null) {
            for (entry in ctx.name.entrySet()) {
              if (entry.getValue().containsKey('last_bytes_sent').containsKey('client.network.bytes.sent') {
                sum += entry.getValue().get('last_bytes_sent').get('client.network.bytes.sent');
              }
            }
          }
          ctx.total_sum = sum;
        """
      }
    }
  ]
}
  1. If the plan is to run this as a batch transform (meaning it will not run continuously via frequency and sync configuration values), then you can run ES|QL to generate the values, something like:
FROM .ds-metrics-ap.clients-default-*
| STATS last_bytes_received = LAST(client.network.bytes.received, desc), last_bytes_sent(client.network.bytes.sent, desc) by connection.session, client.mac, client.ip, client.name
| STATS total_last_bytes_received = SUM(last_bytes_received), total_last_bytes_sent = SUM(last_bytes_sent) by client.mac, client.ip, client.name

(I'm not that familiar with ES|QL so I'm not 100% sure that would work)