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
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>
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
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" ]
Thank you very much again, I need in total average not another rows and column with average 😁
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:
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>
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
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
Thank you 🙏🏾🙏🏾🙏🏾.