Find out request count per node vs total request count

[susmita_k]

Hi,

I need to set up an alert when the distribution of a particular request is not even across the different nodes. To do that, I need to find out the percentage of load based on the load/per node vs total load.  I am trying to achieve it by this DQL, however the sum is not working. Any help is really appreciated. 

fetch spans, from: now()-1h
| filter service.name == "service-name"
| summarize requests = count(), by: {host.name}
| fieldsAdd total = sum(requests)
| fieldsAdd pct = round(toDouble(requests) / toDouble(total) * 100, 2)
| sort requests desc

[sujit_k_singh · Accepted solution]

Hi @susmita_k 

Try this

fetch spans, from: now()-1h
| filter service.name == "easytrade-ld-offerservice"
| summarize requests = count(), by: {host.name}
| summarize { total = sum(requests), d = collectArray(record(host.name, requests)) }
| expand d
| fieldsAdd host.name = d[host.name], requests = d[requests]
| fieldsAdd pct = concat(toString(round(toDouble(requests) / toDouble(total) * 100)), "%")
| fields host.name, requests, total, pct
| sort requests desc

[susmita_k]

Thank you @sujit_k_singh . this works.

[susmita_k]

@sujit_k_singh can I make it a timeseries if I want to set up an alert in anomaly detection?

[sujit_k_singh]

Hi @susmita_k 

Try this if it works....

This will render as a timeseries line chart (one line per host) that you can use directly in Anomaly Detection.

fetch spans, from: now()-2h
| filter service.name == "your-service-name"
| makeTimeseries requests = count(), by: {host.name}, interval: 5m

Time-bucketed data for anomaly detection alerting:

fetch spans, from: now()-2h
| filter service.name == "your-service-name"
| summarize requests = count(), by: {host.name, timebin = bin(start_time, 5m)}
| lookup [
fetch spans, from: now()-2h
| filter service.name == "your-service-name"
| summarize totalRequests = count(), by: {timebin = bin(start_time, 5m)}
], sourceField: timebin, lookupField: timebin, fields: {totalRequests}
| fieldsAdd pct = round(toDouble(requests) / toDouble(totalRequests) * 100.0, decimals: 0)
| fields timebin, host.name, requests, totalRequests, pct
| sort timebin desc

Thanks,

Sujit

[susmita_k]

Thanks much, I will try and let you know 🙂