Hi I have a table result created as:
Emp sold consumed wasted...... stolen
ABC 8 12 5 12
XYZ 2 5 6 7
:
:
TUV 10 34 2 3
where Emp, sold,consumed, wasted , stolen etc. (can be more also), are table header
ABC is row1, with 8,12,5,12 as the values of sold,consumed, wasted , stolen respectively.
My problem:
I need to create a pie chart for ABC, XYZ etc on page load (not drilldown), to show the detail of each category corresponding to each Emp.
Please help me with some pointers
Best,
@splunkdivya, if you are on Splunk Enterprise 6.6 or later, you can make use of Trellis Layout to have you Pie Charts depicting various Status, which are split by Employees.
Following is the underlying search for the attached run anywhere dashboard example as per sample data provided in the question:
| makeresults
| eval data="ABC,8,12,5,12;XYZ,2,5,6,7;TUV,10,34,2,3 "
| makemv data delim=";"
| mvexpand data
| eval data=split(data,",")
| eval Emp=mvindex(data,0)
| eval sold=mvindex(data,1)
| eval consumed=mvindex(data,2)
| eval wasted=mvindex(data,3)
| eval stolen=mvindex(data,4)
| fields - _time data
| transpose header_field=Emp column_name=Status
| search Status!="_*"
| stats first(*) as * by Status
| search Status!="Status"
The commands till fields - time data prepare the sample data as per the question. You can use the pipes from transpose onward in your existing search. Since I used multivalued field in my mock query, I have search for removing _mkv_child
value after transpose
command. If you are not getting such field you can remove the `search Status!="*"condition. Since I intend to use the
split by Statuscondition in Trellis, as a pre-requisite stats command by status is required. This creates an empty
Status` row in the results and hence needs to be removed.
PS: Trellis is created with splitBy _aggregation
.
_aggregation
Following is a run anywhere dashboard for you to test out.
<dashboard>
<label>Pie Chart by field over another field</label>
<row>
<panel>
<chart>
<search>
<query>| makeresults
| eval data="ABC,8,12,5,12;XYZ,2,5,6,7"
| makemv data delim=";"
| mvexpand data
| eval data=split(data,",")
| eval Emp=mvindex(data,0)
| eval sold=mvindex(data,1)
| eval consumed=mvindex(data,2)
| eval wasted=mvindex(data,3)
| eval stolen=mvindex(data,4)
| fields - _time data
| transpose header_field=Emp column_name=Status
| search Status!="_*"
| stats first(*) as * by Status
| search Status!="Status"</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="height">220</option>
<option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
<option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
<option name="charting.axisTitleX.visibility">collapsed</option>
<option name="charting.axisTitleY.visibility">collapsed</option>
<option name="charting.axisTitleY2.visibility">collapsed</option>
<option name="charting.axisX.abbreviation">none</option>
<option name="charting.axisX.scale">linear</option>
<option name="charting.axisY.abbreviation">none</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.axisY2.abbreviation">none</option>
<option name="charting.axisY2.enabled">0</option>
<option name="charting.axisY2.scale">inherit</option>
<option name="charting.chart">pie</option>
<option name="charting.chart.bubbleMaximumSize">50</option>
<option name="charting.chart.bubbleMinimumSize">10</option>
<option name="charting.chart.bubbleSizeBy">area</option>
<option name="charting.chart.nullValueMode">gaps</option>
<option name="charting.chart.showDataLabels">none</option>
<option name="charting.chart.sliceCollapsingThreshold">0.01</option>
<option name="charting.chart.stackMode">default</option>
<option name="charting.chart.style">shiny</option>
<option name="charting.drilldown">none</option>
<option name="charting.layout.splitSeries">0</option>
<option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
<option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
<option name="charting.legend.mode">standard</option>
<option name="charting.legend.placement">none</option>
<option name="charting.lineWidth">2</option>
<option name="trellis.enabled">1</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
<option name="trellis.splitBy">_aggregation</option>
</chart>
</panel>
</row>
</dashboard>
@splunkdivya, if you are on Splunk Enterprise 6.6 or later, you can make use of Trellis Layout to have you Pie Charts depicting various Status, which are split by Employees.
Following is the underlying search for the attached run anywhere dashboard example as per sample data provided in the question:
| makeresults
| eval data="ABC,8,12,5,12;XYZ,2,5,6,7;TUV,10,34,2,3 "
| makemv data delim=";"
| mvexpand data
| eval data=split(data,",")
| eval Emp=mvindex(data,0)
| eval sold=mvindex(data,1)
| eval consumed=mvindex(data,2)
| eval wasted=mvindex(data,3)
| eval stolen=mvindex(data,4)
| fields - _time data
| transpose header_field=Emp column_name=Status
| search Status!="_*"
| stats first(*) as * by Status
| search Status!="Status"
The commands till fields - time data prepare the sample data as per the question. You can use the pipes from transpose onward in your existing search. Since I used multivalued field in my mock query, I have search for removing _mkv_child
value after transpose
command. If you are not getting such field you can remove the `search Status!="*"condition. Since I intend to use the
split by Statuscondition in Trellis, as a pre-requisite stats command by status is required. This creates an empty
Status` row in the results and hence needs to be removed.
PS: Trellis is created with splitBy _aggregation
.
_aggregation
Following is a run anywhere dashboard for you to test out.
<dashboard>
<label>Pie Chart by field over another field</label>
<row>
<panel>
<chart>
<search>
<query>| makeresults
| eval data="ABC,8,12,5,12;XYZ,2,5,6,7"
| makemv data delim=";"
| mvexpand data
| eval data=split(data,",")
| eval Emp=mvindex(data,0)
| eval sold=mvindex(data,1)
| eval consumed=mvindex(data,2)
| eval wasted=mvindex(data,3)
| eval stolen=mvindex(data,4)
| fields - _time data
| transpose header_field=Emp column_name=Status
| search Status!="_*"
| stats first(*) as * by Status
| search Status!="Status"</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="height">220</option>
<option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
<option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
<option name="charting.axisTitleX.visibility">collapsed</option>
<option name="charting.axisTitleY.visibility">collapsed</option>
<option name="charting.axisTitleY2.visibility">collapsed</option>
<option name="charting.axisX.abbreviation">none</option>
<option name="charting.axisX.scale">linear</option>
<option name="charting.axisY.abbreviation">none</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.axisY2.abbreviation">none</option>
<option name="charting.axisY2.enabled">0</option>
<option name="charting.axisY2.scale">inherit</option>
<option name="charting.chart">pie</option>
<option name="charting.chart.bubbleMaximumSize">50</option>
<option name="charting.chart.bubbleMinimumSize">10</option>
<option name="charting.chart.bubbleSizeBy">area</option>
<option name="charting.chart.nullValueMode">gaps</option>
<option name="charting.chart.showDataLabels">none</option>
<option name="charting.chart.sliceCollapsingThreshold">0.01</option>
<option name="charting.chart.stackMode">default</option>
<option name="charting.chart.style">shiny</option>
<option name="charting.drilldown">none</option>
<option name="charting.layout.splitSeries">0</option>
<option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
<option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
<option name="charting.legend.mode">standard</option>
<option name="charting.legend.placement">none</option>
<option name="charting.lineWidth">2</option>
<option name="trellis.enabled">1</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
<option name="trellis.splitBy">_aggregation</option>
</chart>
</panel>
</row>
</dashboard>
Hi folks,
Any idea about the "_mkv_child" row created. I cannot find anything related to it in Splunk docs.
Thanks, in advance.
Hi Niketnilay,
The solution did work as expected. Thanks for sharing it... 🙂
Just one more insight I would like from your end:
| search Status!="_"
| stats first() as * by Status
| search Status!="Status"
What does thes line of code do. As I was using trellis earlier but It gave me:
No results in the pie... empty circles but these lines did magic.
Appreciations
Best,
@splunkdivya, I have explained these points in my answer between Splunk Search code and Sample Dashboard code.
1) You might not need | search Status!="_"
with your actual Splunk search. Please try removing and see if results are still the same. I had to mock data as multivalue field because of which I had to remove an unwanted row.
2) Unless you have by Status
with some transforming command, Trellis will not give you the option to segregate Status over the other field i.e. Emp in this case. Even if you use table with exact same output you will not be able to split Trellis as you require.
3) Since I uses stats to rebuild the table for Status, there was an empty row inserted in my example. If the data in your question sample is the result of stats query, you might not need this as well. Please check accordingly.
Hope these clarify. If not let me know 🙂
Are you trying to display JUST the pie chart, or are you trying to display the data in a row, with a pie chart to itself at the end of the row?
If JUST the pie chart, then you could use Trellis. https://answers.splunk.com/answers/567335/how-do-i-get-a-sparkline-to-display-as-a-pie-chart-1.html
Thanks @DalJeanis, I think this use case is another one of those Trellis to the rescue
scenarios 🙂 I have added my answer here with a run anywhere dashboard example.
PS: Pie Chart Sparkline is not supported officially by Splunk and since it can create Pie slices only for time span
buckets, it can not be readily applied for anything else like Status count in this case. (Not unless someone looks at hard coded _time and span to be overridden by our custom fields.
@niketnilay - I noticed that, and SERIOUSLY wondered about it. If it was a choice of what to make pie chart sparklines
work for, _time
is the LAST thing I'd think of.
Well span seriously limits it for sure but still applicable for some use cases. For example weekly distribution with daily span will give contribution of each day.
I am keenly interested in reverse engineering this to change bucket from time to something else, whenever I find time 🙂
what if you did something like this:
...|untable emp sold consumed|eval emp=emp+" - "+sold|table emp consumed|rename consumed as value
here is what i did with your sample data above from the results table
|makeresults|eval data="emp=ABC,sold=8,consumed=12,wasted=5,stolen=12 emp=XYZ,sold=2,consumed=5,wasted=6,stolen=7 emp=TUV,sold=10,consumed=34,wasted=2,stolen=3"|makemv data|mvexpand data|rename data as _raw|kv|table emp sold consumed wasted stolen|untable emp sold consumed|eval emp=emp+" - "+sold|table emp consumed|rename consumed as value
Hi cmerriman,
Appreciate your help. But "untable emp sold consumed" may not suit to the scenario as the table headers are not constant. They are changing as per time selection. Like May be Today nothing was wasted and may be yesterday nothing was sold.
apologies for the vague dataset.
Please let me know if that makes sense.
Best,
now this took a bit of thinking, but it might work...you might need to add 0
to the transpose if you have a lot of emp
s
|makeresults|eval data="emp=ABC,sold=8,consumed=12,wasted=5,stolen=12 emp=XYZ,sold=2,consumed=5,wasted=6,stolen=7 emp=TUV,sold=10,consumed=34,wasted=2,stolen=3"|makemv data|mvexpand data|rename data as _raw|kv|table emp sold consumed wasted stolen
|streamstats count
|eval emp=emp+"_"+count|fields - count
|transpose header_field=emp
|foreach *_1 *_2 [untable column <<MATCHSTR>>_1 <<MATCHSTR>>_2]|streamstats window=1 current=f values(*_2) as prev*|foreach *_2 [rex field=<<FIELD>> mode=sed "s/\w+_\d+//g"|eval value=<<FIELD>>]|foreach prev* [rex field=<<FIELD>> mode=sed "s/_\d+|\d+//g"|eval emp=<<FIELD>>]
|eval keep=if(emp="" OR value="",0,1)|search keep=1 |table column value emp |eval emp=emp+" - "+column|table emp value
@cmerriman. Wow!!! Your query should be used as tutorial of all types of SPL commands at one place 🙂