Splunk Search

Can I create each row of a table as pie chart without drilldown?

splunkdivya
Explorer

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,

Labels (1)
0 Karma
1 Solution

niketn
Legend

@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.

alt text
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 thesplit by Statuscondition in Trellis, as a pre-requisite stats command by status is required. This creates an emptyStatus` 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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@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.

alt text
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 thesplit by Statuscondition in Trellis, as a pre-requisite stats command by status is required. This creates an emptyStatus` 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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

vinoth_raj
Path Finder

Hi folks,
Any idea about the "_mkv_child" row created. I cannot find anything related to it in Splunk docs.

Thanks, in advance.

0 Karma

splunkdivya
Explorer

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,

0 Karma

niketn
Legend

@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 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

niketn
Legend

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@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.

0 Karma

niketn
Legend

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 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

cmerriman
Super Champion

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
0 Karma

splunkdivya
Explorer

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,

0 Karma

cmerriman
Super Champion

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 emps

|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

niketn
Legend

@cmerriman. Wow!!! Your query should be used as tutorial of all types of SPL commands at one place 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...