cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

DQLQuery for calculating totalbytes sent/received by portfolios

ramsundar
Visitor

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 ? 

2 REPLIES 2

LukeHearth
Dynatrace Helper
Dynatrace Helper

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

LukeHearth_1-1704733093690.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

 

Let me know if this helps.

Yours,

Luke Hearth

krzysztof_hoja
Dynatrace Mentor
Dynatrace Mentor

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 }

 

 

krzysztof_hoja_1-1704917298573.png

Kris

 

 

 

Featured Posts