Multidimension timechart

Occasional Contributor

Hello,

 

So first off, i've already reviewed this answer, and it seems hacky. 

https://techcommunity.microsoft.com/t5/Azure-Log-Analytics/Azure-log-analytics-timechart-with-multip...

 

What I'd like to do it view the CPU usage of my content delivery servers and the count of participating in the load balancer. 

 

Here is my CPU query. 

Perf

| where

ObjectName == "Processor"

and CounterName == "% Processor Time"

and InstanceName == "_Total"

and Computer contains "CD"

and TimeGenerated > ago(10h)

and CounterName

| summarize

avg( CounterValue )

by Computer=extract(@"([^\.]*)", 1, Computer ), bin(TimeGenerated, 15m)

| render timechart
 
and here is my loadbalancer dip count query 
AzureDiagnostics

| where Category == "LoadBalancerProbeHealthStatus"

and

Resource == "SITECORE-PROD-CD-EXTERNAL-LB"

and

TimeGenerated > ago(2d)

| summarize max(totalDipCount_d) by bin(TimeGenerated, 5m)

| render timechart
 
So I want to combine them, here is an attempt at that. 
 
 (Perf

| where

ObjectName == "Processor"

and CounterName == "% Processor Time"

and InstanceName == "_Total"

and Computer contains "CD"

and TimeGenerated > ago(10h)

)

| union

(AzureDiagnostics

| where Category == "LoadBalancerProbeHealthStatus"

and

Resource == "SITECORE-PROD-CD-EXTERNAL-LB"

and

TimeGenerated > ago(1d)

)

| summarize max(totalDipCount_d), avg( CounterValue )

by Computer=extract(@"([^\.]*)", 1, Computer ), bin(TimeGenerated, 5m)

 

| render timechart
 
 Which is close, I am getting rows from the correct datasets, but when I add the timechart, I don't get the visualization I am after. 
 
Thanks,
Devin
3 Replies

Hi Devin,

I'm not sure what you get vs. what you want to get - can you add a picture?

I've created a similar query on our demo data:

(Perf
 | where ObjectName == "Processor"
   and CounterName == "% Processor Time"
   and InstanceName == "_Total"
   and Computer contains "Contoso"
   and TimeGenerated > ago(1h)
 )
| union
 (AzureDiagnostics
 | where Category=="ApplicationGatewayPerformanceLog" and Resource contains "Contoso" 
 and TimeGenerated > ago(1h)
 | project TimeGenerated, latency_d
 )
| summarize avg(latency_d), avg(CounterValue) by bin(TimeGenerated, 5m)
| render timechart

and it produces this output:

union.png

Were you aiming for something very different?

Sorry, I never got notified on your reply. I realized the fundamental issue with my problem. I didn't have a common Y Axis. 

 

Here is a new query.

 (
Perf
| where
ObjectName == "Processor"
and CounterName == "% Processor Time"
and InstanceName == "_Total"
and Computer contains "CD"
and TimeGenerated > ago(7d)
| project CPU_Utilization=CounterValue, TimeGenerated
)
| union
(
AzureDiagnostics
| where
Category == "LoadBalancerProbeHealthStatus"
and Resource == "SITECORE-PROD-CD-EXTERNAL-LB"
and TimeGenerated > ago(7d)
| project TimeGenerated, dipCount=totalDipCount_d
)
| summarize dipCount=max(dipCount)*10, avg(CPU_Utilization) by bin(TimeGenerated, 1h)
//, Computer=extract(@"([^\.]*)", 1, Computer )
| render timechart

 

 

image.png

Here is another query that further exemplifies the concept. 

 

(
Perf
| where
ObjectName == "Processor"
and CounterName == "% Processor Time"
and InstanceName == "_Total"
and Computer contains "CD"
and Computer !contains "azecdag01"
and Computer !contains "azescdb03"
and Computer !contains "azescdb00"
and TimeGenerated > ago(7d)
| project CPU_Utilization=CounterValue, TimeGenerated
)
| union
(
Perf
| where
ObjectName == "Memory"
and CounterName == "% Committed Bytes In Use"
and TimeGenerated > ago(7d)
| project Mem_Utilization=CounterValue, TimeGenerated
)
| union
(
app("Sitecore Production").pageViews
| where timestamp > ago(7d)
| summarize count() by bin(timestamp,1h)
| project TimeGenerated=timestamp, pageViews=count_
)
| union
(
app("Sitecore Production").pageViews
| where timestamp > ago(7d)
| summarize count() by session_Id, bin(timestamp,1h)
| summarize count() by bin(timestamp, 1h)
| project TimeGenerated=timestamp, sessions=count_
)
| summarize avg_pageviewsx100=avg(pageViews)/100, avg_sessionsx100=avg(sessions)/100, avg(CPU_Utilization), avg(Mem_Utilization) by bin(TimeGenerated, 1h)
| render timechart

image.png