# DQLQuery for calculating totalbytes sent/received by portfolios

**URL:** <https://community.dynatrace.com/t/dqlquery-for-calculating-totalbytes-sent-received-by-portfolios/43122>\
**Category:** DQL\
**Tags:** dql, dashboards\
**Created:** [December 11, 2023, 1:23pm UTC](https://community.dynatrace.com/t/dqlquery-for-calculating-totalbytes-sent-received-by-portfolios/43122 "2023-12-11T13:23:06Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ramsundar](https://avatars.discourse-cdn.com/v4/letter/r/eb8c5e/32.png) [@ramsundar](https://community.dynatrace.com/u/ramsundar)\
**Post date:** [December 11, 2023, 1:23pm UTC](https://community.dynatrace.com/t/dqlquery-for-calculating-totalbytes-sent-received-by-portfolios/43122/1 "2023-12-11T13:23:06Z")

</div>

We are forwarding Firewall traffic logs to dynatrace , and we split into columns like srcip , dstip , port , sentbyte & received byte.

Now i need calculate totalbytes by adding sent & received byte , which should group based on the subnets&nbsp;  
each subnet is specific to unique team .&nbsp;  
Example :  
&nbsp;Retail - 100GB  
&nbsp;Infra - 100 GB  
&nbsp;HR&nbsp; &nbsp;- 100GB

The query im using is bleow  
fetch logs ,from:now() - 24h //, scanLimitGBytes: 500, samplingRatio: 1000  
| filter matchesPhrase(host, “SDW-”) AND ( toIp(srcip)==ipMask(“10.0.0.0”,24) OR toIP(dstip)==ipMask(“10.0.0.0”,24) )  
| summarize Totalbytes = sum(toDouble(sentbyte)+toDouble(rcvdbyte))

how case function in DQL ?

---

<div class="post-metadata">

**Author:** ![LukeHearth](https://avatars.discourse-cdn.com/v4/letter/l/41988e/32.png) [@LukeHearth](https://community.dynatrace.com/u/LukeHearth)\
**Post date:** [January 8, 2024, 4:58pm UTC](https://community.dynatrace.com/t/dqlquery-for-calculating-totalbytes-sent-received-by-portfolios/43122/2 "2024-01-08T16:58:41Z")

</div>

Hi&nbsp;[@ramsundar](https://community.dynatrace.com/user/69601)&nbsp;,

I hope i can answer your question you should be able to us the “by:” keyword in the summarize if we start off with this data:

data record( sentbyte = 10 , receivedbyte = 20 , subnet = “Retail”) ,  
record( sentbyte = 10 , receivedbyte = 20 , subnet = “Retail”),  
record( sentbyte = 10 , receivedbyte = 20 , subnet = “Retail”),  
record( sentbyte = 10 , receivedbyte = 20 , subnet = “HR”)

 ![LukeHearth_1-1704733093690.png](https://global.discourse-cdn.com/dynatrace/original/3X/e/c/ec3799fdae8f203be00ae766981515abd08a4ea2.png)

We can then use this line to add up the bytes to get the sum:

| summarize total = sum(receivedbyte + sentbyte) , by:subnet

 ![LukeHearth_0-1704733050222.png](https://global.discourse-cdn.com/dynatrace/original/3X/2/1/216061ceee7cb64746931aad6e246d5b3fb97f0c.png)

Let me know if this helps.

Yours,

Luke Hearth

---

<div class="post-metadata">

**Author:** ![krzysztof\_hoja](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/krzysztof_hoja/32/34625_2.png) [@krzysztof\_hoja](https://community.dynatrace.com/u/krzysztof_hoja)\
**Post date:** [January 10, 2024, 8:09pm UTC](https://community.dynatrace.com/t/dqlquery-for-calculating-totalbytes-sent-received-by-portfolios/43122/3 "2024-01-10T20:09:17Z")

</div>

1. Case function can be realized as a combination of of _ **coalesce** _ and _ **if** _ functions

2. Matching IP addresses to networks can be simplified using _ **ipIn** _ function ([Network functions — Dynatrace Docs](https://docs.dynatrace.com/docs/platform/grail/dynatrace-query-language/functions/network-functions#ipIn))  
3. and _ **summarize** _ command mentioned by Luke is the final ingredient to produce few values out of many log records&nbsp;  
Using my private, but similar data this could look like:

```markup
fetch logs 
| fieldsAdd team = coalesce(
    if (ipIn(srcaddr, "10.176.1.0/24"), "Retail"),
    if (ipIn(srcaddr, "10.176.2.0/24"), "Infra"),
    if (ipIn(srcaddr, "10.176.3.0/24"), "HR"),
    "Other"
)
| summarize {totalbytes=sum(toLong(bytes))}, by: { team }

```

 ![krzysztof_hoja_1-1704917298573.png](https://global.discourse-cdn.com/dynatrace/original/2X/5/53b3db726ac2fe98f9ca51cee41cad7bc8336380.png)

Kris
