Splunk Search

Stuck yet again. percent difference between two searches

pir8radio
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

mbarrie_splunk
Splunk Employee
Splunk Employee

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

pir8radio
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

mbarrie_splunk
Splunk Employee
Splunk Employee

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
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...