Dashboards & Visualizations

Creating pie chart with nested data

Branden
Builder

Hello. We have tabular data formatted like this:

"CollectionName" "CollectionSize" "PercentageUsed"
"FOO" "36" "50" 
"BAR" "14" "36"

(The first row is the column headers, translated into field names.)

We are trying to create a dashboard containing pie charts like this:

For each CollectionName, create a pie chart labeled with "CollectionName" that contains the value of field "CollectionSize."
Within that pie chart, we want a wedge for the "PercentageUsed" field. So if "PercentageUsed" field is "50" (%) of CollectionSize, it would split the pie chart in half. If it's "20" (%) of CollectionSize then the pie chart would have a wedge that is 20% of the chart's size. We'll eventually want to be able to click on that wedge to drill down, but we'll tackle that later.

We'd like the dashboard to show a pie chart for each CollectionName. I think I'm close. I can get it to create a single pie chart for one CollectionName.

index=foo sourcetype="foo-usage" CollectionName=FOO | stats count by CollectionSize,PercentageUsed | transpose

That will show a single pie chart for the CollectionName "FOO." How do we get a pie chart for each CollectionName on the dashboard?

Thanks!

Update: I am able to get pie charts for each CollectionName with the PercentageUsed on top:

index=vdiclinics sourcetype="vdi-usage" | stats count by PercentageUsed,CollectionName

and using Trellis (finally got that to work). That's okay, but I'd like PercentageUsed to be a wedge, not a label. Here's what I have right now for one Collection:

alt text

How can I accomplish this? Thank you!

0 Karma
1 Solution

ololdach
Builder

Hi,
try this search:

| makeresults | eval CollectionName="Foo,Bar" | fields - _time | makemv delim="," CollectionName | mvexpand CollectionName 
| eval PercentageUsed=round(random() % 1000/10,1) 
| eval PercentageFree=100-PercentageUsed | eval Percentages=PercentageFree.",".PercentageUsed 
| makemv delim="," Percentages | mvexpand Percentages | eval Label=if(PercentageFree=Percentages,"Free","Used") 
| table Label,CollectionName,Percentages | xyseries Label,CollectionName,Percentages

In the first line I just recreate your data and add some random usages in the second.
The third line introduces a new field PercentageFree as 100-used, because you want two slices per graph. Now, this is where the going gets tough: You combine the two values into one Percentages field, turn it into multivalue and split them into two events. In effect you duplicate every event, because you want one event showing the free value and one with the used.

Now, the next step is to introduce a Label field that will hold the "Free/Used" to be used as label. Since our MV field Percentages was split into two distinct values, one per event, we do this with a simple if. If you have more values to cope with... use case or lookup instead.

Almost done: Use table to get rid of all unwanted information and xyseries to transpose the fields into the right form for the trellis.

Hope it helps.
Oliver

View solution in original post

jacobpevans
Motivator

Try something like this:

index=vdiclinics sourcetype="vdi-usage"
| stats avg(PercentageUsed) as PercentageUsed by CollectionName
| eval PercentageFree = 100 - PercentageUsed

And make your Trellis pie chart from that.

Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.
0 Karma

ololdach
Builder

Hi,
try this search:

| makeresults | eval CollectionName="Foo,Bar" | fields - _time | makemv delim="," CollectionName | mvexpand CollectionName 
| eval PercentageUsed=round(random() % 1000/10,1) 
| eval PercentageFree=100-PercentageUsed | eval Percentages=PercentageFree.",".PercentageUsed 
| makemv delim="," Percentages | mvexpand Percentages | eval Label=if(PercentageFree=Percentages,"Free","Used") 
| table Label,CollectionName,Percentages | xyseries Label,CollectionName,Percentages

In the first line I just recreate your data and add some random usages in the second.
The third line introduces a new field PercentageFree as 100-used, because you want two slices per graph. Now, this is where the going gets tough: You combine the two values into one Percentages field, turn it into multivalue and split them into two events. In effect you duplicate every event, because you want one event showing the free value and one with the used.

Now, the next step is to introduce a Label field that will hold the "Free/Used" to be used as label. Since our MV field Percentages was split into two distinct values, one per event, we do this with a simple if. If you have more values to cope with... use case or lookup instead.

Almost done: Use table to get rid of all unwanted information and xyseries to transpose the fields into the right form for the trellis.

Hope it helps.
Oliver

Branden
Builder

Wow... that is quite amazing. Not quite the solution I was expecting, but it works! 🙂
And I learned some stuff. Thank you. 🙂

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi Branden,
did you explored the use of Trellis in pie charts?
If not, see the Splunk Dashboard Examples App at https://splunkbase.splunk.com/app/1603/ "Trellis Visualization Layout", it could be useful for you.

Bye.
Giuseppe

Branden
Builder

Thank you for your reply. I experimented with Trellis, but all it gave me was a bunch of blank pie charts containing no data.

0 Karma

Branden
Builder

Actually, Trellis DID help me, thank you! Still having some difficulty (see edits above), but definitely progress.

0 Karma

jacobpevans
Motivator

Check out that Splunk Dashboard Examples app. It's great for learning how to use all sorts of things related to dashboards. It even contains a lot of plug-and-play stuff.

Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...