topic Re: DQLQuery for calculating totalbytes sent/received by portfolios in DQL

DQLQuery for calculating totalbytes sent/received by portfolios

ramsundar — Mon, 11 Dec 2023 13:23:06 GMT

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 ?

Re: DQLQuery for calculating totalbytes sent/received by portfolios

LukeHearth — Mon, 08 Jan 2024 16:58:41 GMT

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

Re: DQLQuery for calculating totalbytes sent/received by portfolios

krzysztof_hoja — Wed, 10 Jan 2024 20:09:17 GMT

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#ipIn)
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