How to perform the calculations on two panels results?

[marisstella]

Hi everyone!
I want to perform calculations on two-panel results. like Panel-1 value is 100, panel-2 value is 90. Now I want to perform (100-90)/100=?
Formula Business SLA= (Total_transactions-successfull transaction)/100
Panel-1 Query index="main" source="access_30day.log"|transaction JSESSIONID | stats count | table count
Panel-2 Query: index="main" source="access_30day.log" errenter code hereor=success | transaction JSESSIONID | stats count

I tried to append but values are not accurate. I'm using the base search concept here. Any suggestions?

ABC

<query>index="main" source="access_30day.log"|transaction JSESSIONID</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>


<query>index="main" source="access_30day.log"|transaction JSESSIONID | eventstats  sum(event_duration) as total_duration, sum(eventcount) as total_events by JSESSIONID |eval avg_duration_pertransaction= round (total_duration/total_events)</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>


<input type="time" token="field1">
  <label></label>
  <default>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
  </default>
</input>


<panel>
  <single>
    <title>Total transactions count</title>
    <search base="base_query">
      <query>|stats count | table count</query>
    </search>
    <option name="colorMode">block</option>
    <option name="drilldown">none</option>
    <option name="numberPrecision">0.0</option>
    <option name="rangeColors">["0x006d9c","0x006d9c"]</option>
    <option name="rangeValues">[0]</option>
    <option name="refresh.display">progressbar</option>
    <option name="useColors">1</option>
  </single>
</panel>
<panel>
  <single>
    <title>AVG RT</title>
    <search base="base_query2">
      <query>|eventstats count(JSESSIONID) as total_transactions, sum(avg_duration_pertransaction) as avg_rt | eval overall_avg_rt= round(avg_rt/total_transactions)|table overall_avg_rt</query>
    </search>
    <option name="colorMode">block</option>
    <option name="drilldown">none</option>
    <option name="numberPrecision">0</option>
    <option name="rangeColors">["0x53a051","0xf1813f","0xf1813f"]</option>
    <option name="rangeValues">[0,100]</option>
    <option name="refresh.display">progressbar</option>
    <option name="unit">ms</option>
    <option name="unitPosition">after</option>
    <option name="useColors">1</option>
    <option name="useThousandSeparators">1</option>
  </single>
</panel>
<panel>
  <single>
    <title>90Perc</title>
    <search base="base_query">
      <query>|sort time | stats count(JSESSIONID) as b| eval a= 0.90 | eval 90perc= round(a*b)| table 90perc</query>
    </search>
    <option name="colorMode">block</option>
    <option name="drilldown">none</option>
    <option name="numberPrecision">0.0</option>
    <option name="rangeColors">["0x53a051","0xFF00FF"]</option>
    <option name="rangeValues">[0]</option>
    <option name="refresh.display">progressbar</option>
    <option name="unit">Perc</option>
    <option name="useColors">1</option>
  </single>
</panel>


<panel>
  <single>
    <title>Total error count</title>
    <search base="base_query">
      <query>|search error| stats count | table count</query>
    </search>
    <option name="colorMode">block</option>
    <option name="drilldown">none</option>
    <option name="rangeColors">["0x53a051","0xf1813f","0xdc4e41"]</option>
    <option name="rangeValues">[0,5]</option>
    <option name="refresh.display">progressbar</option>
    <option name="useColors">1</option>
  </single>
</panel>
<panel>
  <single>
    <title>Total methods</title>
    <search base="base_query">
      <query>search GET| stats count | table count</query>
    </search>
    <option name="colorMode">block</option>
    <option name="drilldown">none</option>
    <option name="rangeColors">["0x53a051","0xf8be34"]</option>
    <option name="rangeValues">[0]</option>
    <option name="refresh.display">progressbar</option>
    <option name="useColors">1</option>
  </single>
</panel>
<panel>
  <single>
    <title>Total methods+POST</title>
    <search base="base_query">
      <query>search POST| stats count | table count</query>
    </search>
    <option name="colorMode">block</option>
    <option name="drilldown">none</option>
    <option name="rangeColors">["0x53a051","0x53a051"]</option>
    <option name="rangeValues">[0]</option>
    <option name="refresh.display">progressbar</option>
    <option name="useColors">1</option>
  </single>
</panel>


<panel>
  <table>
    <title>AVG RUNTIME PER TRANSACTION - Table visualization</title>
    <search base="base_query2">
      <query>|table JSESSIONID total_events avg_duration_pertransaction</query>
    </search>
    <option name="count">10</option>
    <option name="drilldown">none</option>
  </table>
</panel>

[marisstella]

Thanks woodcock , Renjith.. both answers are really helpful.

1Q

index="main" source="access_30day.log"|transaction JSESSIONID

   <query>|search POST| stats count | table count</query>
 </search>

Above query is working fine but my actual query |search method=POST |stats count.. this query is working also fine but panel is not picking results. It is only picking values |search POST.. what is the reason for it? Like that many of the panels are not picking results but the query is populating values when I run manually. Please answer.

[renjith_nair]

@marisstella,

If you depend only on the JSESSIONID to identify the transaction, probably we should re-look at the search and replace transaction with stats. Its more efficient, especially when you are handling large number of events.

For e.g.

index="main" source="access_30day.log"
|stats count as total, range(_time) as duration, count(eval(status=="success"))  as success_count by JSESSIONID    

You can perform the calculation now on total & success_count (ps : adjust the condition for success based on your events)

You may do the subsequent breakdown to the panels based on this search.

However, for any reason, if you want to perform calculations on values from two completely different panels, you can use tokens.

Set a token for each panels on search done event and use the tokens to perform calculation in different panel.

<search>
    <query>
        index="my_index"|stats count 
    </query>
    <done>
        <set token="total">$result.count$</set>
    </done>
</search>

[woodcock]

You would use <set token="total_panel1">$result.count$</set> and then <set token="total_panel1">$result.count$</set> and then elsewhere use something like ... | eval sum = $total_panel1$ + $total_panel2$.