Splunk Search

How to get pie chart average value in three slices?

alakdam
Path Finder

Short description:
When a consumer orders groceries online, I provide the picker—the individual who picked the foods based on the order—with an estimated box that will be needed for that order and that data is stored in a database. The functionality of the Estimated box generally works, although occasionally it fluctuates. It may be greater or lesser. Actual box use data will be stored in data if the picker adds more or fewer boxes than necessary for the order. Actual data box never store in database if approximated functionality works.

Expected output: 

1. I want find out how much Percentage/Average of actual values missingI am not sure how to evaluate null/defined Actual boxes.

This is my attempt not sure is it correct:

 

 

 

| spath path=data{}.actual_totes{}.finalBoxAmount output=finalBoxes
| spath path=data{}.estimated_totes{}.box output=estimatedBox 
| stats sum(estimatedBox) as totalEstimatedBox, sum(finalBoxes) as totalFinalBoxes
| eval diff =( totalFinalBoxes - totalEstimatedBox) * 100 / totalFinalBoxes
| table diff

 

 

 


This is my data splunk data table image. As you can see in splunk table  some my actual boxes value is null/undefined/emptyObject(not sure).  In splunk JSON, this is how I get actual_totes: { }

  data: { 
     actual_totes: { },
     estimated_totes: { 
        box: 4
     }
   }



Screenshot 2022-10-06 at 7.53.46.png

PS: I'm a rookie with Splunk, thus my grasp of its syntax is limited. Please walk me through how to display the value in a PIE chart. Pie chat had the following value: Estimated Boxes, Real Boxes used, and missing actual numbers in Percentage %.

Thank you.

Labels (4)
Tags (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

@alakdam Handling missing values is quite easy: just use if with isnull.  But you want to ask whether you need to calculate percentage yourself.  For starters, real values AND percentage they represent on the same piechart is nonsensical.  Your piechart either have real values so they make up a whole pie, or have percentages so they make up a whole pie. (A side note: negative values are nonsensical in piecharts so your base should be estimated boxes.)

Understandably, you want the user to see actual values, not just percentage.  In Splunk, you can simply calculate real values; Splunk's piechart visualization will supply percentage.  For example,

| stats sum(data.estimated_totes.box) as totalEstimatedBox, sum(data.actual_totes.FinalBoxAmount) as totalFinalBoxes
| eval totalFinalBoxes = if(isnull(totalFinalBoxes), 0, totalFinalBoxes)
| eval diff = (totalEstimatedBox - totalFinalBoxes)
| fields - totalEstimatedBox
| eval series = "value" ``` this is just for prettier header ```
| transpose header_field=series column_name=key

Your singular sample data will render

boxes.png

Note: you do not need separate spath to retrieve values.  If your raw events are Python, JSON should be already been extracted into dot (".") annotated paths so the above should work without spath.  If not, use a single spath to extract before stats, e.g.,

| spath input=JSONdata

 

View solution in original post

Tags (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

@alakdam Handling missing values is quite easy: just use if with isnull.  But you want to ask whether you need to calculate percentage yourself.  For starters, real values AND percentage they represent on the same piechart is nonsensical.  Your piechart either have real values so they make up a whole pie, or have percentages so they make up a whole pie. (A side note: negative values are nonsensical in piecharts so your base should be estimated boxes.)

Understandably, you want the user to see actual values, not just percentage.  In Splunk, you can simply calculate real values; Splunk's piechart visualization will supply percentage.  For example,

| stats sum(data.estimated_totes.box) as totalEstimatedBox, sum(data.actual_totes.FinalBoxAmount) as totalFinalBoxes
| eval totalFinalBoxes = if(isnull(totalFinalBoxes), 0, totalFinalBoxes)
| eval diff = (totalEstimatedBox - totalFinalBoxes)
| fields - totalEstimatedBox
| eval series = "value" ``` this is just for prettier header ```
| transpose header_field=series column_name=key

Your singular sample data will render

boxes.png

Note: you do not need separate spath to retrieve values.  If your raw events are Python, JSON should be already been extracted into dot (".") annotated paths so the above should work without spath.  If not, use a single spath to extract before stats, e.g.,

| spath input=JSONdata

 

Tags (2)
0 Karma

alakdam
Path Finder

Thank you very much for advice and query. How Can I rename the totalFinalBoxes to Total final boxes

0 Karma

yuanliu
SplunkTrust
SplunkTrust

The command is, ahem, rename😉

| rename totalFinalBoxes AS "Total final boxes"

 

Tags (1)
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...