topic Re: How to calculate percentage based on 2 different searches? in Splunk Search

How to calculate percentage based on 2 different searches?

albinortiz — Fri, 26 Jan 2018 17:13:08 GMT

Greetings to the pro's,

I have 2 panels, one brings me the Total Active Hosts and the other brings me the Total Hosts. By Total Active Hosts I mean that they are currently ONLINE and by Total Hosts I mean the total count in Active Directory. What I am trying to do is to have the percentage display in a panel Total_Active_Hosts/Total_Hosts)*100 = %

For the Active Host panel I use this:

index=winevents | dedup host | stats count(host)

For the total host panel I use this:

| ldapsearch search="(objectClass=computer") attrs="cn, operatingSystem, operatingSystemVersion" 
|  lookup dnslookup clienthost AS cn | search (opeartingSystem="Win*") | stats count(cn)

This is what I have so far of the Percentage panel:

index=winevents | dedup host
| append [ | ldapsearch search="(objectClass=computer") attrs="cn, operatingSystem, operatingSystemVersion" 
| lookup dnslookup clienthost AS cn | search (opeartingSystem="Win*")] 
| stats count(eval(Total_Hosts)) AS cn, count(eval(Total_Active_Hosts)) AS host
| eval Percentage = (Total_Active_Hosts / Total_Hosts) *100

If there is any better way to do this, please let me know.

Re: How to calculate percentage based on 2 different searches?

niketn — Fri, 26 Jan 2018 17:51:29 GMT

@albinortiz, you have two ways to do it (Option 2 does not use append so it should perform better):

Option 1: Use combined search to calculate percent and display results using tokens in two different panels
In your case you will just have the third search with two searches appended together to set the tokens. Following is a run anywhere example using Splunk's _internal index:

<dashboard>
  <label>Percent from two panels - Option 1</label>
  <search>
    <query>index=_internal sourcetype=splunkd log_level=* earliest=-24h@h latest=now
| stats count as Total
| appendcols [search index=_internal sourcetype=splunkd log_level!="INFO" earliest=-24h@h latest=now
| stats count as Error]
| eval Percent=round((Error/Total)*100,1)
    </query>
    <done>
      <set token="tokTotal">$result.Total$</set>
      <set token="tokError">$result.Error$</set>
      <set token="tokPercent">$result.Percent$</set>
    </done>
  </search>
  <row>
    <panel>
      <single>
        <title>Total</title>
        <search>
          <query>| makeresults | eval Total=$tokTotal$</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="useThousandSeparators">0</option>
      </single>
      <single>
        <title>Errors</title>
        <search>
          <query>| makeresults | eval Error=$tokError$</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="useThousandSeparators">0</option>
      </single>
      <single>
        <title>Percent</title>
        <search>
          <query>| makeresults | eval Percent=$tokPercent$</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="unit">%</option>
        <option name="useThousandSeparators">0</option>
      </single>
    </panel>
  </row>
</dashboard>

Option 2: Use separate searches for Total and Active hosts and set tokens from both to be used in third panel for percent
In your case this will have first and second search but not the third combined search for percent as tokens from first two searches will be used in the third one

<dashboard>
  <label>Percent from two panels - Option 2</label>
  <row>
    <panel>
      <single>
        <title>Total</title>
        <search>
          <query>index=_internal sourcetype=splunkd log_level=* earliest=-24h@h latest=now
| stats count as Total</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
          <done>
            <set token="tokTotalCount">$result.Total$</set>
          </done>
        </search>
        <option name="drilldown">none</option>
        <option name="useThousandSeparators">0</option>
      </single>
      <single>
        <title>Errors</title>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO" earliest=-24h@h latest=now
| stats count as Error
          </query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
          <done>
            <set token="tokErrorCount">$result.Error$</set>
          </done>
        </search>
        <option name="drilldown">none</option>
        <option name="useThousandSeparators">0</option>
      </single>
      <single>
        <title>Percent</title>
        <search>
          <query>| makeresults | eval Total=$tokTotalCount$, Error=$tokErrorCount$ | eval percent= round((Error/Total)*100,1) | table percent</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="unit">%</option>
        <option name="useThousandSeparators">0</option>
      </single>
    </panel>
  </row>
