Stuck yet again. percent difference between two searches

[pir8radio]

So i have this search:

index="sense_power_monitor" | where 'usage_info.solar_w'>=0 
| bin _time span=1h
  | stats count as samples sum(usage_info.solar_w) as watt_sum by _time
  | eval kW_Sum=watt_sum/1000
  | eval avg_kWh=kW_Sum/samples
|stats sum(avg_kWh)

which returns: 47.56

And i have this search:

index="sense_power_monitor" | where 'usage_info.d_w'>=0 
| bin _time span=1h
  | stats count as samples sum(usage_info.d_w) as watt_sum by _time
  | eval kW_Sum=watt_sum/1000
  | eval avg_kWh=kW_Sum/samples
|stats sum(avg_kWh)

 which returns: 74.73

I know i can get the percentage difference between these two search results by 47.56/74.73*100 = 63.64%

How can I do one search that gives me that final percent?

[mbarrie_splunk]

Try this, I don't have a running splunk in front of me to check the syntax though:

index="sense_power_monitor" | where 'usage_info.d_w'>=0  OR 'usage_info.solar_w'>=0 

| bin _time span=1h
| stats sum(eval(if('usage_info.d_w'>=0,1,0))) as d_samples  sum(eval(if('usage_info.solar_w'>=0,1,0))) as solar_samples as samples sum(usage_info.d_w) as d_watt_sum  sum(usage_info.solar_w) as solar_watt_sum by _time

  | eval d_kW_Sum=d_watt_sum/1000

  | eval d_avg_kWh=d_kW_Sum/samples

  | eval kW_Sum=solar_watt_sum/1000

  | eval solar_avg_kWh=solar_kW_Sum/samples

|stats sum(d_avg_kWh) as d_avg_kWh_sum sum(solar_avg_kWh) as solar_avg_kWh

| eval perc_diff=d_avg_kWh_sum/solar_avg_kWh

[pir8radio]

Did not work    😞

Seems to be an issue here somewhere:

| stats sum(eval(if('usage_info.d_w'>=0,1,0))) as d_samples  sum(eval(if('usage_info.solar_w'>=0,1,0))) as solar_samples as samples sum(usage_info.d_w) as d_watt_sum  sum(usage_info.solar_w) as solar_watt_sum by _time

[mbarrie_splunk]

Sorry I didn't have a UI, I had mislabeled some fields.  How about this:

| makeresults | eval usage_info.solar_w=100
| append[| makeresults | eval usage_info.solar_w=50]
| append[| makeresults | eval usage_info.d_w=50]
| append[| makeresults | eval usage_info.d_w=50]
| where 'usage_info.d_w'>=0 OR 'usage_info.solar_w'>=0

| bin _time span=1h
| stats sum(eval(if('usage_info.d_w'>=0,1,0))) as d_samples sum(eval(if('usage_info.solar_w'>=0,1,0))) as solar_samples sum(usage_info.d_w) as d_watt_sum sum(usage_info.solar_w) as solar_watt_sum by _time

| eval d_kW_Sum=d_watt_sum/1000

| eval d_avg_kWh=d_kW_Sum/d_samples

| eval solar_kW_Sum=solar_watt_sum/1000

| eval solar_avg_kWh=solar_kW_Sum/solar_samples
|stats sum(d_avg_kWh) as d_avg_kWh_sum sum(solar_avg_kWh) as solar_avg_kWh

| eval perc_diff=d_avg_kWh_sum/solar_avg_kWh