Creating pie chart with nested data

[Branden]

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:

How can I accomplish this? Thank you!

[jacobpevans]

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.

[ololdach · Accepted solution]

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]

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

[gcusello]

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]

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

[Branden]

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

[jacobpevans]

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.