</dashboard>

PS: I have used <done> search event handler to access the search result to be assigned as token (you can also use <progress>).
For example:

       <done>
         <set token="tokErrorCount">$result.Error$</set>
       </done>

Refer to Splunk Documentation: http://docs.splunk.com/Documentation/Splunk/latest/Viz/EventHandlerReference#done

Re: How to calculate percentage based on 2 different searches?

albinortiz — Tue, 29 Sep 2020 17:51:31 GMT

@niketn

I was looking at both proposed solutions and tried using the second one. Should I replace the queries with my own?

i.e. replace this:

index=_internal sourcetype=splunkd log_level=* earliest=-24h@h latest=now
| stats count as Total

with this?:

index=winevents | dedup host earliest=-24h@h latest=now
| stats count as Total

I'm no expert at any of this but I'm sure trying my best.

Re: How to calculate percentage based on 2 different searches?

albinortiz — Fri, 26 Jan 2018 20:41:28 GMT

NVM. I replaced them with my strings and they project the right data now. The only issue is that the token is not passing anything (no calculation is being done).

I probably forgot to mention that I am not using a table. I think this part is what is not working:

... | table percent

I am using a dashboard panel not a table.

Any other ideas?

Re: How to calculate percentage based on 2 different searches?

somesoni2 — Fri, 26 Jan 2018 22:03:18 GMT

Try something like this(check the time-ragne on the baseSearch. If you're using a timerange picker, you need to specify corresponding token in earliest/latest tags). Basically both searches are combined and populating 3 panels.

<dashboard>
  <label>Dashboard with post-process search</label>
  <!-- This limits events passed to post-process search -->
  <search id="baseSearch">
    <query>
     | ldapsearch search="(objectClass=computer") attrs="cn, operatingSystem, operatingSystemVersion" 
     |  lookup dnslookup clienthost AS cn | search (opeartingSystem="Win*") | stats count(cn) as Total_Active_Hosts
     | appendcols [| tstats count WHERE index=winevents by host | stats count as Total_Hosts]      
    </query>
          <earliest>-1d@d</earliest>
          <latest>now</latest>
  </search>    
 <row>
    <panel>
      <single>
        <title>Total Host Count</title>

        <!-- post-process search -->
        <search base="baseSearch">
          <query>
            table Total_Hosts
          </query>
        </search>
      </single>
    </panel>
    <panel>
      <single>
        <title>Total Active Host Count</title>

        <!-- post-process search -->
        <search base="baseSearch">
          <query>
            table Total_Active_Hosts
          </query>
        </search>
      </single>
    </panel>
    <panel>
      <single>
        <title>Active Host Percentage</title>
        <!-- post-process search -->
        <search base="baseSearch">
          <query>
            eval Percentage = (Total_Active_Hosts / Total_Hosts) *100  | table Percentage
          </query>
        </search>
      </single>
    </panel>
  </row>
</dashboard>

Re: How to calculate percentage based on 2 different searches?

albinortiz — Sat, 27 Jan 2018 00:22:42 GMT

@somesoni2

I have something so far but I am not using a table rather, I am trying to display the result in a panel in my dashboard. The intent is to show one panel with the total hosts in AD, one panel with the hosts that are active, and what percent of the total hosts are active.

How would I present that Percentage?

Re: How to calculate percentage based on 2 different searches?

niketn — Sat, 27 Jan 2018 03:56:26 GMT

@albinortiz, you would also need to change the tokens being set in done search event handler as per your search. While setting the token in <done> search event handler, the name of field in default token $result.<fieldname>$ should match exactly with the field name in the query.

For example search with field Total_Hosts i.e. ... | table Total_Hosts is $result.Total_Hosts$

<set token="tokTotalHostCount">$result.Total_Hosts$</set>

And for search with field Total_Active_Hosts i.e. ... | table Total_Active_Hosts is $result.Total_Active_Hosts$

