How can I compute value based on group by values in timechart?

[Sathiya123]

| eval vm_unit=case(vmSize="Standard_F16s_v2",2,vmSize="Standard_F8s_v2",1,vmSize="Standard_F4s",0.5,vmSize="Standard_F2s_v2",0.25)
| bin _time span=1h
| stats values(vm_unit) as vm_unit values(location) as location by _time id
| timechart span=1h usenull=true sum(vm_unit)-case(location="westus2",2245,location="centralus",2146,location="northeurope",624,location="germanywestcentral",620) as vm_count by location
| fillnull value=0

[yuanliu]

As you discovered, operators are not allowed outside of stats functions in a stats command.  Why not calculate it after stats?

| eval vm_unit=case(vmSize="Standard_F16s_v2",2,vmSize="Standard_F8s_v2",1,vmSize="Standard_F4s",0.5,vmSize="Standard_F2s_v2",0.25)
| bin _time span=1h
| stats values(vm_unit) as vm_unit values(location) as location by _time id
| timechart span=1h usenull=true sum(vm_unit) as vm_count by location
| eval vm_count = vm_count - case(location="westus2",2245,location="centralus",2146,location="northeurope",624,location="germanywestcentral",620)
| fillnull value=0

[ITWhisperer · Accepted solution]

@yuanliu vm_count doesn't exist after timechart

Either do it this way:

| eval vm_unit=case(vmSize="Standard_F16s_v2",2,vmSize="Standard_F8s_v2",1,vmSize="Standard_F4s",0.5,vmSize="Standard_F2s_v2",0.25)
| bin _time span=1h
| stats values(vm_unit) as vm_unit values(location) as location by _time id
| timechart span=1h usenull=true sum(vm_unit) as vm_count by location
| fillnull value=0
| eval westus2 = westus2 - 2245
| eval centralus = centralus - 2146
| eval northeurope = northeurope - 624
| eval germanywestcentral = germanywestcentral - 620

or this way

| eval vm_unit=case(vmSize="Standard_F16s_v2",2,vmSize="Standard_F8s_v2",1,vmSize="Standard_F4s",0.5,vmSize="Standard_F2s_v2",0.25)
| bin _time span=1h
| stats values(vm_unit) as vm_unit values(location) as location by _time id
| stats sum(vm_unit) as vm_count by _time location
| eval vm_count = vm_count - case(location="westus2",2245,location="centralus",2146,location="northeurope",624,location="germanywestcentral",620)
| timechart span=1h usenull=true sum(vm_count) as vm_count by location
| fillnull value=0

Although, to be fair, I am not sure what this is trying to achieve

[Sathiya123]

Wow, thanks for your solution! It worked like charm!

[Sathiya123]

it says no results found. But the below query works without the case.

| eval vm_unit=case(vmSize="Standard_F16s_v2",2,vmSize="Standard_F8s_v2",1,vmSize="Standard_F4s",0.5,vmSize="Standard_F2s_v2",0.25)
| bin _time span=1h
| stats values(vm_unit) as vm_unit values(location) as location by _time id
| timechart span=1h usenull=true sum(vm_unit) as vm_count by location
| fillnull value=0

[ITWhisperer]

The subtraction with the case is not valid in the timechart command.

It is not clear what you are trying to do here.

Do you wish to subtract a value from the sum for the location based on the location, or subtract a value from the vm_unit based on the location prior to summing for each location, or something else?