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