Splunk Search
Highlighted

Stuck yet again. percent difference between two searches

Path Finder

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?

Labels (3)
0 Karma
Highlighted

Re: Stuck yet again. percent difference between two searches

Explorer

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

0 Karma
Highlighted

Re: Stuck yet again. percent difference between two searches

Path Finder

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
0 Karma
Highlighted

Re: Stuck yet again. percent difference between two searches

Explorer

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

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.