Can't merge rows together

%3CLINGO-SUB%20id%3D%22lingo-sub-2652527%22%20slang%3D%22en-US%22%3ECan't%20merge%20rows%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2652527%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI'm%20using%20Grafana%20with%20Azure%20Monitor%20as%20datasource%20to%20monitor%20our%20AKS%20cluster.%20I've%20installed%20a%20Grafana%20dashboard%20which%20shows%20Node%20CPU%20usage%20for%20the%20past%2012%20hours%2C%20which%20is%20great%2C%20but%20it%20would%20be%20more%20useful%20to%20me%20if%20it%20showed%20me%20the%20last%207%20days.%20This%20is%20very%20easy%20to%20adjust%20in%20Grafana%2C%20but%20when%20I%20change%20timespan%20to%207%20days%2C%20the%20graph%20is%20pretty%20opaque%2C%20since%20it's%20trying%20to%20plot%20some%2023.000%20entries%20(see%26nbsp%3B%20attached%20image).%3C%2FP%3E%3CP%3ETo%20make%20it%20more%20comprehensible%2C%20I%20would%20like%20to%20aggregate%20the%20entires%20together%20in%20for%20instance%20timespans%20of%203%20hours.%20I%20know%20theres%20a%20function%20to%20do%20just%20that%20in%20the%20query%20language%3A%3C%2FP%3E%3CPRE%3Ebin(TimeGenerated%2C%203h)%3C%2FPRE%3E%3CP%3EI've%20tried%20so%20hard%20to%20make%20it%20work%20in%20my%20query%2C%20but%20I%20just%20can't%20make%20it%20work.%20Next%20step%20is%20to%20really%20dive%20into%20the%20query%20language%2C%20but%20it's%20time%20I%20would%20like%20to%20really%20spare.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20can%20anyone%20help%20me%20make%20this%20query%20aggregate%20the%20data%20with%203%20hours%20intervals%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3EPerf%0A%7C%20where%20ObjectName%20%3D%3D%20%22K8SNode%22%0A%7C%20where%20CounterName%20%3D%3D%20%22cpuCapacityNanoCores%22%0A%7C%20where%20%24__timeFilter(TimeGenerated)%0A%7C%20where%20InstanceName%20contains%20'%24AKS'%0A%7C%20summarize%20arg_max(TimeGenerated%2C%20*)%20by%20Computer%0A%7C%20project%20Computer%2C%20CpuTotal%3D(CounterValue)%0A%7C%20join%20kind%3Dinner%20(%0A%20%20%20%20Perf%0A%20%20%20%20%7C%20where%20ObjectName%20%3D%3D%20%22K8SNode%22%0A%20%20%20%20%7C%20where%20CounterName%20%3D%3D%20%22cpuUsageNanoCores%22%0A%20%20%20%20%7C%20where%20%24__timeFilter(TimeGenerated)%0A%20%20%20%20%7C%20where%20InstanceName%20contains%20'%24AKS'%0A%20%20%20%20%7C%20project%20TimeGenerated%2C%20Computer%2C%20CpuUsed%3D(CounterValue)%0A)%20on%20Computer%0A%7C%20order%20by%20TimeGenerated%20asc%0A%7C%20project%20TimeGenerated%2C%20Computer%2C%20CpuPercentageUsad%3D(CpuUsed%20%2F%20CpuTotal)%20*%20100%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2652527%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2689321%22%20slang%3D%22en-US%22%3ERe%3A%20Can't%20merge%20rows%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2689321%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20made%20a%20small%20example%20for%20you%2C%20where%20the%20CPU%20graph%20is%20summarized%20to%20an%20average%20per%20hour.%3CBR%20%2F%3EDoes%20this%20help%20you%3F%3CBR%20%2F%3E%3CBR%20%2F%3EPerf%3CBR%20%2F%3E%7C%20where%20Computer%20%3D%3D%20%22idala%22%3CBR%20%2F%3E%7C%20where%20ObjectName%20%3D%3D%20%22Processor%22%3CBR%20%2F%3E%7C%20where%20CounterName%20%3D%3D%20%22%25%20Processor%20Time%22%3CBR%20%2F%3E%7C%20summarize%20avg(CounterValue)%20by%20bin(TimeGenerated%2C%201h)%2C%20CounterName%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello

I'm using Grafana with Azure Monitor as datasource to monitor our AKS cluster. I've installed a Grafana dashboard which shows Node CPU usage for the past 12 hours, which is great, but it would be more useful to me if it showed me the last 7 days. This is very easy to adjust in Grafana, but when I change timespan to 7 days, the graph is pretty opaque, since it's trying to plot some 23.000 entries (see  attached image).

To make it more comprehensible, I would like to aggregate the entires together in for instance timespans of 3 hours. I know theres a function to do just that in the query language:

bin(TimeGenerated, 3h)

I've tried so hard to make it work in my query, but I just can't make it work. Next step is to really dive into the query language, but it's time I would like to really spare.

 

So can anyone help me make this query aggregate the data with 3 hours intervals?

 

 

Perf
| where ObjectName == "K8SNode"
| where CounterName == "cpuCapacityNanoCores"
| where $__timeFilter(TimeGenerated)
| where InstanceName contains '$AKS'
| summarize arg_max(TimeGenerated, *) by Computer
| project Computer, CpuTotal=(CounterValue)
| join kind=inner (
    Perf
    | where ObjectName == "K8SNode"
    | where CounterName == "cpuUsageNanoCores"
    | where $__timeFilter(TimeGenerated)
    | where InstanceName contains '$AKS'
    | project TimeGenerated, Computer, CpuUsed=(CounterValue)
) on Computer
| order by TimeGenerated asc
| project TimeGenerated, Computer, CpuPercentageUsad=(CpuUsed / CpuTotal) * 100

 

 Thanks in advance

1 Reply
Hi,

I made a small example for you, where the CPU graph is summarized to an average per hour.
Does this help you?

Perf
| where Computer == "idala"
| where ObjectName == "Processor"
| where CounterName == "% Processor Time"
| summarize avg(CounterValue) by bin(TimeGenerated, 1h), CounterName