topic Re: Is there a way I can make average two columns value based on requirements? in Splunk Search

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

alakdam — Wed, 05 Oct 2022 16:20:00 GMT

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:

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>

Re: Is there a way I can make average two columns value based on requirements?

richgalloway — Wed, 05 Oct 2022 15:38:31 GMT

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" ]

Re: Is there a way I can make average two columns value based on requirements?

alakdam — Wed, 05 Oct 2022 15:47:55 GMT

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

This is how I want to show:

Re: Is there a way I can make average two columns value based on requirements?

alakdam — Wed, 05 Oct 2022 16:27:52 GMT

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

Re: Is there a way I can make average two columns value based on requirements?

richgalloway — Wed, 05 Oct 2022 16:44:23 GMT

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>

Re: Is there a way I can make average two columns value based on requirements?

alakdam — Wed, 05 Oct 2022 16:56:17 GMT

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

Re: Is there a way I can make average two columns value based on requirements?

richgalloway — Thu, 06 Oct 2022 12:54:43 GMT

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

Re: Is there a way I can make average two columns value based on requirements?

alakdam — Thu, 06 Oct 2022 13:54:26 GMT

Thank you 🙏🏾🙏🏾🙏🏾.