<set token="tokActiveHostCount">$result.Total_Active_Hosts$</set>

Finally, you can also display the tokens $tokTotalHostCount$ and $tokActiveHostCount$ in the Single Value Panel <title> to check whether they are being set or not

   <single>
     <title>Percent - $tokTotalHostCount$ $tokActiveHostCount$</title>
     <search>
       <query>| makeresults | eval Total=$tokTotalHostCount$, Active=$tokActiveHostCount$ | eval percent= round((Active/Total)*100,1) | table percent</query>
       <earliest>-24h@h</earliest>
       <latest>now</latest>
       <sampleRatio>1</sampleRatio>
     </search>
     <option name="drilldown">none</option>
     <option name="unit">%</option>
     <option name="useThousandSeparators">0</option>
   </single>

Please ensure that above changes are in place and confirm. If you see any text values like $tokTotalHostCount$ and $tokActiveHostCount$ instead of numeric value in the third panel title, then it confirms that tokens are not being set properly and you would need to check and ensure that the field names in first two searches match with their respective field names in $result.<fieldName>$

Re: How to calculate percentage based on 2 different searches?

albinortiz — Sun, 28 Jan 2018 17:27:09 GMT

I will try this tomorrow. Thanks

Re: How to calculate percentage based on 2 different searches?

albinortiz — Mon, 29 Jan 2018 13:46:40 GMT

You are amazing! After I rechecked it today. I found the issue. The problem was that I was not displaying the result value. I had a typo on one of the token variables.

Thanks again!

Re: How to calculate percentage based on 2 different searches?

niketn — Mon, 29 Jan 2018 14:41:29 GMT

@albinortiz, good to know.. glad you found it working!!! 🙂

Re: How to calculate percentage based on 2 different searches?

albinortiz — Mon, 05 Feb 2018 18:20:54 GMT

Is there a way I can have this on one single panel?

What I am trying to do is to calculate the percentage difference, then have the Total Active Hosts display color change according to the percentage.

Re: How to calculate percentage based on 2 different searches?

leemburg — Tue, 25 Feb 2020 16:59:51 GMT

Hi,

i am attempting the second alternative. For some reason, the code modifies the sequence of the code. I place [...] after the 1. When I close and return into the code it puts it before the

So below is my code. I am getting the Total average time in the first panel. The second panel segments the average by either virtual machine or physical desktop.

I am trying to get the % delta between the time with a virtual machine compared to a physical desktop.

Thanks

