topic Re: Getting cumulative total into chart in Splunk Search

Getting cumulative total into chart

splunk-support0 — Fri, 10 Mar 2017 05:17:02 GMT

I have a dataset like:

quarter,faculty, people
2016-Q1,LAW,2
2016-Q1,BUSINESS,11
2016-Q1,EDUCATION,2
2016-Q2,BUSINESS,11
2016-Q2,BUSINESS,7
2017-Q1,LAW,5
2017-Q1,LAW,1
2017-Q1,EDUCATION,3
2017-Q1,EDUCATION,4
2017-Q1,EDUCATION,2

I'm trying to get the cumulative total by quarter of people per faculty

And display this in a chart so that the people count is on the y axis, the quarter is on the x-axis and the graph is stacked by faculty.

e.g.

I can get the (summed) people count as a chart, by doing this:

search | chart sum(people) over quarter by faculty

So the data would look like:

2016-Q1
LAW = 2
BUSINESS = 11
EDUCATION = 2

2016-Q2
LAW = 0
BUSINESS = 18
EDUCATION = 0

2017-Q1
LAW=6
BUSINESS = 0
EDUCATION = 9

But I want to get the cumulative people count, so that the counts end up more like

2016-Q1
LAW = 2
BUSINESS = 11
EDUCATION = 2

2016-Q2
LAW = 2
BUSINESS = 29
EDUCATION = 2

LAW = 8
BUSINESS = 29
EDUCATION = 11

I know there is an accum function but I can't get this to play with chart.

Any ideas how to do this?

Re: Getting cumulative total into chart

niketn — Fri, 10 Mar 2017 13:11:39 GMT

Just add the following to your existing query

 <Your exiting Search with chart as base search>
| accum LAW as Cumu_LAW 
| accum BUSINESS as Cumu_BUSINESS  
| accum EDUCATION as Cumu_EDUCATION

Then you need to enable Chart Overlay for all Cumu_* fields and View as Axis should be turned on. You can do the same by editing the Visualization in Splunk Web UI or else through Splunk CHart reference

<charting.chart.overlayfields>Cumu_LAW ,Cumu_BUSINESS,Cumu_EDUCATION</charting.chart.overlayfields>
<charting.Y2.enabled>1</charting.Y2.enabled>
<charting.Y2.scale>linear</charting.Y2.scale>

Re: Getting cumulative total into chart

somesoni2 — Fri, 10 Mar 2017 15:25:41 GMT

Assuming that the faculty name can be dynamic, try something like this. THis will give cumulative sum of all faculty column without specifying a name.

your base search  | chart sum(people) over quarter by faculty
| streamstats sum(*) as *

Re: Getting cumulative total into chart

splunk-support0 — Mon, 13 Mar 2017 02:10:51 GMT

Thank you. This does exactly what I want.