Series Theory - or - Pie chart creation from search reporting keywords

[jfolkers]

So a manager comes into my office and asks for a pie chart.

I tell him, yes it's possible, in fact I can do it today. Big F.

I quickly whip up this vastly inefficient search string and all I want is one pie chart with two slices: gross data and gross voice:

sourcetype="wan_traffic" earliest=@d SITE=RDGDC-WAN1 OR SITE=RDGDC-WAN2 | fields SITE,WS_OUT_OCTETS,WS_VOICE_OUT_OCTETS,VZ_OUT_OCTETS,VZ_VOICE_OUT_OCTETS | accum WS_OUT_OCTETS as WS_GROSS | accum VZ_OUT_OCTETS as VZ_GROSS | eval GROSS=WS_GROSS + VZ_GROSS | accum WS_VOICE_OUT_OCTETS as WS_VOICE | accum VZ_VOICE_OUT_OCTETS as VZ_VOICE | eval VOICE=WS_VOICE + VZ_VOICE | eval DATA=GROSS - VOICE | chart last(GROSS),last(DATA), last(VOICE)

However, after reading all the docs and answers on this board - my wish remains unfilled.

Curiosity killed the cat. I couldn't resist clicking Show Report. What it generates is indeed a pie chart - however it is generating a 100% full blue pie chart of one value. Mousing over it reveals what it is doing with the 2nd value. I'll stop here.

My guess is I need series theory. I'm missing something basic.

I think I need either quantum physics with quarks theory taught to me, or search string series theory taught to me. I'm thinking splunk's the easier of the two.

I need some enlightenment! Anyone care to step up to this probably easy plate?

=============================== UPDATE:

I solved it. Turns out Splunk's Pie Chart engine requires a 2x2 table at a minimum. I didn't realize that and had just output a single summary results table with 1 line.

I had to use eval to generate the labels in column one, and append to generate the values for column two.

This works to generate the 2x2 table using eval to generate the labels in column 1 and append to generate the values in column two.

sourcetype="wan_traffic" earliest=@d SITE=RDGDC-WAN1 | fields SITE,WS_OUT_OCTETS,WS_VOICE_OUT_OCTETS,VZ_OUT_OCTETS,VZ_VOICE_OUT_OCTETS | accum WS_OUT_OCTETS as WS_GROSS | accum VZ_OUT_OCTETS as VZ_GROSS | eval GROSS=WS_GROSS + VZ_GROSS | accum WS_VOICE_OUT_OCTETS as WS_VOICE | accum VZ_VOICE_OUT_OCTETS as VZ_VOICE | eval V=WS_VOICE + VZ_VOICE | eval D=GROSS - V | eval TYPE="Data" | chart last(D) as BYTES by TYPE | append [search sourcetype="wan_traffic" earliest=@d SITE=RDGDC-WAN1 | fields SITE,WS_OUT_OCTETS,WS_VOICE_OUT_OCTETS,VZ_OUT_OCTETS,VZ_VOICE_OUT_OCTETS | accum WS_OUT_OCTETS as WS_GROSS | accum VZ_OUT_OCTETS as VZ_GROSS | eval GROSS=WS_GROSS + VZ_GROSS | accum WS_VOICE_OUT_OCTETS as WS_VOICE | accum VZ_VOICE_OUT_OCTETS as VZ_VOICE | eval V=WS_VOICE + VZ_VOICE | eval D=GROSS - V | eval TYPE="Voice" | chart last(V) as BYTES by TYPE]

This generates the 2x2 table properly: TYPE BYTES Data 206051242413 Voice 1440297883

Which Splunk will happily generate into a pie chart. However, the search string is very huge. Any idea how to optimize?

[jfolkers]

Nick, you'd be so proud of me. I used your stats & sum technique in a new column chart I made.

sourcetype="wan_traffic" earliest=@d SITE=RDGDC-WAN1 | fields SITE,WS_OUT_OCTETS,WS_VOICE_OUT_OCTETS,VZ_OUT_OCTETS,VZ_VOICE_OUT_OCTETS | eval V=WS_VOICE_OUT_OCTETS+VZ_VOICE_OUT_OCTETS | eval G=WS_OUT_OCTETS+VZ_OUT_OCTETS | eval D=G-V | bucket _time span=1h | stats sum(V) as Voice, sum(D) as Data by _time

So, even though your answer helped only with field formation optimization, I'm going to give you credit for the answer.

[sideview]

hehe. Sweet. I'm glad to hear. Yea once you really discover what stats can do it's like discovering the search language all over again.

[sideview · Accepted solution]

I think you want to back away slowly from accum and instead embrace the much more powerful and simple stats command. You are indeed using a complicated and obscure method where a simple and common method would work better. Granted it doesnt look any simpler at first blush but when you get used to what you can do with the stats command it'll seem simpler.

I'd start here for now:

sourcetype="wan_traffic" earliest=@d SITE=RDGDC-WAN1 OR SITE=RDGDC-WAN2 | stats sum(WS_OUT_OCTETS) as WS_GROSS sum(VZ_OUT_OCTETS) as VZ_GROSS sum(WS_VOICE_OUT_OCTETS) as WS_VOICE sum(VZ_VOICE_OUT_OCTETS) as VZ_VOICE | eval GROSS=WS_GROSS + VZ_GROSS | eval VOICE=WS_VOICE + VZ_VOICE | eval DATA=GROSS - VOICE

http://www.splunk.com/base/Documentation/latest/SearchReference/Stats