Adding a Total column (without using addtotals)

[tread_splunk]

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

[PickleRick]

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 😁

[PickleRick]

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

[tread_splunk]

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.

[PickleRick]

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>>) ]

[tread_splunk]

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

[ITWhisperer]

| 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

[tread_splunk]

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? 🙂)

[ITWhisperer · Accepted solution]

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.