topic Re: How can I compute value based on group by values in timechart? in Splunk Search

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

Sathiya123 — Wed, 12 Apr 2023 07:50:43 GMT

| 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

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

Sathiya123 — Wed, 12 Apr 2023 07:52:48 GMT

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

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

ITWhisperer — Wed, 12 Apr 2023 08:30:45 GMT

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?

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

yuanliu — Wed, 12 Apr 2023 08:38:02 GMT

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

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

ITWhisperer — Wed, 12 Apr 2023 08:55:50 GMT

@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

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

Sathiya123 — Wed, 12 Apr 2023 09:07:43 GMT

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