Splunk Search

Make average compare with two columns value based and display it in Panel

alakdam
Path Finder

I have two two columns of data, One is Expected box and another is Actual box.  I would like to make Percentage/Average of how much actual Box values are missing compare with Expected box.
Also some one my Actual boxes data there are no(which null/undefined)  value.  I would like to ignore those rows  where Actual boxes are  (null/undefined) which is compare with my Expected box. 

PS: I am beginner level splunker ?

Is there any way I can do this Average based on requirements?


This my search query:

 

index::service sourcetype::service "order_tote_analytics" | spath "data.order_number" | search "data.order_number"=* |  spath path=data{}.actual_totes{}.finalBoxAmount output=actualBox| spath path=data{}.estimated_totes{}.box output=estimatedBox | table estimatedBox actualBox

 

 

 



This my table looks like

Screenshot 2022-10-05 at 18.02.57.png

PS: I would love to display that  
Percentage of actual values missing in single panel, It would nice to show me, how to do that? This is wrong search query 👇🏾👇🏾👇🏾

 

 

    <panel>
      <single>
        <title>Percentage of actual values missing</title>
        <search>
          <query>index::service sourcetype::service "order_tote_analytics" | spath "data.order_number" | search "data.order_number"=$orderNumber$ |  spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox| spath path=data{}.estimated_totes{}.box output=estimatedBox | stats sum(estimatedBox) as totalBox, sum(finalBox) as finalbox</query>
          <earliest>$chosenTimePeriod.earliest$</earliest>
          <latest>$chosenTimePeriod.latest$</latest>
        </search>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0xf1813f","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">progressbar</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>

 

 

 

 

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

There is a typo in my response ('finalbox' is not the same as 'finalBox').  Try this, instead.

| spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox
| spath path=data{}.estimated_totes{}.box output=estimatedBox 
| stats sum(estimatedBox) as totalBox, sum(finalBox) as finalBox
| eval pctMissing = (totalBox - finalBox) * 100 / finalBox
| table pctMissing

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Try this query

index::service sourcetype::service "order_tote_analytics" 
| spath "data.order_number" | search "data.order_number"=* 
| spath path=data{}.actual_totes{}.finalBoxAmount output=actualBox
| spath path=data{}.estimated_totes{}.box output=estimatedBox
| fields estimatedBox actualBox
```Compute the % of missed boxes, ignoring empty actualBox entries```
| eval pctMissed=if(isnull(actualBox), null(), round((estimatedBox-actualBox) * 100 / estimatedBox, 2))
| table estimatedBox actualBox pctMissed
```Add the average of all pctMissed values on the end```
| appendpipe [ stats avg(pctMissed) as pctMissed | eval estimatedBox="Average Missed" ]
---
If this reply helps you, Karma would be appreciated.
0 Karma

alakdam
Path Finder

Thank you very much again, I need in total average not another rows and column with average 😁

Tags (1)
0 Karma

alakdam
Path Finder

Thank you very much. I am super new in splunk. Can you please show me In this panel ui, how to implement it.

    <panel>
      <single>
        <title>Percentage of actual values missing</title>
        <search>
          <query>index::service sourcetype::service "order_tote_analytics" | spath "data.order_number" | search "data.order_number"=$orderNumber$ |  spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox| spath path=data{}.estimated_totes{}.box output=estimatedBox | stats sum(estimatedBox) as totalBox, sum(finalBox) as finalbox</query>
          <earliest>$chosenTimePeriod.earliest$</earliest>
          <latest>$chosenTimePeriod.latest$</latest>
        </search>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0xf1813f","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">progressbar</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>


This is how I want to show:

Screenshot 2022-10-05 at 18.47.35.png

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Thank you for clarifying the desired result.  This code should produce it.

<panel>
      <single>
        <title>Percentage of actual values missing</title>
        <search>
          <query>index::service sourcetype::service "order_tote_analytics" 
| spath "data.order_number" 
| search "data.order_number"=$orderNumber$ 
| spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox
| spath path=data{}.estimated_totes{}.box output=estimatedBox 
| stats sum(estimatedBox) as totalBox, sum(finalBox) as finalbox
| eval pctMissing = (totalBox - finalBox) * 100 / finalbox
| table pctMissing</query>
          <earliest>$chosenTimePeriod.earliest$</earliest>
          <latest>$chosenTimePeriod.latest$</latest>
        </search>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0xf1813f","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">progressbar</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>
---
If this reply helps you, Karma would be appreciated.
0 Karma

alakdam
Path Finder

I am not getting  anything.  There are two finalBox you defined:  one we got as output=finalBox and another is sum(finalBox) as finalbox, is this the reason I am not getting anything.

| spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox
| spath path=data{}.estimated_totes{}.box output=estimatedBox 
| stats sum(estimatedBox) as totalBox, sum(finalBox) as finalbox
| eval pctMissing = (totalBox - finalBox) * 100 / finalbox
| table pctMissing



Screenshot 2022-10-05 at 19.52.17.png

0 Karma

richgalloway
SplunkTrust
SplunkTrust

There is a typo in my response ('finalbox' is not the same as 'finalBox').  Try this, instead.

| spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox
| spath path=data{}.estimated_totes{}.box output=estimatedBox 
| stats sum(estimatedBox) as totalBox, sum(finalBox) as finalBox
| eval pctMissing = (totalBox - finalBox) * 100 / finalBox
| table pctMissing

 

---
If this reply helps you, Karma would be appreciated.

alakdam
Path Finder

Thank you 🙏🏾🙏🏾🙏🏾

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...