Azure Log Analytics Aggregation Query

Question

I am having trouble creating with the below query.

I am trying to get the average number of sessions from four computers over a time interval of one hour. I then want to chart the sum of the four averages over a time period of 24 hours.

So far I have the query below, using a join, but I can't get the right results.

 // Total Sessions for all four computers
    Perf
    | project Computer, bin(TimeGenerated,1h) 
    | where Computer == "s-az-vdigpu2.company.local" or Computer == "s-az-vdigpu4.company.local" or Computer == "s-az-vdigpu5.company.local" or Computer == "s-az-vdigpu6.company.local"
    | join kind= inner (
        Perf
        | where Computer == "s-az-vdigpu2.company.local" or Computer == "s-az-vdigpu4.company.local" or Computer == "s-az-vdigpu5.company.local" or Computer == "s-az-vdigpu6.company.local"
        | where CounterName  == "Total Sessions"
        | summarize avg(CounterValue) by Computer, bin(TimeGenerated, 1h)
 ) on TimeGenerated
| summarize sum(avg_CounterValue) by TimeGenerated
| render timechart

I'm getting enormous figures for the amount of sessions. The result I am looking for should be a figure between 0 and 50 because they are load balanced between four servers and the maximum licenses we have for the application is 50. — OptimusPrime
The table returned in the query I am joining gives me the average number of sessions per computer for each hour. I am not sure which value I should be joining on, but I can't get the aggregate which adds the averages together to work in the way I want. It seems to be adding the average for all time. — OptimusPrime
it's better if add some screenshots, which can give us good understanding. — Ivan Yang

OptimusPrime OptimusPrime · Accepted Answer · 2019-03-21T14:17:26

The code below seems to be working. I used a union instead of a join.

    // Total Sessions for all four computers
Perf
| project Computer, bin(TimeGenerated,1h) 
| where Computer == "s-az-vdigpu2.company.local" or Computer == "s-az-vdigpu4.company.local" or Computer == "s-az-vdigpu5.company.local" or Computer == "s-az-vdigpu6.company.local"
| union (
    Perf
    | where Computer == "s-az-vdigpu2.company.local" or Computer == "s-az-vdigpu4.company.local" or Computer == "s-az-vdigpu5.company.local" or Computer == "s-az-vdigpu6.company.local"
    | where CounterName  == "Total Sessions"
    | summarize avg(CounterValue) by Computer, bin(TimeGenerated, 1h)
    | project-rename avg_CounterValue, interval=TimeGenerated
) 
| summarize sum(avg_CounterValue) by interval
| render timechart

Azure Log Analytics Aggregation Query

1 Answers