<form>
  <label>Smallworld - Performance globale</label>
  <fieldset submitButton="true" autoRun="true">
    <input type="time" token="field1">
      <label></label>
      <default>
        <earliest>-15m</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Smallworld - Temps de charge de l'application (V1)</title>
      <single>
        <title>Temps moyen de charge - total</title>
        <search>
          <done>
            <set token="tokTotalCount">$result.avg_duration_total$</set>
          </done>
          <query>index=diag_sw_prod sourcetype="rwa_code_load" load_type="load_module"  NOT user_name=swadmin|eval location = coalesce(location,"VM") |search (location="*") (user_name=*)  | stats earliest(_time) as start_load_module by user_name session_id | join type=left session_id [|search index=diag_sw_prod sourcetype=rwr_jvm_statistics | stats latest(vm_uptime) as uptime latest(_time) as time by session_id | eval startup_time=time-uptime| fields startup_time session_id] | join type=left session_id  [search index=diag_sw_prod sourcetype=rwa_custom_function user_action=startup_profiling handler_class=smallworld_product message="open_database()" message_args=*\\* | stats earliest(_time) as start_open_db by session_id] | join type=left session_id  [search index=diag_sw_prod sourcetype=rwa_custom_function user_action=startup_profiling handler_class=smallworld_product message="login()" | stats earliest(_time) as login by session_id]| eval bootstrapping=start_load_module-startup_time , loading_module_time=start_open_db-start_load_module, open_db_time=login-start_open_db, total_time=bootstrapping+loading_module_time+open_db_time |convert ctime(startup_time) | stats avg(total_time) as avg_duration_total</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="colorMode">block</option>
        <option name="drilldown">all</option>
        <option name="height">206</option>
        <option name="rangeColors">["0x53a051","0xdc4e41"]</option>
        <option name="rangeValues">[300]</option>
        <option name="refresh.display">progressbar</option>
        <option name="unit">sec.</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
      <title>Smallworld - Temps de charge de l'application - Machine virtuelle vs poste physique</title>
      <single>
        <title>Machine virtuelle</title>
        <search>
          <done>
            <set token="tokVMCount">$result.avg_duration_VM$</set>
          </done>
          <query>index=diag_sw_prod sourcetype="rwa_code_load" load_type="load_module"  NOT user_name=swadmin|eval location = coalesce(location,"VM") |search (location="VM") (user_name=*)  | stats earliest(_time) as start_load_module by user_name session_id | join type=left session_id [|search index=diag_sw_prod sourcetype=rwr_jvm_statistics | stats latest(vm_uptime) as uptime latest(_time) as time by session_id | eval startup_time=time-uptime| fields startup_time session_id] | join type=left session_id  [search index=diag_sw_prod sourcetype=rwa_custom_function user_action=startup_profiling handler_class=smallworld_product message="open_database()" message_args=*\\* | stats earliest(_time) as start_open_db by session_id] | join type=left session_id  [search index=diag_sw_prod sourcetype=rwa_custom_function user_action=startup_profiling handler_class=smallworld_product message="login()" | stats earliest(_time) as login by session_id]| eval bootstrapping=start_load_module-startup_time , loading_module_time=start_open_db-start_load_module, open_db_time=login-start_open_db, total_time=bootstrapping+loading_module_time+open_db_time |convert ctime(startup_time) | stats avg(total_time) as avg_duration_VM</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">all</option>
        <option name="rangeColors">["0x53a051","0xdc4e41"]</option>
        <option name="rangeValues">[300]</option>
        <option name="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.splitBy">_aggregation</option>
        <option name="unit">sec.</option>
        <option name="useColors">1</option>
      </single>
      <single>
        <title>Poste physique</title>
        <search>
          <done>
            <set token="tokPPCount">$result.avg_duration_PP$</set>
          </done>
          <query>index=diag_sw_prod sourcetype="rwa_code_load" load_type="load_module"  NOT user_name=swadmin|eval location = coalesce(location,"VM") |search (location="Site locations") (user_name=*)  | stats earliest(_time) as start_load_module by user_name session_id | join type=left session_id [|search index=diag_sw_prod sourcetype=rwr_jvm_statistics | stats latest(vm_uptime) as uptime latest(_time) as time by session_id | eval startup_time=time-uptime| fields startup_time session_id] | join type=left session_id  [search index=diag_sw_prod sourcetype=rwa_custom_function user_action=startup_profiling handler_class=smallworld_product message="open_database()" message_args=*\\* | stats earliest(_time) as start_open_db by session_id] | join type=left session_id  [search index=diag_sw_prod sourcetype=rwa_custom_function user_action=startup_profiling handler_class=smallworld_product message="login()" | stats earliest(_time) as login by session_id]| eval bootstrapping=start_load_module-startup_time , loading_module_time=start_open_db-start_load_module, open_db_time=login-start_open_db, total_time=bootstrapping+loading_module_time+open_db_time |convert ctime(startup_time) | stats avg(total_time) as avg_duration_PP</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">all</option>
        <option name="rangeColors">["0x53a051","0xdc4e41"]</option>
        <option name="rangeValues">[300]</option>
        <option name="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.size">large</option>
        <option name="unit">sec.</option>
        <option name="useColors">1</option>
      </single>
      <single>
        <title>Différence de performance</title>
        <search>
          <query>makeresults | eval Total=$tokTotalCount$, TotalVM=$tokVMCount$, TotalPP=$tokPPCount$ | eval percent= round((abs(TotalVM-TotalPP)/Total)*100,1) | table percent</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <option name="unit">%</option>
        <option name="useThousandSeparators">0</option>
      </single>
    </panel>
  </row>
</form>