DQLQuery for calculating totalbytes sent/received by portfolios

[ramsundar]

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 
each subnet is specific to unique team . 
Example :
 Retail - 100GB
 Infra - 100 GB
 HR   - 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 ? 

[LukeHearth]

Hi @ramsundar ,

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")

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

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

Let me know if this helps.

Yours,

Luke Hearth

[krzysztof_hoja · Accepted solution]

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 (https://docs.dynatrace.com/docs/platform/grail/dynatrace-query-language/functions/network-functions#...)
3. and summarize command mentioned by Luke is the final ingredient to produce few values out of many log records 
Using my private, but similar data this could look like:

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 }

Kris