Yet Another Question - math on fields from different index and searches in one display?

[pir8radio]

@to4kawa You have helped me a lot the past few weeks, lol you will probably answer this one too! 🙂

So i have these two searches that give me the below single display numbers... I need to make a new single display that subtracts the second value from the first value.. Normally this is easy, but the second value is based on a search that requires the host. My main goal is to get the "latest" results for each field (which i am doing in my current searches, maybe not so efficiently) then subtract field 1 from field two....

First search: index="sense_power_monitor" | head 1 | table usage_info.solar_w
Result:

Second search: index="homeautomationrtac" host="sunnyboy1_watts" | head 1 | rename instMag as SunnyBoy1Watts1 | eval SunnyBoy1Watts = if(SunnyBoy1Watts1 < 0, 0, SunnyBoy1Watts1) | table SunnyBoy1Watts

Result:

I need a search that will take the first and subtract the second giving me a third display of 147 W
I know how to do that math in splunk, im hung up on how to get my starting numbers being two different indexes, and having to do the other math first...

Any help would be appreciated.

[to4kawa]

<dashboard theme="dark" hideChrome="true" hideEdit="true">
  <label>Solar - Real-Time</label>
  <search id="baseSearch">
    <query>index="sense_power_monitor"</query>
    <earliest>-15m</earliest>
    <latest>now</latest>
    <refresh>5s</refresh>
    <refreshType>delay</refreshType>
  </search>
  <search id="baseSearch2">
    <query>index="sense_power_monitor" OR (index="homeautomationrtac" host="sunnyboy1_watts")</query>
    <earliest>@d</earliest>
    <latest>now</latest>
    <refresh>30s</refresh>
    <refreshType>delay</refreshType>
  </search>
  <row>
    <panel>
      <single>
        <title>Usage</title>
        <search base="baseSearch">
          <query>|head 1| table usage_info.d_w</query>
        </search>
        <option name="drilldown">none</option>
        <option name="link.visible">0</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>Production</title>
        <search base="baseSearch">
          <query>|head 1| table usage_info.solar_w</query>
        </search>
        <option name="colorMode">none</option>
        <option name="drilldown">none</option>
        <option name="link.visible">0</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x006d9c","0x53a051","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>To/From Grid</title>
        <search base="baseSearch">
          <query>|head 1| table usage_info.grid_w</query>
        </search>
        <option name="drilldown">none</option>
        <option name="link.visible">0</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <single>
        <title>Peak Usage Today</title>
        <search base="baseSearch2">
          <query>| stats max(usage_info.d_w)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>Peak Production Today</title>
        <search base="baseSearch2">
          <query>| stats max(usage_info.solar_w)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="link.visible">0</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>Peak To Grid Today</title>
        <search base="baseSearch2">
          <query>| rename usage_info.grid_w as toGridW |eval toGridW2 = if(toGridW > 0, 0, toGridW) | stats min(toGridW2)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <single>
        <title>Production - Solar Array #1</title>
        <search>
          <query>index="homeautomationrtac" host="sunnyboy1_watts" | head 1 | rename instMag as SunnyBoy1Watts1 | eval SunnyBoy1Watts = if(SunnyBoy1Watts1 < 0, 0, SunnyBoy1Watts1) | table SunnyBoy1Watts</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <refresh>5s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x006d9c","0x53a051","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>Production - Solar Array #2</title>
        <search>
          <query>index="sense_power_monitor" OR (index="homeautomationrtac" host="sunnyboy1_watts")
  | stats latest(usage_info.solar_w) as usage_info latest(instMag) as SunnyBoy1Watts1
  | eval SunnyBoy1Watts = if(SunnyBoy1Watts1 < 0, 0, SunnyBoy1Watts1) 
  | eval result = usage_info -SunnyBoy1Watts 
  | table result</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <refresh>5s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x006d9c","0x53a051","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <table>
        <title>Grid Voltage @ Bkr Panel</title>
        <search base="baseSearch1">
          <query>| head 1 | rename usage_info.voltage{} as voltage |  mvexpand voltage | stats values(voltage) by voltage | streamstats count as voltage</query>
        </search>
        <option name="count">3</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">none</option>
        <option name="totalsRow">true</option>
        <option name="wrap">false</option>
        <format type="color" field="values(voltage)">
          <colorPalette type="list">[#DC4E41,#F8BE34,#53A051,#F8BE34,#DC4E41]</colorPalette>
          <scale type="threshold">119,120,123,124</scale>
        </format>
        <format type="number" field="values(voltage)">
          <option name="precision">4</option>
          <option name="unit">VAC</option>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <single>
        <title>Array #1 Peak Production Today</title>
        <search base="baseSearch2">
          <query> | rename instMag as SunnyBoy1Watts1 | eval SunnyBoy1Watts = if(SunnyBoy1Watts1 < 0, 0, SunnyBoy1Watts1) | stats max(SunnyBoy1Watts)</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <refresh>30s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>Array #2 Peak Production Today</title>
        <search base="baseSearch2">
          <query> | stats max(usage_info.solar_w) as usage_info max(instMag) as SunnyBoy1Watts1
  | eval SunnyBoy1Watts = if(SunnyBoy1Watts1 < 0, 0, SunnyBoy1Watts1) 
  | eval result = usage_info -SunnyBoy1Watts 
  | table result</query>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <table>
        <title>Grid Voltage @ Solar Array</title>
        <search>
          <query>index="homeautomationrtac" host="sunnyboy1_vl*" | rename instMag as voltage | eval voltage=voltage/100 | stats first(voltage) by host</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <refresh>5s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="count">3</option>
        <option name="drilldown">none</option>
        <option name="refresh.display">none</option>
        <option name="totalsRow">true</option>
        <format type="number" field="values(voltage)">
          <option name="precision">4</option>
          <option name="unit">VAC</option>
        </format>
        <format type="color" field="values(voltage)">
          <colorPalette type="list">[#DC4E41,#F8BE34,#53A051,#F8BE34,#DC4E41]</colorPalette>
          <scale type="threshold">119,120,123,124</scale>
        </format>
        <format type="number" field="first(voltage)">
          <option name="precision">4</option>
          <option name="unit">VAC</option>
        </format>
        <format type="color" field="first(voltage)">
          <colorPalette type="list">[#DC4E41,#F8BE34,#53A051,#F8BE34,#DC4E41]</colorPalette>
          <scale type="threshold">119,120,123,124</scale>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <search>
          <query>index="sense_power_monitor" | timechart span=1m  max(usage_info.solar_w) max(usage_info.d_w)
 | rename max(usage_info.solar_w) as "Production" max(usage_info.d_w) as "Usage"</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <refresh>2m</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="charting.axisLabelsY.majorUnit">1000</option>
        <option name="charting.axisTitleX.visibility">collapsed</option>
        <option name="charting.axisTitleY.text">kW</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.chart">line</option>
        <option name="charting.chart.nullValueMode">connect</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.legend.placement">bottom</option>
        <option name="link.visible">0</option>
        <option name="refresh.display">none</option>
      </chart>
    </panel>
  </row>
</dashboard>

I collect queries to baseSearch and baseSearch2

[pir8radio]

do i have to do anything? All of my panels display "no results found" looking through your xml everything seems good...

[to4kawa]

https://docs.splunk.com/Documentation/Splunk/8.0.3/Viz/Savedsearches

maybe, fields is need. sorry.

[to4kawa · Accepted solution]

3rd query:

index="sense_power_monitor" OR (index="homeautomationrtac" host="sunnyboy1_watts")
| stats latest(usage_info.solar_w) as usage_info latest(instMag) as SunnyBoy1Watts1
| eval SunnyBoy1Watts = if(SunnyBoy1Watts1 < 0, 0, SunnyBoy1Watts1) 
| eval result = usage_info -SunnyBoy1Watts 
| table result

or use base search and tokens

reference:

• https://docs.splunk.com/Documentation/Splunk/latest/Viz/tokens
• https://docs.splunk.com/Documentation/Splunk/latest/Viz/Savedsearches

[pir8radio]

I was SO close, I gave up too soon.... Thank you again @to4kawa !!

[pir8radio]

@to4kawa thanks again, final solar dashboard complete!

[to4kawa]

Good and Cool job!
If you provide SimleXML, I'm going to optimize it.

[pir8radio]

Yes please... Let me know where I can improve so I get better please.

<dashboard theme="dark" hideChrome="true" hideEdit="true">
  <label>Solar - Real-Time</label>
  <row>
    <panel>
      <single>
        <title>Usage</title>
        <search>
          <query>index="sense_power_monitor" | head 1 | table usage_info.d_w</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <refresh>5s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="link.visible">0</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>Production</title>
        <search>
          <query>index="sense_power_monitor" | head 1 | table usage_info.solar_w</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <refresh>5s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="colorMode">none</option>
        <option name="drilldown">none</option>
        <option name="link.visible">0</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x006d9c","0x53a051","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>To/From Grid</title>
        <search>
          <query>index="sense_power_monitor" | head 1 | table usage_info.grid_w</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <refresh>5s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="link.visible">0</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <single>
        <title>Peak Usage Today</title>
        <search>
          <query>index="sense_power_monitor" | stats max(usage_info.d_w)</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <refresh>30s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>Peak Production Today</title>
        <search>
          <query>index="sense_power_monitor" | stats max(usage_info.solar_w)</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <refresh>30s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="link.visible">0</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>Peak To Grid Today</title>
        <search>
          <query>index="sense_power_monitor" | rename usage_info.grid_w as toGridW |eval toGridW2 = if(toGridW &gt; 0, 0, toGridW) | stats min(toGridW2)</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <refresh>30s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <single>
        <title>Production - Solar Array #1</title>
        <search>
          <query>index="homeautomationrtac" host="sunnyboy1_watts" | head 1 | rename instMag as SunnyBoy1Watts1 | eval SunnyBoy1Watts = if(SunnyBoy1Watts1 &lt; 0, 0, SunnyBoy1Watts1) | table SunnyBoy1Watts</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <refresh>5s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x006d9c","0x53a051","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>Production - Solar Array #2</title>
        <search>
          <query>index="sense_power_monitor" OR (index="homeautomationrtac" host="sunnyboy1_watts")
 | stats latest(usage_info.solar_w) as usage_info latest(instMag) as SunnyBoy1Watts1
 | eval SunnyBoy1Watts = if(SunnyBoy1Watts1 &lt; 0, 0, SunnyBoy1Watts1) 
 | eval result = usage_info -SunnyBoy1Watts 
 | table result</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <refresh>5s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x006d9c","0x53a051","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <table>
        <title>Grid Voltage @ Bkr Panel</title>
        <search>
          <query>index="sense_power_monitor" | head 1 | rename usage_info.voltage{} as voltage |  mvexpand voltage | stats values(voltage) by voltage | streamstats count as voltage</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <refresh>5s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="count">3</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">none</option>
        <option name="totalsRow">true</option>
        <option name="wrap">false</option>
        <format type="color" field="values(voltage)">
          <colorPalette type="list">[#DC4E41,#F8BE34,#53A051,#F8BE34,#DC4E41]</colorPalette>
          <scale type="threshold">119,120,123,124</scale>
        </format>
        <format type="number" field="values(voltage)">
          <option name="precision">4</option>
          <option name="unit">VAC</option>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <single>
        <title>Array #1 Peak Production Today</title>
        <search>
          <query>index="homeautomationrtac" host="sunnyboy1_watts" | rename instMag as SunnyBoy1Watts1 | eval SunnyBoy1Watts = if(SunnyBoy1Watts1 &lt; 0, 0, SunnyBoy1Watts1) | stats max(SunnyBoy1Watts)</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <refresh>30s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <single>
        <title>Array #2 Peak Production Today</title>
        <search>
          <query>index="sense_power_monitor" OR (index="homeautomationrtac" host="sunnyboy1_watts")
 | stats max(usage_info.solar_w) as usage_info max(instMag) as SunnyBoy1Watts1
 | eval SunnyBoy1Watts = if(SunnyBoy1Watts1 &lt; 0, 0, SunnyBoy1Watts1) 
 | eval result = usage_info -SunnyBoy1Watts 
 | table result</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <refresh>30s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[1000,4000,7000,9000]</option>
        <option name="refresh.display">none</option>
        <option name="unit">W</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <table>
        <title>Grid Voltage @ Solar Array</title>
        <search>
          <query>index="homeautomationrtac" host="sunnyboy1_vl*" | rename instMag as voltage | eval voltage=voltage/100 | stats first(voltage) by host</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <refresh>5s</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="count">3</option>
        <option name="drilldown">none</option>
        <option name="refresh.display">none</option>
        <option name="totalsRow">true</option>
        <format type="number" field="values(voltage)">
          <option name="precision">4</option>
          <option name="unit">VAC</option>
        </format>
        <format type="color" field="values(voltage)">
          <colorPalette type="list">[#DC4E41,#F8BE34,#53A051,#F8BE34,#DC4E41]</colorPalette>
          <scale type="threshold">119,120,123,124</scale>
        </format>
        <format type="number" field="first(voltage)">
          <option name="precision">4</option>
          <option name="unit">VAC</option>
        </format>
        <format type="color" field="first(voltage)">
          <colorPalette type="list">[#DC4E41,#F8BE34,#53A051,#F8BE34,#DC4E41]</colorPalette>
          <scale type="threshold">119,120,123,124</scale>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <search>
          <query>index="sense_power_monitor" | timechart span=1m  max(usage_info.solar_w) max(usage_info.d_w)
| rename max(usage_info.solar_w) as "Production" max(usage_info.d_w) as "Usage"</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <refresh>2m</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="charting.axisLabelsY.majorUnit">1000</option>
        <option name="charting.axisTitleX.visibility">collapsed</option>
        <option name="charting.axisTitleY.text">kW</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.chart">line</option>
        <option name="charting.chart.nullValueMode">connect</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.legend.placement">bottom</option>
        <option name="link.visible">0</option>
        <option name="refresh.display">none</option>
      </chart>
    </panel>
  </row>
</dashboard>