topic Adding a Total column (without using addtotals) in Splunk Search

Adding a Total column (without using addtotals)

tread_splunk — Tue, 01 Oct 2024 13:06:52 GMT

Sometimes I set myself SPL conundrum challenges just to see how to solve them. I realised I couldn't do something I thought would be quite straightforward. For the dummy data below I want a single row resultset which tells me how many events of each UpgradeStatus and how many events in total i.e.

Total	Completed	Pending	Processing
11	6	3	2

I don't know in advance what the different values of UpgradeStatus might be and I don't want to use addtotals (this is the challenge part).

I came up with the solution below which kinda "misuses" xyseries (which I'm strangely proud of) . I feel like I'm missing a more straightforward solution, other than addtotals 🙂 Anyone up for the challenge?

Dummy data and solution (misusing xyseries) follows...

| makeresults format=csv data="ServerName,UpgradeStatus Server1,Completed Server2,Completed Server3,Completed Server4,Completed Server5,Completed Server6,Completed Server7,Pending Server8,Pending Server9,Pending Server10,Processing Server11,Processing" | stats count by UpgradeStatus | eventstats sum(count) as Total | xyseries Total UpgradeStatus count

Re: Adding a Total column (without using addtotals)

ITWhisperer — Tue, 01 Oct 2024 13:20:40 GMT

| makeresults format=csv data="ServerName,UpgradeStatus Server1,Completed Server2,Completed Server3,Completed Server4,Completed Server5,Completed Server6,Completed Server7,Pending Server8,Pending Server9,Pending Server10,Processing Server11,Processing" | eventstats count as Total | chart count by Total UpgradeStatus

Re: Adding a Total column (without using addtotals)

PickleRick — Tue, 01 Oct 2024 13:50:45 GMT

There isn't a search that can't be made uglier with foreach XD

| makeresults format=csv data="ServerName,UpgradeStatus
Server1,Completed
Server2,Completed
Server3,Completed
Server4,Completed
Server5,Completed
Server6,Completed
Server7,Pending
Server8,Pending
Server9,Pending
Server10,Processing
Server11,Processing"
| stats count by UpgradeStatus
| transpose 0 header_field=UpgradeStatus 
| fields - column
| eval Total=0
| foreach * 
    [ eval Total=Total+<<FIELD>> ]

As an alternative you can also use appendpipe

| makeresults format=csv data="ServerName,UpgradeStatus
Server1,Completed
Server2,Completed
Server3,Completed
Server4,Completed
Server5,Completed
Server6,Completed
Server7,Pending
Server8,Pending
Server9,Pending
Server10,Processing
Server11,Processing"
| stats count by UpgradeStatus
| appendpipe 
    [ stats sum(count) as count 
    | eval UpgradeStatus="Total" ]
| transpose 0 header_field=UpgradeStatus

Re: Adding a Total column (without using addtotals)

tread_splunk — Tue, 01 Oct 2024 14:04:47 GMT

Nice work @PickleRick !

Am I missing something? I had previously tried with the delightful foreach command earlier but I can't get it to avoid double-counting the Total field. So I end up with the Total equalling 22 - not the correct result of 11. I get the same thing with your solution as well.

Re: Adding a Total column (without using addtotals)

PickleRick — Tue, 01 Oct 2024 14:09:44 GMT

Right you are. As usual, forgot about it.

Here's fixed version.

| makeresults format=csv data="ServerName,UpgradeStatus
Server1,Completed
Server2,Completed
Server3,Completed
Server4,Completed
Server5,Completed
Server6,Completed
Server7,Pending
Server8,Pending
Server9,Pending
Server10,Processing
Server11,Processing"
| stats count by UpgradeStatus
| transpose 0 header_field=UpgradeStatus 
| fields - column
| eval Total=0
| foreach *
    [ eval Total=Total+if("<<FIELD>>"=="Total",0,<<FIELD>>) ]

Re: Adding a Total column (without using addtotals)

tread_splunk — Tue, 01 Oct 2024 14:15:49 GMT

Oh @ITWhisperer That is sweet.

It helps me if I specify the over / by designation ... but hats off to you.

| eventstats count as Total | chart count over Total by UpgradeStatus

Not looking forward to explaining to anyone how it works ... but work it does. (I'll add it to my collection of misusing a command to get a result. Got any others? 🙂)

Re: Adding a Total column (without using addtotals)

tread_splunk — Tue, 01 Oct 2024 14:18:29 GMT

Aha! That's how you avoid the double-count. I've encountered this before. Will definitely put this on file. Thank you.

Re: Adding a Total column (without using addtotals)

PickleRick — Tue, 01 Oct 2024 14:18:34 GMT

Alternatively you could just to a very ugly hack and name your field so it will be iterated over at the end and knowing it will be added to itself just divide it in half 😁

Re: Adding a Total column (without using addtotals)

ITWhisperer — Tue, 01 Oct 2024 14:30:49 GMT

I tend to just use the by clause as the first mentioned field is used for the over field, but that's just a matter of style / preference. Since you can only specify two fields on a chart command, over and by is probably clearer.

In this example, the eventstats is a way of providing a single value for the "over" field so that you get a single row of statistics.