topic Re: Average wrong box estimate in Splunk Search

Average wrong box estimate- Why is my attempt wrong?

alakdam — Thu, 06 Oct 2022 13:51:59 GMT

I have total 17 orders. Box Estimates is wrong 6 out of 17 orders. What is the average wrong box estimate in total?

This is my attempt who is wrong:

| spath path=data{}.actual_totes{}.finalBoxAmount output=actualBoxes | spath path=data{}.estimated_totes{}.box output=estimatedBoxes | eventstats count AS total | eval box_missing=if(actualBoxes != estimatedBoxes, "YES", "NO") | eval average= (actualBoxes - estimatedBoxes) / total * 100 | table actualBoxes estimatedBoxes total box_missing average

Re: Average wrong box estimate

ITWhisperer — Thu, 06 Oct 2022 09:37:30 GMT

What events are you using?

How is it wrong?

What were you expecting?

Re: Average wrong box estimate

alakdam — Thu, 06 Oct 2022 09:49:01 GMT

Sorry If question is not clear.

These are the data I get from my event 👇🏾👇🏾

| spath path=data{}.actual_totes{}.finalBoxAmount output=actualBoxes | spath path=data{}.estimated_totes{}.box output=estimatedBoxes

Below splunk table image. I have estimated order was right 11 time and 6 time was wrong. I'm curious to know the percentage of incorrect box estimations overall.?

Re: Average wrong box estimate

ITWhisperer — Thu, 06 Oct 2022 09:56:50 GMT

| spath path=data{}.actual_totes{}.finalBoxAmount output=actualBoxes | spath path=data{}.estimated_totes{}.box output=estimatedBoxes | eval box_missing=if(actualBoxes != estimatedBoxes, 1, 0) | stats count as total sum(box_missing) as missing | eval percent_wrong = 100*missing/total

Re: Average wrong box estimate

alakdam — Thu, 06 Oct 2022 09:59:54 GMT

I don't see any answer.

Re: Average wrong box estimate

ITWhisperer — Thu, 06 Oct 2022 10:04:42 GMT

Try with eventstats so you can see which events have been included

| spath path=data{}.actual_totes{}.finalBoxAmount output=actualBoxes | spath path=data{}.estimated_totes{}.box output=estimatedBoxes | eval box_missing=if(actualBoxes != estimatedBoxes, 1, 0) | eventstats count as total sum(box_missing) as missing | eval percent_wrong = 100*missing/total

Re: Average wrong box estimate

alakdam — Thu, 06 Oct 2022 10:06:51 GMT

I started learning splunk yesterday. don't know the difference between spats and evenstats. 😄

Re: Average wrong box estimate

alakdam — Thu, 06 Oct 2022 10:08:32 GMT

Re: Average wrong box estimate

ITWhisperer — Thu, 06 Oct 2022 10:09:18 GMT

Essentially, event stats adds the calculated values as new fields to all the events without dropping any events, whereas stats replaces all the events with a single event containing just the calculated or group by fields

Re: Average wrong box estimate

ITWhisperer — Thu, 06 Oct 2022 10:12:28 GMT

Try including the other fields

| table actualBoxes estimatedBoxes total box_missing missing percent_wrong

Re: Average wrong box estimate

alakdam — Thu, 06 Oct 2022 10:15:11 GMT

PS: I just want only TOTAL average of wrong estimate

Re: Average wrong box estimate

ITWhisperer — Thu, 06 Oct 2022 10:18:19 GMT

Your actualBoxes and estimatedBoxes have not extracted correctly (or there weren't any values for them in your events)

Re: Average wrong box estimate

alakdam — Thu, 06 Oct 2022 10:24:08 GMT

How come it returns these

Re: Average wrong box estimate

alakdam — Thu, 06 Oct 2022 10:26:31 GMT

PS: Some of the actual values are null

Re: Average wrong box estimate

ITWhisperer — Thu, 06 Oct 2022 10:29:31 GMT

You have changed the names of the output fields on the spath so they no longer match the field names used in the eval - you should try and be consistent

Re: Average wrong box estimate

alakdam — Thu, 06 Oct 2022 10:40:02 GMT

I am really sorry for stupid mistake 🙏🏾. Now it shows the averages but why it shows 17 rows in same result. Cant I make one column and one row

Re: Average wrong box estimate

ITWhisperer — Thu, 06 Oct 2022 10:41:34 GMT

Change the eventstats back to stats (as I explained earlier)

Re: Average wrong box estimate

alakdam — Thu, 06 Oct 2022 10:43:28 GMT

Thank you for stick with me 🙏🏾🙏🏾