Forum Discussion

Devin Lambert's avatar
Devin Lambert
Copper Contributor
Jun 29, 2018

Multidimension timechart

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-multiple-dimensions/td-p/108245

 

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
  • 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:

    Were you aiming for something very different?

    • Devin Lambert's avatar
      Devin Lambert
      Copper Contributor

      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

       

       

      • Devin Lambert's avatar
        Devin Lambert
        Copper Contributor

        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

Resources