Splunk Search

## How to find the sum of several transactions, including a zero result?

Path Finder

Now, what troubles most is how to find the sum of several transactions, including a zero result.

I want to run the following searches:
index=a | stats count as a
index=b | stats count as b
index=c | stats count as c (however, the result is 0)
Calculated as the sum of three transaction numbers.

My final search is like this：
A:

``````index=a | stats count as a |appendcols [search index=b | stats count as b] | appendcols [search index=c | stats count as c | fillnull value=0 ] |  eval total=a+b+c
``````

B:

``````index=a | stats count as a |appendcols [search index=b | stats count as b] | appendcols [search index=c | stats count as c | eval coalesce(c,0) ] |  eval total=a+b+c
``````

Unfortunately, the two searches have no results. What way should I try? When C is zero, I want the total to equal the sum of A plus B.

Tags (3)
Path Finder

C'log like this:
aaaa count:100 bbbb
xxxxx count:200 zzzzz
wwww count:700 yyyyy

What I want to do like this:
Calculate the sum of all counts about C,When C is zero, is equal to the sum of A plus B.

Motivator

AppendCols needs a column to join your data on. Since all three of your columns are different, there is nothing to join on. If you are just trying to get the count of everything you can do the following:

``````index=a OR index=b OR index=C | stats count as total
``````

If you need the results separately first, then you can use append instead of appendcols.

``````index=a | eval label = a | stats count by label |append [search index=b | eval label = b| stats count by label] | append [search index=c | eval label = c| stats count by label | fillnull value=0 ]
``````

This should result in a table like thus:

``````label     count
a         5
b         2
c         6
``````

Hope this helps

==== EDIT ====

My second search had a typo. The second "append" was typoed to "appendcols". Now it is correct, and both are appends.

Influencer

If this solution doesn't work, we might need some sample data to help.

Influencer

One thing to add, to get the total after the results table you would want to do:
| stats sum(count) as Total

Path Finder

C'log like this:
aaaa count:100 bbbb
xxxxx count:200 zzzzz
wwww count:700 yyyyy

A & B‘log like this:
qqqq
sssss
pppp
This should result in a table like thus:
label count
a 3
b 3
c 1000
total:1006
However,when c is zero, the search have no result.I want to calculate the sum like "6".

Motivator

I had a typo in my original solution. Please try the new solution.

Just mentioning again: try using `append` rather than `appendcols`, and don't rename your count to something else. You can then use the pipe command that @masonmorales suggested to get the total of all three searches.

``````| stats sum(count) as Total
``````

Hope this helps

Path Finder

I am sorry,I try to slove the question with the ways you given.But it still has no result .
My search like this:
index=bancs | stats count | append [search index=apache | stats sum(bytes) | fillnull value=0 ] | stats sum(count)
In a period of time,the search like: index=apache | stats sum(bytes),has no result, actually.
But the search like:index=bancs | stats count has result return.Finally,stats sum(count) has no result,however.I need the finally result like index=bancs | stats count

Path Finder

My search like this:
index=a | stats count as a |appendcols [search index=b | stats count as b] | appendcols [search index=c sourcetype=cc "status=4" OR "status=5" OR "status=6" | stats sum (count) as c | fillnull value=0 ] | eval total=a+b+c
but ,it has no result,because c is zero.Why not total=A+B???

SplunkTrust

The search `index=a | stats count as a | appendcols [search index=b | stats count as b] | appendcols [search index=c | stats count as c] | eval total=a+b+c` works correctly for me.

Perhaps something like this would work for you if the total is all you seek?

``````index=a OR index=b OR index=c | stats count as total
``````
---
If this reply helps you, Karma would be appreciated.
Path Finder

I am sorry,my search just a simple description of the problem.In fact,it including transctions and some limited conditions.
index=a | stats count as a |appendcols [search index=b | stats count as b] | appendcols [search index=c sourcetype=cc "status=4" OR "status=5" OR "status=6" | stats sum (count) as c | fillnull value=0 ] | eval total=a+b+c

Influencer

Assuming your appendcols searches really are that simple, why not do...

``````index=a OR index=b OR index = C | stats count
``````
Path Finder

I am sorry,my search just a simple description of the problem.In fact,it including transctions and some limited conditions.
index=a | stats count as a |appendcols [search index=b | stats count as b] | appendcols [search index=c sourcetype=cc "status=4" OR "status=5" OR "status=6" | stats sum (count) as c | fillnull value=0 ] | eval total=a+b+c

Get Updates on the Splunk Community!