topic Re: How to create a table that has values summed up by the column? in Splunk Search

How to create a table that has values summed up by the column?

Hppjet — Thu, 03 May 2018 15:49:57 GMT

I would like to manipulate it to look like this:

Re: How to create a table that has values summed up by the column?

kamlesh_vaghela — Thu, 03 May 2018 15:57:38 GMT

Hi @Hppjet,

Have you tried addcoltotals ??

Can you please try my sample search?

index="_internal" sourcetype=* date_minute=* 
| chart count over sourcetype by date_minute 
| addcoltotals 
| fillnull value="Grand Total" sourcetype

I have added fillnull for displaying "Grand Total" value at the last.

Thanks

Re: How to create a table that has values summed up by the column?

woodcock — Thu, 03 May 2018 16:04:12 GMT

I can't believe I actually transcribed your dataset (please post text, not graphics next time):

|makeresults | eval raw="A10 1 1090:
A10 2 1429:
A10 3 1527.66667:
A10 4 1611.46154:
A10 5 1023:
A102 1 1928.06667:
A102 2 1564.26923:
A102 3 2668.7:
A102 4 1730.33333:
A102 5 1801.75:
A103 1 1687.26316:
A103 2 1903.45833:
A103 3 1784.84615:
A103 4 1358.93333:
A103 5 1818.5:
A104 1 2476.2:
A104 2 2184.26316:
A104 3 2272.86667:
A104 4 1742.55:
A104 5 2571:
A107 1 2294.07692"
| makemv delim=":" raw
| mvexpand raw
| rex field=raw "(?<operator>\S+)\s+(?<Month>\S+)\s+(?<Count>\S+)"
| table operator Month Count

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| chart sum(Count) BY operator Month
| addtotals row=f col=t
| fillnull operator value="Grand Total"

Re: How to create a table that has values summed up by the column?

Hppjet — Thu, 03 May 2018 16:14:24 GMT

I need to take the values in the second column and turn those into columns which will sum the values based on what is in the first column.

Re: How to create a table that has values summed up by the column?

Hppjet — Thu, 03 May 2018 16:14:26 GMT

I need to take the values in the second column and turn those into columns which will sum the values based on what is in the first column.

Re: How to create a table that has values summed up by the column?

elliotproebstel — Thu, 03 May 2018 16:26:08 GMT

If you're looking to go from the first chart above (three columns: operator, Month, and Count) to the second chart, then you should be able to append this to your current search:

| xyseries operator Month Count 
| addcoltotals label="Grand Total" labelfield="operator"

Re: How to create a table that has values summed up by the column?

woodcock — Thu, 03 May 2018 16:39:02 GMT

Yes, I get it; that is what my solution does. Did you not try it? I went back and modified my answer to prove that it works with sample data.

Re: How to create a table that has values summed up by the column?

Hppjet — Thu, 03 May 2018 16:45:27 GMT

It worked!